WAL pitfalls

[brodycj]

As I said in https://github.com/litehelpers/Cordova-sqlite-storage/issues/643#issuecomment-285372916:

In general I do not really favor WAL (write ahead logging ref: https://www.sqlite.org/wal.html) since it would require the overhead of checkpointing at certain intervals. I also just discovered an interesting issue at http://stackoverflow.com/questions/39149065/sqlite-write-ahead-logging-wal-journal-mode-with-attached-database.

[philonor]

@brodybits What is the current status on WAL? Is it still disabled by default for the cordova-sqlite-** plugins, or is it necessary to disable it manually? I have a weird issue on iOS with https://github.com/litehelpers/Cordova-sqlite-evcore-extbuild-free where the database gets emptied from time to time, but unfortunately I can't reproduce it yet, so I'm trying to ensure that I am not running into any pitfall I'm not aware of. https://stackoverflow.com/a/18870738/5062057 https://stackoverflow.com/q/17487306/5062057

[brodycj]

What is the current status on WAL?

WAL is not be enabled on any platforms. Test at litehelpers / Cordova-sqlite-storage / spec / www / spec / sqlite-version-test.js#L126-L144 shows PRAGMA journal_mode returns delete or persist as described at: https://sqlite.org/pragma.html#pragma_journal_mode

It should be no problem to change using a PRAGMA statement, though I have not tested this.

https://stackoverflow.com/a/18870738/5062057 https://stackoverflow.com/q/17487306/5062057

Links expanded to:

• https://stackoverflow.com/questions/18870387/core-data-and-ios-7-different-behavior-of-persistent-store/18870738#18870738
• https://stackoverflow.com/questions/17487306/ios-coredata-are-there-any-disadvantages-to-enabling-sqlite-wal-write-ahead

This plugin does not deal with iOS CoreData.

For the next major release (https://github.com/litehelpers/Cordova-sqlite-storage/issues/687) I would like to update the plugin to explicitly set PRAGMA journal_mode to persist in all cases in order to minimize the number of possible deviations between the different platform implementations.

[brodycj]

More troubles with WAL at: http://sqlite.1065341.n5.nabble.com/Atomic-DELETE-index-optimisation-td99277.html

[brodycj]

I was thinking that WAL may be more robust against possible database corruption and found a couple threads that confirm WAL may be more reliable, at least in certain cases:

• http://sqlite.1065341.n5.nabble.com/WAL-mode-is-reliable-td41264.html
• https://marc.info/?l=sqlite-users&m=132052237302135&w=2 http://sqlite.1065341.n5.nabble.com/concurrent-writes-and-reads-to-from-DB-td42513.html

[brodycj]

Adding the following Q&A that I initiated on the SQLite forum for reference (with a single response which I think has no new information):

• http://sqlite.1065341.n5.nabble.com/Is-WAL-mode-more-robust-against-corruption-td99624.html

[brodycj]

Here is an interesting, positive case for WAL on the server side, at least: https://medium.com/@rbranson/sharing-sqlite-databases-across-containers-is-surprisingly-brilliant-bacb8d753054

ref: http://sqlite.1065341.n5.nabble.com/Sharing-an-SQLite-database-across-containers-tc110391.html

[brodycj]

I just found a couple more resources today:

• https://www.digitalforensics.com/blog/forensic-analysis-of-sqlite-write-ahead-log-wal/ - could prove suboptimal for protection of privacy
• https://blog.xojo.com/2015/12/21/speed-up-sqlite-with-write-ahead-logging/ - see comment below

[brodycj]

• https://blog.xojo.com/2015/12/21/speed-up-sqlite-with-write-ahead-logging/

includes a description of how to use a PRAGMA for manual checkpointing - definitely looks like a drawback to me