Dealing with possible database corruption

[brodycj]

Reports of sqlite corruption on Cordova have been extremely rare but I have a customer who is dealing with this kind of issue right now. In general I recommend that app developers upgrade to a very recent version of the plugin before diving into much deeper investigation.

In case of an app that seems vulnerable to database corruption it is recommended to do PRAGMA integrity_check at certain points and do the following in case it does not report "OK":

• log and report error (sentry.io may be a good friend)
• dump or otherwise capture the users data from the database if possible (sqlite is designed to recover from integrity_check failures, at least to a certain extent)
• obtain a copy of the sqlite database that fails integrity_check (if possible) for further analysis
• remove (delete) the database that fails the integrity check

I found and recommend the following links, despite what looks like some conflicting information:

• http://www.sqlite.org/howtocorrupt.html
• http://sqlite.1065341.n5.nabble.com/Integrity-Check-Failure-Handling-td70289.html
• http://blog.niklasottosson.com/?p=852
• http://www.froebe.net/blog/2015/05/27/error-sqlite-database-is-malformed-solved/

See below for additional discussions.

[brodycj]

Possible causes of corruption on iOS that were already resolved (for reference):

• Transaction problem after page change, WITH POSSIBLE DATA LOSS cordova-sqlite-storage#666 - Transaction problem after page change, WITH POSSIBLE DATA LOSS - closed with workaround solution described in Transaction problem after page change, WITH POSSIBLE DATA LOSS cordova-sqlite-storage#666 (comment)
• litehelpers/Cordova-sqlite-storage#703 ("database malformation") - INCORRECTLY resolved by compile-time DSQLITE_THREADSAFE=2 option (see SQLITE_THREADSAFE=1 compile-time option REQUIRED for iOS/macOS cordova-sqlite-storage#754)
• litehelpers/Cordova-sqlite-storage#716 (possible iOS threading issue) - resolved by using PSPDFThreadSafeMutableDictionary

These options These solutions did not seem to help the customer discussed above.

Additional idea may be to use SQLITE_DEFAULT_SYNCHRONOUS=3 setting as discussed in storesafe/cordova-sqlite-storage#736.

[githoniel]

My android cordova project is using this plugin for 2 years and got some sqlite malform report.
It's very rare and the reason is unknown. Everything is all right before malform. There is no suddenly power down or app crash.

1. It was a time when I'm using WAL journal mode and it seem to malform more often, then I use back the default DELETE journal mode。But it does not stop the sqlite malform.

2. my app is a multi-page app. I was using transaction for every sql command. someTimes I met Unable to begin transaction: cannot start a transaction within a transaction. or SQLITE is locked and the device had to reboot. end transaction before url change does reduce the error rate but cannot resolve these two. Now I removed all transaction as the document tells me and it is resolved these two error. and there is no more malform report so far. since malform is rare I'm not certain it is OK now.

3. When the sqlite file is corrupted, I found something very dangerous. In one of the malform report, update sql command does not really update the sqlite data but return success......

[brodycj]

@githoniel I highly recommend that you push an update with a recent version of this plugin to your users and advise your users to install the update, especially in case of malformed data. The recent updates should take resolve the errors mentioned in your second point (cannot start a transaction within a transaction, SQLITE is locked).

[githoniel]

@brodybits remove transaction is not enough and update the plugin is required ?

[brodycj]

@githoniel please update the plugin. Even sqlBatch may suffer from this kind of an issue since it does the batch in multiple steps (waits for other statements to succeed before issuing COMMIT).

[brodycj]

To address the question in storesafe/cordova-sqlite-storage#703 (comment):

I am also facing the same issue of "database disk image is malformed" in android. I have installed whti plugin near to Aug 2017. So can you please me that this issue is resolved in this plugin version or I have to use another plugin(please share the new plugin url if required)?

Please update with a recent version of this plugin. There are some recent updates to resolve possible data loss risk issues.

[brodycj]

From storesafe/cordova-sqlite-storage#703 (comment):

Thanks a lot, I have read the issue but can you please confirm me more so that I can save my time.

I cannot promise anything without looking at your application, and in many cases I cannot promise an absolute solution even after looking at the application. Database corruption may occur if something really goes wrong with the access. I have already found and fixed some possible causes as described above.

In general it is highly recommended to keep your application up to date with this and other plugins. This should be able to save you time investigating issues that may have already been fixed.

I can think of a couple more ideas that may be worth trying if you continue to see database corruption issues after updating your application:

• use PRAGMA SYNCHRONOUS=3 to get extra durable setting ref: https://www.sqlite.org/pragma.html#pragma_synchronous, EXTRA DURABLE build setting to improve robustness against corruption cordova-sqlite-storage#736
• see if using androidDatabaseImplementation: 2 in sqlitePlugin.openDatabase would make a difference or not

[brodycj]

Additional discussions:

• http://sqlite.1065341.n5.nabble.com/Question-about-threadsafe-td100512.html
• http://sqlite.1065341.n5.nabble.com/Header-corruption-td100516.html
• http://sqlite.1065341.n5.nabble.com/Crash-when-writing-header-td100601.html
• http://sqlite.1065341.n5.nabble.com/More-sqlite-header-questions-td100604.html
• SQLITE_THREADSAFE=1 compile-time option REQUIRED for iOS/macOS cordova-sqlite-storage#754 - SQLITE_THREADSAFE=1 compile-time option wanted REQUIRED for iOS/macOS

[brodycj]

New discussion:

• http://sqlite.1065341.n5.nabble.com/Database-is-malformed-but-no-further-information-td102451.html

[brodycj]

FYI I had an open question about whether or not using WAL could be more robust against possible database corruption ref: #10 (comment), #10 (comment)

From #10 (comment):

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

My conclusion has been to simply use SQLITE_DEFAULT_SYNCHRONOUS=3 setting as discussed in storesafe/cordova-sqlite-storage#736, with no WAL mode enabled.

[brodycj]

Another update I had made around January 2019 was to start using SQLITE_DBCONFIG_DEFENSIVE ref:

• https://www.sqlite.org/c3ref/c_dbconfig_defensive.html
• https://www.sqlite.org/releaselog/3_26_0.html

[brodycj]

I would like to quote a couple of major statements from a new review of some discussions on the SQLite mailing list, for the sake of further discussion:

From these discussions on WAL (linked from #10 (comment)):

• http://sqlite.1065341.n5.nabble.com/WAL-mode-is-reliable-td41264.html
• http://sqlite.1065341.n5.nabble.com/concurrent-writes-and-reads-to-from-DB-td42513.html

I think this is the key information from Hipp:

(4) WAL mode is much more resistant to corruption following a power failure that occurs soon after the disk drive lied and said that content was committed to oxide when in fact it was still in a volatile track buffer. And pretty much all consumer-grade disk drives tell such lies these days.

From http://sqlite.1065341.n5.nabble.com/Database-is-malformed-but-no-further-information-td102451.html:

Chris Brody wrote:
> My understanding is that mobile apps are not 100% predictable since
> they may be randomly suspended or terminated, at any point of time.

But SQLite is designed so that even killing the process will not
corrupt the database, as long as the storage device works correctly.

At this point I hope the "EXTRA DURABLE" build setting I made as discussed in storesafe/cordova-sqlite-storage#736 proves to be sufficiently robust against the common "consumer-grade" hardware issues discussed on the mailing list. I guess only time will tell here.