Interoperability issues between releases 1.1.1 and 1.3.x with hot WAL journal

[LarsKloseV]

The following scenario may seem somewhat strange or constructed, but it depicts a real situation given the sqlite3mc release 1.1.1 we have in the field (and due to #39 I'm even somewhat happy we didn't switch).

Overall scenario: one application A writes to a new database in WAL mode, data inserted in a transaction is already commited, then the application crashes or is killed, leaving a hot WAL journal behind (The fact that SQLite is normally able to incorporate that journal into the database when the database is opened the next time meets a desirable design goal in our case, i.e. providing fail-over safety). Another application B should then still be able to open the database with all the commited data being available. Eventually, it will then switch back to DELETE journal mode.
This scenario worked well with System.Data.SQLite (used before we switched to sqlite3mc). Note that RC4 encryption here is only a legacy thing, current databases use chacha20 but we need to provide compatibility.
Now, depending on the sqlite3mc versions in use for A and B we see different errors as described below.

I recreated the issue just using the appropriate versions of the sqlite3mc shell (binaries as provided in the github releases) using the scripts below. "master" means shell binary built from sources downloaded after commit 43bd436.

Database creation (using 1.1.1):

.open newtest.db
pragma cipher=rc4;
pragma rekey='123';
create table test (x int);
pragma journal_mode=wal;
begin immediate;
insert into test (x) values (1);
commit;
*** kill shell here ***

Reading database with hot WAL journal:

.open newtest.db
pragma cipher=rc4;
pragma key='123';
pragma journal_mode;

1.1.1 and 1.3.1: wal
master: delete
select * from test;
1.1.1 and 1.3.1: no data
master: Error: no such table: test
.open (close db)
1.1.1 and 1.3.1: wal journal discarded, data lost
master: wal journal still exists, but:

.open newtest.db
.dbinfo

master: unable to read database header

It's unclear to me if the database created with 1.1.1 is broken (but can be read with 1.1.1 with no problems) or 1.3.x stumbles or maybe both.

[utelle]

The following scenario may seem somewhat strange or constructed, but it depicts a real situation given the sqlite3mc release 1.1.1 we have in the field (and due to #39 I'm even somewhat happy we didn't switch).

I'm very sorry that releases 1.3.0 and 1.3.1 contain such a dumb bug (#39). However, such things happen unfortunately - as even SQLite itself demonstrated recently with 5 bug fix releases in rapid succession (from version 3.35.0 to 3.35.5).

Overall scenario: one application A writes to a new database in WAL mode, data inserted in a transaction is already commited, then the application crashes or is killed, leaving a hot WAL journal behind (The fact that SQLite is normally able to incorporate that journal into the database when the database is opened the next time meets a desirable design goal in our case, i.e. providing fail-over safety).

Even without any application crash it is a typical situation that concurrently running applications access the WAL journal file to read the latest database modifications. And the changes introduced with SQLite3 Multiple Ciphers version 1.3.x add support to access databases in WAL journal mode from old legacy applications (like System.Data.SQLite) and new applications using SQLite3 Multiple Ciphers. However, this compatibility comes at a price: hot WAL journal files created by SQLite3 Multiple Ciphers versions 1.2.5 or below are not compatible with hot WAL journal files created by versions 1.3.0 or later.

Another application B should then still be able to open the database with all the commited data being available. Eventually, it will then switch back to DELETE journal mode.
This scenario worked well with System.Data.SQLite (used before we switched to sqlite3mc).

This is not surprising, because System.Data.SQLite used the (no longer existing) SQLITE_HAS_CODEC API for encryption support.

Now, depending on the sqlite3mc versions in use for A and B we see different errors as described below.

I recreated the issue just using the appropriate versions of the sqlite3mc shell (binaries as provided in the github releases) using the scripts below. "master" means shell binary built from sources downloaded after commit 43bd436.

Database creation (using 1.1.1):

Reading database with hot WAL journal:

1.1.1 and 1.3.1: wal
master: delete

Is this the reported journal mode?

select * from test;
1.1.1 and 1.3.1: no data
master: Error: no such table: test

.open (close db)
1.1.1 and 1.3.1: wal journal discarded, data lost

master: wal journal still exists, but:

.open newtest.db
.dbinfo

master: unable to read database header

All this is not really surprising, because the WAL journal file created by version 1.1.1 can't be accessed successfully from newer versions.

It's unclear to me if the database created with 1.1.1 is broken (but can be read with 1.1.1 with no problems) or 1.3.x stumbles or maybe both.

Versions from 1.0.0 up to 1.2.5 should be able to operate in WAL mode without problems. The WAL journal file is consistent across those versions. However, concurrent access with legacy applications will not work.

Version 1.3.0 introduced WAL journal mode compatibility with legacy applications, so that concurrent access with legacy applications works (at least it would if bug #39 did not exist). However, concurrent access with versions 1.2.5 and below will unfortunately not work due to incompatible WAL journal files.

Unfortunately there exists no "fix" for this problem. It is unlikely that I revert the WAL journal file format to that of version 1.2.5 (or below), because in my opinion compatibility with legacy applications in WAL mode is more important than compatibility with prior versions of SQLite3 Multiple Ciphers.

For a transitions from version 1.2.5 or below to version 1.3.x it will be necessary to perform a full WAL checkpoint operation, for example with

PRAGMA wal_checkpoint(TRUNCATE);

This guarantees that the WAL file is empty, and the database file itself will be accessed.

[utelle]

To facilitate the transition from version 1.2.5 or below to version 1.3.x and above, an additional pragma could be implemented that could be used to switch between the 2 WAL journal implementations, so that a "version 1.3.x or above" application could open a hot WAL journal created by a "version 1.2.5 or below" application. The pragma could be named PRAGMA LEGACY_MC_WAL=[ 0 | 1 ], where the value 0 (= version 1.3.x implementation) would be the default.

However, I'm not eager to implement such a pragma, because I don't want to encourage the use of the previous implementation - its WAL journal mode could still be flawed, you know. There is a reason why I haven't removed yet the remark "This project is Work In Progress. As of March 2021, the code base is now rather stable, however, further major code modifications and/or reorganizations may still occur." from the readme.md file of the project.

[LarsKloseV]

the changes introduced with SQLite3 Multiple Ciphers version 1.3.x add support to access databases in WAL journal mode from old legacy applications (like System.Data.SQLite) and new applications using SQLite3 Multiple Ciphers. However, this compatibility comes at a price: hot WAL journal files created by SQLite3 Multiple Ciphers versions 1.2.5 or below are not compatible with hot WAL journal files created by versions 1.3.0 or later.

Versions from 1.0.0 up to 1.2.5 should be able to operate in WAL mode without problems. The WAL journal file is consistent across those versions. However, concurrent access with legacy applications will not work.
Version 1.3.0 introduced WAL journal mode compatibility with legacy applications, so that concurrent access with legacy applications works (at least it would if bug #39 did not exist). However, concurrent access with versions 1.2.5 and below will unfortunately not work due to incompatible WAL journal files.

Thanks for explaining in detail!

in my opinion compatibility with legacy applications in WAL mode is more important than compatibility with prior versions of SQLite3 Multiple Ciphers.

Clearly this is the correct decision!

Database creation (using 1.1.1):
Reading database with hot WAL journal:
1.1.1 and 1.3.1: wal
master: delete

Is this the reported journal mode?

Yes.

select * from test;
1.1.1 and 1.3.1: no data
master: Error: no such table: test
.open (close db)
1.1.1 and 1.3.1: wal journal discarded, data lost
master: wal journal still exists, but:

.open newtest.db
.dbinfo

master: unable to read database header

All this is not really surprising, because the WAL journal file created by version 1.1.1 can't be accessed successfully from newer versions.

Ok. Still, it is unclear to me why I cannot get the data commited immediately before the crash (see point marked with no data after the select), neither with 1.1.1 nor 1.2.5, regardless if I create the db with 1.1.1 or 1.2.5 (just tested, also regardless from cipher being used). There should be no compatibility issue involved there.
Even when operating both sides with 1.2.5, it seems I would need to explicitly do a PRAGMA wal_checkpoint(TRUNCATE); after each single commit to get the expected behaviour - otherwise content of a hot WAL journal after a crash is always lost! Is there any way that such a database inclusive content of the hot journal could be rescued? For us, this is outside normal operations but may be relavant in customer support cases.

For a transition from version 1.2.5 or below to version 1.3.x it will be necessary to perform a full WAL checkpoint operation, for example with
PRAGMA wal_checkpoint(TRUNCATE);
This guarantees that the WAL file is empty, and the database file itself will be accessed.

This would only help if the checkpointing would be done immediately after each single commit. Aside from the fact that this is not the way it should work, and we cannot change released code, I guess performance would suffer.

To facilitate the transition from version 1.2.5 or below to version 1.3.x and above, an additional pragma could be implemented that could be used to switch between the 2 WAL journal implementations

I fully understand your reluctancy to introduce that PRAGMA.
The main question for me now is indeed, how to achieve limited interoperability between <=1.2.5 and >=1.3.0.
Depending on the answers to my following questions, I'd be happy to get away in another way.

• Is there a way to detect which WAL journal format a database is in, either without touching the WAL itself or at least without any risk of losing/destroying it, regardless of the WAL format?
• Is there a way to explicitly open a connection to an existing database with disregard of the WAL (only checkpointed content in the database itself)
• At what point would the PRAGMA LEGACY_MC_WAL need to be issued? Would it be possible to read data from the database itself before doing so?

One option is to ship two sqlite3 DLLs and use the appropriate one. Just as I would need to know which value the PRAGMA would need to be set to, it would be necessary to detect which DLL to use.
I know that as a last resort I could make a copy of the db file without shm/wal file, open that and conclude from db contents with which of our releases and thus which sqlite variant the db was created, then choose the compatible DLL - but a less hacky approach would ne nice.

There is a reason why I haven't removed yet the remark "This project is Work In Progress. As of March 2021, the code base is now rather stable, however, further major code modifications and/or reorganizations may still occur." from the readme.md file of the project.

I was aware of it but never thought that the on-disk db or journal format would possibly be affected, since I understood that compatibility was the main goal. Maybe I should have asked explicitly about risks and side effects ;-)

[utelle]

Versions from 1.0.0 up to 1.2.5 should be able to operate in WAL mode without problems. The WAL journal file is consistent across those versions.

In the meantime I did some further research. I'm not absolutely sure whether WAL mode really works properly for those versions under all circumstances. I know, this is not good news.

Version 1.3.0 introduced WAL journal mode compatibility with legacy applications, so that concurrent access with legacy applications works (at least it would if bug #39 did not exist). However, concurrent access with versions 1.2.5 and below will unfortunately not work due to incompatible WAL journal files.

The revision represented by commit 43bd436 should now work flawlessly in WAL mode.

Still, it is unclear to me why I cannot get the data commited immediately before the crash (see point marked with no data after the select), neither with 1.1.1 nor 1.2.5, regardless if I create the db with 1.1.1 or 1.2.5 (just tested, also regardless from cipher being used). There should be no compatibility issue involved there.

I'll try to find some time to investigate what's going on. I fear that somehow the WAL journal file is corrupted. However, I would have expected a more verbose error message. Maybe turning on logging in the shell (.log stdout) displays more error details.

Even when operating both sides with 1.2.5, it seems I would need to explicitly do a PRAGMA wal_checkpoint(TRUNCATE); after each single commit to get the expected behaviour - otherwise content of a hot WAL journal after a crash is always lost! Is there any way that such a database inclusive content of the hot journal could be rescued?

Good question. I will have to look into the issue, before I can give a reliable answer.

For us, this is outside normal operations but may be relevant in customer support cases.

Yes, I fully understand that.

The problem is definitely related to WAL journal mode.

Rollback journal mode works - at least under normal conditions. If a legacy application crashes and leaves a rollback journal behind, accessing it with SQlite3 Multiple Ciphers may not work.

For a transition from version 1.2.5 or below to version 1.3.x it will be necessary to perform a full WAL checkpoint operation, for example with
PRAGMA wal_checkpoint(TRUNCATE);
This guarantees that the WAL file is empty, and the database file itself will be accessed.

This would only help if the checkpointing would be done immediately after each single commit. Aside from the fact that this is not the way it should work, and we cannot change released code, I guess performance would suffer.

Of course, this is not the way it should work.

If the database with hot WAL journal was created by a legacy application (i.e. System.Data.SQLite), one could take the database + WAL files and execute a checkpoint operation with a legacy version (and maybe even switch to DELETE journal mode), before converting the database.

If the database with hot WAL journal was created by SQLite3 Multiple Ciphers version <= 1.2.5, upgrading to version 1.3.x will be difficult, unfortunately.

To facilitate the transition from version 1.2.5 or below to version 1.3.x and above, an additional pragma could be implemented that could be used to switch between the 2 WAL journal implementations

I fully understand your reluctancy to introduce that PRAGMA.

Especially, because I'm not absolutely sure whether it will really solve the problem.

The main question for me now is indeed, how to achieve limited interoperability between <=1.2.5 and >=1.3.0.
Depending on the answers to my following questions, I'd be happy to get away in another way.

• Is there a way to detect which WAL journal format a database is in, either without touching the WAL itself or at least without any risk of losing/destroying it, regardless of the WAL format?

Difficult. When accessing the WAL file directly (not via SQLite), one could read the WAL frames, calculate the checksums and check whether they match with the page content within the frame. For versions <= 1.2.5 they would not match, for versions > 1.3.1 they would match. However, this would require to reuse some of the WAL code in SQLite.

• Is there a way to explicitly open a connection to an existing database with disregard of the WAL (only checkpointed content in the database itself)

No. If you have a non-empty WAL journal which was not checkpointed, then large portions of the database content can reside in the WAL journal.

• At what point would the PRAGMA LEGACY_MC_WAL need to be issued? Would it be possible to read data from the database itself before doing so?

You will have to specify the pragma in advance, before accessing the database. The reason is that even the current page 1 containing the database header may reside in the WAL journal.

One option is to ship two sqlite3 DLLs and use the appropriate one. Just as I would need to know which value the PRAGMA would need to be set to, it would be necessary to detect which DLL to use.

Unless you already load the SQLite DLL dynamically, it may be rather cumbersome having to deal with different DLL versions. Issuing just a pragma might be more convenient.

I know that as a last resort I could make a copy of the db file without shm/wal file, open that and conclude from db contents with which of our releases and thus which sqlite variant the db was created, then choose the compatible DLL - but a less hacky approach would ne nice.

Well, dropping the WAL file frquently means loosing database content. That's certainly not an ideal approach.

There is a reason why I haven't removed yet the remark _"This project is Work In Progress.

I was aware of it but never thought that the on-disk db or journal format would possibly be affected, since I understood that compatibility was the main goal.

Compatibility of the database file itself and the supported cipher schemes was and is a major goal. And up to now it is fulfilled. However, with the journal files it's different:

The rollback journal files are definitely not compatible with legacy applications, but this is usually not an issue, because only one writer can access the database and the rollback journal at a time, and after the commit (or rollback) succeeded, the journal file will be deleted. Only, if the rollback file needs to be accessed after a crash to restore the database, then compatibility matters.

For versions <= 1.2.5 the WAL journal files are not compatible, for versions > 1.3.1 they will be compatible even with legacy applications.

Maybe I should have asked explicitly about risks and side effects ;-)

Yes, most likely that would have been wise. 😢

I'm really sorry that such big trouble with the WAL file handling now surfaced.

[utelle]

Bad news, unfortunately. I checked with version 1.2.5 what's going on on reopening the database after killing the previous connection right after executing the commit.

SQLite tries to recover the WAL journal as it should, but fails due to incorrect WAL frame checksums. However, SQLite simply ignores invalid WAL frames and resets the WAL journal thereafter. Therefore the table is empty and the select command doesn't show any data.

SQLite isn't to blame. It is a bug in the implementation of SQLite3 Multiple Ciphers. SQLite3 Multiple Ciphers version 1.2.5 (and below) encrypts and decrypts only WAL journal read/write requests, if the requested amount of data matches the page size. However, in the function that tries to recover the WAL journal, this is not the case: SQLite asks for a complete WAL journal frame, that is, a frame header plus a database page. For proper operation it would be necessary to decrypt the page data of the frame.

Maybe introducing a compatibility pragma accompanied by fixing the missing WAL frame encryption/decryption would allow to recover a database with hot WAL journal - no guarantee, though.

The problem is I really don't like the idea, to officially add such a pragma, because if I do so, I know for sure that there will be developers using this feature forever. In fact, using such a pragma for more than recovering a database is asking for trouble in the long run, because I also found out that there is a function in the WAL implementation of SQLite itself that potentially splits a frame write into 2 separate write operations of unpredictable chunk size - almost impossible to handle correctly in the previous implementation (version <= 1.2.5), no problem in the current implementation (version > 1.3.1).

[LarsKloseV]

Reiterating, just to make sure I understand everything correctly:

• A hot WAL journal from sqlite3mc <= 1.2.5 is currently not recoverable in any way
• the theoretical compatibility pragma discussed above would in the best case allow to open such a database, do a checkpoint and switch out of WAL mode

And two more questions:

• Would it be a valid assumption that a table including its data which was created/inserted before even entering WAL mode and not touched thereafter always resides in the main database file, thus always accessible eben with a lost hot WAL? Or is it possible that even that data is moved into the WAL journal at some point?
• The aforementioned SQLite function which potentially splits a WAL frame in two write operations: if this happens, does it mean a corrupt WAL in versions <=1.2.5? If so, is it possible to say, under what circumstances that will happen? Or, maybe easier to answer: can this happen if there is no concurrent access at all? Or if there is only one writer and it never checkpoints until after the last commit and then closes the connection, leaving at most one other reader behind?

If the assumption in the first question is valid and if the frame split can be ruled out as in the second question, would you assume that the recovery scenario using the pragma would be working in general?
No, I'm not trying to nail you down on any implied promises, I'm just weighing our options ;-)

[utelle]

• A hot WAL journal from sqlite3mc <= 1.2.5 is currently not recoverable in any way

Yes. At least not with any of the existing releases. An adjusted implementation may be able to recover a hot WAL journal, but I can't guarantee that it will work under all circumstances.

• the theoretical compatibility pragma discussed above would in the best case allow to open such a database, do a checkpoint and switch out of WAL mode

That would be the recommended use of such a pragma. Theoretically, one could continue to operate in WAL journal mode with enabled compatibility pragma, but after what I learned about WAL mode in the meantime I would strongly suggest not to do that.

I have to admit that I should have analyzed WAL mode more carefully, before starting to implement the WAL journal encryption. So, I have to take the blame for the mess we are now in.

And two more questions:

• Would it be a valid assumption that a table including its data which was created/inserted before even entering WAL mode and not touched thereafter always resides in the main database file, thus always accessible eben with a lost hot WAL? Or is it possible that even that data is moved into the WAL journal at some point?

If the WAL journal can't be recovered, the state of the database will be that of the last successful WAL checkpoint. That is, if the content of a table was not changed after the last WAL checkpoint, it will be retrievable from the database even without the WAL journal.

• The aforementioned SQLite function which potentially splits a WAL frame in two write operations: if this happens, does it mean a corrupt WAL in versions <=1.2.5?

The function in question is walWriteToLog (see wal.c) in SQLite's WAL journal implementation. If a requested write operation crosses a sync point, the write request will be split into 2 chunks. It is very unlikely that the chunk size will match the database page size. Therefore the sqlite3mc VFS implementation (<= version 1.2.5) would not encrypt the content, thus in effect corrupting the WAL journal.

However, according to the comments in wal.c this happens only, if operating with PRAGMA SYNCHRONOUS=FULL; - which happens to be the default, if this pragma isn't used explicitly.

If so, is it possible to say, under what circumstances that will happen?

See above.

Or, maybe easier to answer: can this happen if there is no concurrent access at all?

Yes, because it has nothing to do with concurrency.

Or if there is only one writer and it never checkpoints until after the last commit and then closes the connection, leaving at most one other reader behind?

It depends on the setting of PRAGMA SYNCHRONOUS;.

If the assumption in the first question is valid and if the frame split can be ruled out as in the second question, would you assume that the recovery scenario using the pragma would be working in general?

If I remember correctly, you use PRAGMA SYNCHRONOUS=OFF; in the application in question. So, at least you shouldn't be affected by the frame split problem. That is, in your recovery scenario I would be optimistic that the compatibility pragma would work. No guarantee, though.

No, I'm not trying to nail you down on any implied promises, I'm just weighing our options ;-)

I can't make any promises, but for sure I will try to help you to sort things out. You may contact me via private mail to discuss an individual solution, if necessary.

[utelle]

A small update regarding the split wal frame problem:

This problem will only occur if the device characteristics of the device where the database file resides do not include the Powersafe Overwrite property. As the default SQLite assumes the presence of this property. That is, usually an application should not experience the problem.

Additionally, I implemented the compatibility pragma in my local development environment in the meantime. This allowed me to successfully recover a hot WAL journal left behind by a crashed application using sqlite3mc version 1.2.5.

The wording compatibility pragma is a bit misleading, because it does not mean that an application with this pragma enabled could be used in parallel to an application using sqlite3mc version <= 1.2.5. It only allows to recover a hot WAL journal. It is strongly recommended to retire applications using sqlite3mc version <= 1.2.5 in WAL journal mode as soon as possible.

[utelle]

I decided to make the WAL compatibility configuration setting publicly available. Commit b8e0cd7 provides the adjusted code. Please note that I renamed the parameter to mc_legacy_wal. This parameter may be set via pragma or via URI parameter.

Please also note that the parameter setting can't be changed after the database connection has been established. That is, the sequence of pragmas should be as follows:

-- Select cipher scheme
PRAGMA cipher=chacha20;
-- Set WAL compatibility with versions <= 1.2.5
PRAGMA mc_legacy_wal=1;
-- Set passphrase
PRAGMA key='passphrase';
-- Change journal mode to recover hot WAL journal
PRAGMA journal_mode=delete;

Thereafter the database connection should be closed. On reopening the database connection do not set mc_legacy_wal (that is, use implicitly mc_legacy_wal=0).

[LarsKloseV]

Thanks for investigating further.

Do you think it would be possible to make sqlite3mc fail-safe in the regard that on opening without the new pragma, it just fails on hot journals from <=1.2.5 consistently without deleting the WAL?
I.e. before this happens:

SQLite tries to recover the WAL journal as it should, but fails due to incorrect WAL frame checksums. However, SQLite simply ignores invalid WAL frames and resets the WAL journal thereafter.

[utelle]

Do you think it would be possible to make sqlite3mc fail-safe in the regard that on opening without the new pragma, it just fails on hot journals from <=1.2.5 consistently without deleting the WAL?

Not without patching SQLite itself.

I.e. before this happens:

SQLite tries to recover the WAL journal as it should, but fails due to incorrect WAL frame checksums. However, SQLite simply ignores invalid WAL frames and resets the WAL journal thereafter.

The problem is that from SQLite's perspective it is totally normal to come across "invalid" WAL frames (which are then silently ignored), because WAL frames can be reused and may not belong to the current chain of frames. On opening a database SQLite checks if the database is in WAL mode, and if this is the case, SQLite checks whether a WAL file exists. If yes, SQLite tries to recover whatever is in the WAL journal. After that operation the WAL journal is reset to force a consistent WAL state.

[utelle]

Release 1.3.2 fixes the underlying problems and provides a configuration parameter to facilitate the transition from prior versions. The documentation has been updated accordingly. Therefore this issue will be closed. Reopen it if necessary.