Database malformation

[kanoshin]

Hello,

in logs I received from iOS clients I found such error:
{"result":{"code":0,"sqliteCode":11,"message":"database disk image is malformed"}}

Percentage of clients who had this error in logs is really small (~0.01%), but I suspect that it might be higher because in some cases our logging depends on database.
Also we have complaints that sometimes it is required to reinstall app to make it work, database malformation might perfectly explain such reports.

Do you know what might cause database malformation?
We use only yours plugin to communicate with database, so 3rd party shouldn't be the case. Our app has multiple pages. Also periodically we close and reopen database because previously we had issues with hanging transactions.

[kanoshin]

I'm thinking about deleting and recreating database in case of corruption. Is it always possible to remove database file or it might be locked?

[brody4hire]

Hi @kanoshin,

This is the first report of corruption with the plugin. I saw quite a bit of possible corruption on the sqlite forum.

In case of corruption it would probably be best to retrieve and then delete/remove the database file, if possible. Unfortunately I have very limited experience with file locking issues. I think sqlite(3) provides some tooling that can recover the data from a corrupted databases in certain cases.

I can also say that there have been some issues with using the standard transaction mechanism together with multi-page applications ref: #666. It should be fixed in the latest version but it would probably still be safer to use the following calls instead:

• db.executeSql() to read data and make single-statement modifications
• db.sqlBatch() to execute multiple changes in an atomic manner (single transaction)

In fact I also raised #690 to discuss the possibility of deprecating the old standard transaction mechanism.

I hope this is somewhat helpful. I hope to address your issue in #702 ("Database might be busy when close is called") within the next few weeks or so.

[brody4hire]

I received a similar report of corruption in a very small percentage of users of an iOS app by email. In that case the application is a single-page app and does not use the now deprecated Web SQL transaction calls.

I can think of a couple possible causes:

1. An iOS sqlite database may be accessed from multiple threads from a thread pool while sqlite3.c is compiled with -DSQLITE_THREADSAFE=1. This is not very likely to cause data corruption since the use of a lock prevents concurrent usage on the same database handle but it would be better to compile with SQLITE_THREADSAFE=2. For reference:

• https://stackoverflow.com/questions/3934641/sqlite-database-disk-image-malformed-on-iphone-sdk
• http://sqlite.org/compile.html#threadsafe

2. iOS threading issue with openDatabase? #716 (comment):

openDBs is an NSMutableDictionary, which isn't designed for thread safety...

Excellent workaround / solution is given in #716.

For the future it would be better to either run all sqlite operations in a single background thread or one background thread per db, just like the implementation for Android. Possible reference:

• https://stackoverflow.com/questions/7055424/ios-start-background-thread