search

statamic / eloquent-driver

Provides support for storing your Statamic data in a database, rather than flat files.
https://statamic.dev/tips/storing-content-in-a-database
MIT License
104 stars 75 forks source link

SQL Server conversion error when trying to access frontend #221

Closed PFEW-ITSD closed 9 months ago

PFEW-ITSD commented 10 months ago

I've imported the entry files into the database successfully, but I keep getting this same error when trying to access any page on the frontend:

SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.

SELECT * FROM [entries] WHERE [id] IN (home, 51f3e305-2028-4fec-91cb-d2e079af5250)

I know the UUID there is from another page as that's been successfully imported. I've also deleted the home.md file through Statamic just in case that was causing any issues, and I've also cleared the cache too.

Application Name: Statamic
Laravel Version: 10.23.1
PHP Version: 8.1.4
Composer Version: 2.6.5
Environment: local
Debug Mode: ENABLED
URL: localhost
Maintenance Mode: OFF

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: statamic
Database: sqlsrv
Logs: single
Mail: smtp
Queue: sync
Session: file

Statamic
Addons: 2
Antlers: runtime
Stache Watcher: Disabled
Static Caching: Disabled
Version: 4.21.0 PRO

Statamic Addons
aryehraber/statamic-captcha: 1.10.0
statamic/eloquent-driver: 3.0.0

Statamic Eloquent Driver
Asset Containers: eloquent
Assets: eloquent
Blueprints: eloquent
Collection Trees: eloquent
Collections: eloquent
Entries: eloquent
Forms: eloquent
Global Sets: eloquent
Global Variables: eloquent
Navigation Trees: eloquent
Navigations: eloquent
Revisions: eloquent
Taxonomies: eloquent
Terms: eloquent
ryanmitchell commented 10 months ago

I've not got any experience with SQL server, but I would guess the issue is the id of home - did you try removing that row in the database or assigning it a valid UUID? Failing that you could try converting to integer based IDs rather than UUIDs.

PFEW-ITSD commented 10 months ago

The new home page actually does have a UUID and I've deleted the page that has 51f3e305-2028-4fec-91cb-d2e079af5250 as its UUID but it still shows the same error strangely. I've had a similar error in the past with users but that was resolved by just deleting the existing file with the same UUID.

Presumably I would just need to drop the existing entries table, publish the migration for int based IDs, and change the model back to EntryModel::class?

ryanmitchell commented 10 months ago

Presumably I would just need to drop the existing entries table, publish the migration for int based IDs, and change the model back to EntryModel::class?

Correct

PFEW-ITSD commented 9 months ago

Unfortunately no change there - even with an empty entries table, it seems to really want to find a page with this UUID for some reason:

SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Error converting data type nvarchar to bigint.

select * from [entries] where [id] = 51f3e305-2028-4fec-91cb-d2e079af5250
ryanmitchell commented 9 months ago

Might you have some data in one of your entries (or terms) thats still linked by that id?

PFEW-ITSD commented 9 months ago

Might you have some data in one of your entries (or terms) thats still linked by that id?

It was exactly this! It seems that when I deleted those pages, despite not showing up in the CP anymore, they were still saved in the database under the trees for the user navigations I have. So when I signed out, I now saw this error with 2 other UUIDs for now deleted pages:

select * from [entries] where [id] in (home, 6cb90e03-172d-4f6b-b6b4-1b6d3f029660, 2b35ccc0-42d1-4c48-8850-c7e8cf36c32e)

Meanwhile, I could get to the home page once signed in by removing the deleted pages from that navigation.

Thanks for your help with all of this, Ryan!