WAL pitfalls
brodybits opened this issue · 8 comments
As I said in storesafe/cordova-sqlite-storage#643 (comment):
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.
@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
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 (storesafe/cordova-sqlite-storage#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.
More troubles with WAL at: http://sqlite.1065341.n5.nabble.com/Atomic-DELETE-index-optimisation-td99277.html
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:
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):
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
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
includes a description of how to use a PRAGMA for manual checkpointing - definitely looks like a drawback to me