-
-
Notifications
You must be signed in to change notification settings - Fork 78
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Failed access to database concurrently opened in System.Data.SQLite 1.0.112 (sqlite3 <3.32) #37
Comments
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:
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.
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.
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 |
Thanks for the quick reply and the thorough explanation of the details and reasoning. I appreciate that. 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! |
Thanks for bringing this topic to my attention! I think I should add an explicit note about this runtime incompatibility to the documentation. |
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 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. |
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. For now, here are some characteristics to start with: Creator process A
For reproduction of Reader process B, I just used the sqlite3mc_shell_x64.exe issueing just these commands: 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...). |
Thanks for providing such a detailed description of the scenario.
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:
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.
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
I don't think that the version matters. I see that you use in process A
Maybe this is the culprit - although it actually didn't matter in my tests so far.
In principle, it is sufficient to rename the file
I find this not very likely to be the cause, but not absolutely impossible either. |
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.
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. |
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. |
WAL journal mode compatibility was established in release 1.3.0. |
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: ,
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.
The text was updated successfully, but these errors were encountered: