Failed access to database concurrently opened in System.Data.SQLite 1.0.112 (sqlite3 <3.32)

[LarsKloseLke]

I'm not sure if this qualifies as a bug or if it is a fundamental incompatibility due to the different way encryption is implemented, but would like to check which is the case:

Background: we have two separate applications where one (A) creates a SQLite database and the other (B) reads it. In some cases B could open the database while A is still updating it. Historically, both used System.Data.SQLite with RC4 encryption. Current versions use SQLite3Multiciphers. For compatibility, current versions of B need to be able to open databases created by old versions of A.

Problem: ,

• A creates db with System.Data.SQLite 1.0.112 (based on sqlite3 3.31), using WAL mode and RC4 encryption, db connection is still open
• Trying to open a connection to the same db with B fails with "Error: file is not a database", using SQLite3MultiCiphers lib or shell (any version, tested with 1.1.1 and 1.2.4)

In contrast, concurrently opening another connection with System.Data.SQLite <=1.0.112 works.
Also, after A closes the connection (i.e. shm and wal files disappear), opening the db with SQLite3MultiCiphers works.

[utelle]

I'm not sure if this qualifies as a bug or if it is a fundamental incompatibility due to the different way encryption is implemented, but would like to check which is the case:

Well, the encryption of the database file itself is definitely compatible. In fact, you confirm that with the remark at the end of your post:

Also, after A closes the connection (i.e. shm and wal files disappear), opening the db with SQLite3MultiCiphers works.

The problem arises from the fact that the journal files (shm, wal, journal) are unfortunately not compatible. This is documented in the description of the Virtual File System architecture.

Conclusion: It is not possible to access a System.Data.SQLite encrypted database file from System.Data.SQLite <=1.0.112 *and SQLite3 Multiple Ciphers at the same time. In fact, this holds true for all supported cipher schemes.

Background: we have two separate applications where one (A) creates a SQLite database and the other (B) reads it. In some cases B could open the database while A is still updating it. Historically, both used System.Data.SQLite with RC4 encryption. Current versions use SQLite3Multiciphers. For compatibility, current versions of B need to be able to open databases created by old versions of A.

Sorry, this is unfortunately not possible while the old version of A has an active connection to the database due to the incompatibility of the journal files.

Problem: ,

• A creates db with System.Data.SQLite 1.0.112 (based on sqlite3 3.31), using WAL mode and RC4 encryption, db connection is still open

• Trying to open a connection to the same db with B fails with "Error: file is not a database", using SQLite3MultiCiphers lib or shell (any version, tested with 1.1.1 and 1.2.4)

As said this will not work. However, the created encrypted database can be accessed when process A has closed the database connection.

Unfortunately, there isn't much I can do about that, because the journal file incompatibility is a consequence of the implementation based on a Virtual File System. The advantage of the SQLite3 Multiple Ciphers approach is that only 4 small patches to the SQLite code are required to make it work with a new SQLite version.

Others, namely the company Zetetic , offering the SQLCipher product, chose a different approach by re-adding the previous HAS_CODEC API to the source code of SQLite. This requires >50 patches to 12 SQLite source files. And it might get worse the more SQLite evolves over time.

[LarsKloseLke]

Thanks for the quick reply and the thorough explanation of the details and reasoning. I appreciate that.
I already suspected that it is an expected restriction but didn't find the details right away.

While the situation gave us some headaches, we can still live with it since the version mix is (at least in the long run) really an edge case for which we will find another workaround.

Thanks again for your work and quick response!

[utelle]

Thanks for bringing this topic to my attention! I think I should add an explicit note about this runtime incompatibility to the documentation.

[utelle]

In conjunction with upgrading SQLite3 Multiple Ciphers to SQLite 3.35.5 I made a few tests with the System.Data.SQLite 1.0.112 DLL SQLite.Interop.dll (which is actually based on SQLite 3.30.1) using a SQLite shell. I created an encrypted database file in WAL mode. I issued several INSERT and DELETE commands. Then I started another separate SQLite shell based on SQLite3 Multiple Ciphers in parallel and opened the same database in this shell, too. I switched between the 2 shells several times, issued INSERT, DELETE and SELECT commands. Interestingly this worked and I always saw the changes made from the other shell.

The shm and wal journal files existed and grew over time. Even though the incompatibilities described in my previous answer do exist, the serialized access to the encrypted database worked. This seems to be in contrast to your observations. Therefore I would like to further investigate, why you experienced the error "not a database" while the other connection to the database was still active.

Could you please describe in more detail which actions your process A performs while you try to open the database from process B? Could it be that process A has an uncommitted transaction, while you try to open the database from process B? Which command(s) do you use to open the database in process B?

Also nice would be if you could provide me a sample process A executable, so that I could test on my own.

[LarsKloseLke]

Providing a sample exeutable wouldn't be easy because the code is part of a really large codebase. But I will try to provide as much information as possible.
Regarding the System.Data.SQLite version being used: it normally is 1.0.99, but I tried replacing it with 1.0.112 which didn't change anything.

For now, here are some characteristics to start with:

Creator process A

• uses the System.DataSQLite.Interop 1.0.99 DLL (tried 1.0.112, too)
• uses the C API, not .NET
• opens an empty db file that is in journal_mode=delete and RC4-encrypted
• switches to WAL mode:
  PRAGMA journal_mode=WAL; PRAGMA synchronous=OFF;
• uses only one single db connection which is kept open all the time
• writes to the db in many very small explicit transactions using
  BEGIN IMMEDIATE; insert/update/delete; COMMIT;
• would try leaving WAL mode before closing the db, but that didn't happen yet at the point in question
  PRAGMA wal_checkpoint(PASSIVE);
PRAGMA journal_mode=DELETE;
ANALYZE;

For reproduction of Reader process B, I just used the sqlite3mc_shell_x64.exe issueing just these commands:
.open <db file>
PRAGMA cipher=rc4;
PRAGMA key='mykey';
Any select or just .databases fails with "file is not a database".

In theory, B could indeed try to open the db while A still has an open transaction - practically, this is not the case in the tested scenario, because A is "paused" in between two transactions.

I also couldn't reproduce the failure by mimicking A's sequence with statements sent from (an appropriately old version of) SQLiteStudio with the System.Data.SQLite plugin (as I don't really know how to push the SQLite.Interop.dll under a SQLite shell...).
One difference I see is that the actual insert/update statements in A are done using sqlite3_prepare_v2/sqlite3_reset, sqlite3_bind_*, sqlite3_step, sqlite3_clear_bindings, whereas interactively, no prepared statements are used.

[utelle]

Thanks for providing such a detailed description of the scenario.

Providing a sample exeutable wouldn't be easy because the code is part of a really large codebase.

I fully understand that. However, to be able to further investigate what's going on I need a way to reproduce the behaviour causing the "not a database" error. Therefore I'd like to ask a few additional questions.

First, I'd like to find out whether this is a static or dynamic issue. static meaning, using a snapshot of the database files process A is creating (including the shm and wal files) like this:

1. Start process A
2. Let it work for some time
3. Take a snapshot of the database files (copy to a different location) at a point of time you would have started process B
4. Stop process A (optional)
5. Test to open the separate database snapshot with process B (SQLite shell)

Is step 5 successful or not? If it fails, then the snapshot could be used for further tests, if you see a way to make it available for me. If it succeeds, then it is more likely to be a dynamic issue depending on the open database connection performing operations on the database.

In theory, B could indeed try to open the db while A still has an open transaction - practically, this is not the case in the tested scenario, because A is "paused" in between two transactions.

This is good to know. And due to the paused process A I have the hope that a snapshot might allow to reproduce the issue.

Second, I would like to ask which size the database files have when you try to open the database from process B.

Third, I would like you to do a test with a SQLite shell as process B and turn on logging with the command .log stdout before issuing any other command. Quite often one gets more detail information about an error.

Regarding the System.Data.SQLite version being used: it normally is 1.0.99, but I tried replacing it with 1.0.112 which didn't change anything.

I don't think that the version matters.

I see that you use in process A

PRAGMA synchronous=OFF;

Maybe this is the culprit - although it actually didn't matter in my tests so far.

I also couldn't reproduce the failure by mimicking A's sequence with statements sent from (an appropriately old version of) SQLiteStudio with the System.Data.SQLite plugin (as I don't really know how to push the SQLite.Interop.dll under a SQLite shell...).

In principle, it is sufficient to rename the file SQLite.Interop.dll to sqlite3.dll. However, at least the shell coming with SQLite refuses to work with a non-matching SQLite version.

One difference I see is that the actual insert/update statements in A are done using sqlite3_prepare_v2/sqlite3_reset, sqlite3_bind_*, sqlite3_step, sqlite3_clear_bindings, whereas interactively, no prepared statements are used.

I find this not very likely to be the cause, but not absolutely impossible either.

[LarsKloseLke]

I tried as you suggested. Step 5 still fails on the copied database, so it seems to be a "static" issue.
I could provide the database, just let me know of any private channel to send it to you.

[utelle]

I tried as you suggested. Step 5 still fails on the copied database, so it seems to be a "static" issue.

This will allow to investigate more easily what's going on. Great.

I could provide the database, just let me know of any private channel to send it to you.

If the database files are not too big, you can send them by mail (mail address as shown in my GitHub profile). Alternatively, you may upload the files to some cloud storage or to a web server you have access to and just send me the link via mail.

[utelle]

Thanks for providing the test database. That allowed me to fully analyze the issue.

There is indeed a bug in the current implementation of the WAL journal access in SQLite3 Multiple Ciphers. However, without that bug it would still not work to access the database file while the System.Data.SQLite connection is open due to the mentioned incompatibility of the WAL journal files.

Parallel access in rollback journal mode is no problem. And most likely it would be preferrable if parallel access works in WAL journal mode, too. However, this would require additional patches in the original SQLite code to overcome the incompatibility issue.

[utelle]

WAL journal mode compatibility was established in release 1.3.0.