SQLitePCLRaw.bundle_e_sqlite3mc v2.1.11 cause SQLite Error 26

[ali50m]

After upgrade to the latest version of SQLitePCLRaw.bundle_e_sqlite3mc in my personal project, it will throws a Exception says.

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 26: 'file is not a database'.'

I have simplified the issue into a test repo.

reproduce:

git clone [email protected]:ali50m/SqliteMcConsole.git
cd SqliteMcConsole
dotnet run --project .\src\SqliteMcConsole\

Run the project more than two times you will met the exception.

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 26: 'file is not a database'.

workaround:

1. downgrade the nuget SQLitePCLRaw.bundle_e_sqlite3mc to 2.1.10.
2. Or delete the password setting in connection string.

env:

1. windows 10 21H2 19044.5737
2. dotnet SDK 9.0.203

[utelle]

See https://github.com/utelle/SQLite3MultipleCiphers-NuGet/issues/10#issuecomment-2801393208.

[ali50m]

Dear folks, any update?

I just tried the latest 9.0.6 version of Microsoft.Data.Sqlite with SQLitePCLRaw.bundle_e_sqlite3mc, but still failed.

[utelle]

Dear folks, any update?

Unfortunately, no.

I just tried the latest 9.0.6 version of Microsoft.Data.Sqlite with SQLitePCLRaw.bundle_e_sqlite3mc, but still failed.

The issue https://github.com/dotnet/efcore/issues/35760 opened by me in March was assigned to @cincuranet, but since then nothing happened to solve the issue - although it shouldn't be too difficult to implement a solution. :confused:

[utelle]

I just discovered that the issue dotnet/efcore#35760 was added to the 10.0.0 milestone. There is not yet a known release date, but most likely the issue will be fixed in a not too far future.

[ali50m]

It sounds like something that will take quite some time... Is there something that can be done in this nuget to get around the current limitation?

I currently have to set an update limit for this nuget in my project, but otherwise all the other nugets are up to date.

[utelle]

It sounds like something that will take quite some time...

Well, there isn't much we can do about that, unfortunately. However, you may apply a fix and compile the component Microsoft.Data.SQLite yourself, until an official release is available.

Is there something that can be done in this nuget to get around the current limitation?

If you want to use the latest release, the only workaround is to not specify the password property. Instead issue PRAGMA key='passphrase'; in your application immediately after the database connection was opened. That should work.

I currently have to set an update limit for this nuget in my project, but otherwise all the other nugets are up to date.

Since the issue is not within SQLitePCL.raw you have either to live with the limitation or to apply the workaround mentioned above.

[ali50m]

Thanks @utelle !

I followed your instructions and tried to use the PRAGMA key in the repo to specify the password for the database. Could you help me see if I am using it correctly?

[utelle]

I followed your instructions and tried to use the PRAGMA key in the repo to specify the password for the database. Could you help me see if I am using it correctly?

Unfortunately, I myself don't have experience with Microsoft.Data.SQLite. However, line 33 in your code suggests that the SQLite connection is closed immediately, and that the resulting connection string will be used elsewhere to re-open the database connection. I fear that that will not work, because the connection string will not include any reference to the PRAGMA key command (corresponding to the password property), and therefore re-opening and accessing the database, without performing PRAGMA key first, will result in an error message.

You will have to keep the database connection open while accessing the database.

[ali50m]

Thanks for your input!

I am not so sure about when to perform the start and close connection and make these changes in my large project. I will wait for the team making a fix before taking action. For now, I can only stay on the old version of nuget.

[ericsink]

I don't see an action item here for me. Feel free to open a new issue if there is one.

[utelle]

I don't see an action item here for me.

Indeed, there isn't, because the problem lies (or more precise lay) in the Microsoft.Data.SQLite code. The issue was fixed in .Net 10.