EF Core not able to compare GUID properties using SQLite database

[Bucket1616]

Hello,

I have tried a few times to update to 1.7.1 and when I do, the database does not seem to port over correctly. I have a backup saved so I can go back if needed. Any ideas on how to fix or troubleshoot?

At first, these were the contents of the docker/openbudgeteer folder:
openbudgeteer.db
openbudgeteer.db-shm
openbudgeteer.db-wal

After maybe 5 minutes, it changed to only contain:
openbudgeteer.db

Afterwards, when looking at data within the app, I see my Buckets configuration, however, when clicking on Transaction, I get a popup with the following: "Error during loading: Sequence contains no elements"

Thanks for any help!
Mike

[Bucket1616]

Here is the log from Docker
openbudgeteer.csv

[TheAxelander]

So in the logs I can only see the issue from #219. I think you also followed the Breaking changes notes from the Changelog before upgrading to 1.7 otherwise you couldn't see your Buckets.

Would be great if you could spin up a new container with a cleansed log and just open the Transaction Page. With above error message there should be something in the logs

[Bucket1616]

Hello,
Happy to spin up an new container, but before I do, I'm not sure i did whatever was needed prior to upgrading. When I look at the ChangeLog, I see two breaking changes going to 1.7; the first about database name (no idea where to see the offending characters) and the second about database path. I did not set the database path to anything, I believe it is defaulting to the "database" directly. I did not see anything dealing with Buckets, so maybe there is something else I needed to do?

[TheAxelander]

So if you always used the defaults then after the upgrade the database container path should be still /app/database/openbudgeteer.db

After you started a new container, just go to the Transaction Page and share the logs

[Bucket1616]

I started a new container, with the v1.6.3 database already in the database location. Same symptoms when it started. There are no log entries created when i go to the Transaction Page. I looked at the log before and after going to the Transaction page and it did not change:
warn: Microsoft.AspNetCore.DataProtection.Repositories.FileSystemXmlRepository[60]
Storing keys in a directory '/root/.aspnet/DataProtection-Keys' that may not be persisted outside of the container. Protected data will be unavailable when container is destroyed.
warn: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[35]
No XML encryptor configured. Key {8fcbce85-75a7-4cfa-9125-63d78faa886d} may be persisted to storage in unencrypted form.
info: Microsoft.Hosting.Lifetime[14]
Now listening on: http://[::]:80
info: Microsoft.Hosting.Lifetime[0]
Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
Hosting environment: Production
info: Microsoft.Hosting.Lifetime[0]
Content root path: /app
warn: Microsoft.AspNetCore.HttpsPolicy.HttpsRedirectionMiddleware[3]
Failed to determine the https port for redirect.

I also tried to enter a garbage transaction. That created the following error popup:
"Errors during database update: An error occurred while saving the entity changes. See the inner exception for details."
But again, there was no entry in the log as a result.

Also, I thought Buckets came over correctly, but they did not. There used to be hierarchy to our buckets and now there is none when on version 1.7.1.

If it would be helpful, I'd be happy to securely share our database with you.

Mike

[TheAxelander]

That would be great. Feel free to send it to me via my account mail address (alexpreib@outlook.com)

[Bucket1616]

Thank you, sent.

[TheAxelander]

Sorry for the late response as I was quite busy last days.

So far I can reproduce the issue on my side. On a first glance I would say the underlying database migration is working fine (which is the most important thing).

But on several places in the coding the queries doesn't return any values. If I execute these queries manually, e.g. in Sqlite DB Browser, I get the expected results (which is also good).

I need to spend some time in investigating that. My initial feeling is that for some reason EF Core and its Sqlite package has some hiccups with the Sqlite Date handling For some reason the Sqlite provider of EF Core is not able to properly compare GUID

[TheAxelander]

I have a first basic fix implemented locally, which I need to test more extensively also in regard to side effects with MySQL and PostgreSQL. My plan right now is to release this as part of the regular 1.8 update

[Bucket1616]

That’s great, very happy you were able to reproduce and even better you have a tentative fix. I’ll look forward to the 1.8 update. MikeOn Mar 3, 2024, at 08:36, Alexander Preibisch ***@***.***> wrote: I have a first basic fix implemented locally, which I need to test more extensively also in regard to side effects with MySQL and PostgreSQL. My plan right now is to release this as part of the regular 1.8 update —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[TheAxelander]

Reopen due to failing Unit Tests

[TheAxelander]

Related to dotnet/efcore#19651, could be solved using dotnet/efcore#25043 (comment)

[TheAxelander]

For transparency I'm sharing my result of this investigation.

So far I was not able to identify the final root cause of that issue. The randomly generated Guid from the database migration are looking good, using plain SQL I can join the various tables without any issue.
For some reason EF Core cannot read these converted Guid, only newly generated Guid provided by EF Core work properly. This is also the main reason why this issue did not pop up during Unit testing.

Using a solution mentioned in dotnet/efcore#25043 (comment) fixed the issue for the shared database that I received, but it's not compatible with other databases like MySql. Also Unit tests (which also use SQLite) were failing now, for whatever reason.

The workaround for SQLite that I have now implemented is, instead of generating random Guid during data migration, a hard-coded Guid format including the previous int-based IDs. Testing with the provided database were successfully.

I had some hard times with that issue and was already thinking about dropping SQLite support at all. I think with this solution I was able to prevent this and hope for the future that nothing similar is popping up again...

[Bucket1616]

Thank you for the detailed analysis and all your work in investigating. Which version number should I try for an upgrade?I’m assuming that once the upgrade happens successfully, I should be good for any other future updates?MikeOn Mar 12, 2024, at 13:14, Alexander Preibisch ***@***.***> wrote: For transparency I'm sharing my result of this investigation. So far I was not able to identify the final root cause of that issue. The randomly generated Guid from the database migration are looking good, using plain SQL I can join the various tables without any issue. For some reason EF Core cannot read these converted Guid, only newly generated Guid provided by EF Core work properly. This is also the main reason why this issue did not pop up during Unit testing. Using a solution mentioned in dotnet/efcore#25043 (comment) fixed the issue for the shared database that I received, but it's not compatible with other databases like MySql. Also Unit tests (which also use SQLite) were failing now, for whatever reason. The workaround for SQLite that I have now implemented is, instead of generating random Guid during data migration, a hard-coded Guid format including the previous int-based IDs. Testing with the provided database were successfully. I had some hard times with that issue and was already thinking about dropping SQLite support at all. I think with this solution I was able to prevent this and hope for the future that nothing similar is popping up again... —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[TheAxelander]

If you want to try it out already, you can use the docker tag pre-release. There is one bug left that I have to work on and then the release of 1.8 should be ready