SQLite "database disk image is malformed"

Question

I am having trouble with an app where the SQLite database is getting corrupted. There was the odd case of this previously, but it seems to have become a lot more common after the release of iOS 7.1.

I'm using the SQLite wrapper by Matteo Bertozzi that you can find here: https://github.com/ConnorD/simple-sqlite

The database gets corrupted and spits out the error database disk image is malformed, some queries can be run but the existing data gets messed up.

I have searched high and low and can't find a solution, I'm hoping someone here has some ideas since this is becoming a more common issue after the iOS update.

I've tried these repair commands:

[sqlite executeNonQuery:@"pragma integrity_check"];
[sqlite executeNonQuery:@"reindex nodes"];
[sqlite executeNonQuery:@"reindex pristine"];

And the output was:

SQLite Step Failed: database disk image is malformed
SQLite Prepare Failed: unable to identify the object to be reindexed
 - Query: reindex nodes
SQLite Prepare Failed: unable to identify the object to be reindexed
 - Query: reindex pristine`

With some further digging I found this problem: Core Data and iOS 7: Different behavior of persistent store which mentions issues with SQLite after iOS7.

Though I have no idea how to use NSPersistentStore, so I tried running [sqlite executeNonQuery:@"pragma journal_mode = DELETE"]; and it just said SQLite Step Failed: unknown error.

Is anyone else experiencing this, or point me in the right direction?

In the meantime I feel like this NSPersistentStore is something I potentially should be doing.. will have to look into that.

edit:

From what I've found you only use NSPersistentStore when the database isn't going to be updated, which mine is regularly.

Here is how I open the database:

sqlite = [[Sqlite alloc] init];

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"HomeOpenDatabase8.sql"];

if (![sqlite open:writableDBPath]) {
    NSLog(@"DB Not Writable");
    return;
} else {
    NSLog(@"All good");
}

So I assume I need to find a way to set pragma journal_mode = DELETE this way..?

edit 2:

I'm not convinced it's to do with the journal_mode as I'm not using Core Data - back to the drawing board.

The biggest flag to me is this error popping up so soon after iOs 7.1 was released, surely it can't be a coincidence.. I'll continue trying to replicate the issue on my device.

Johnettajohnette

Are there certain steps that you can perform to reproduce the corruption reliably? – Poston

I haven't been able to replicate it myself which is a massive pain, though I will continue trying. I'm no longer convinced it's the journal_mode either as I'm not using Core Data, so it's back to square one. – Johnettajohnette

Why are you messing with the pragmas at all? – Poston

The integrity_check etc could have potentially repaired the corruption, and the journal_mode was to fix a potential Core Data WAL issue described in the link. – Johnettajohnette

So you are not changing them in general? In that case you need to figure out what causes the corruption. In general Sqlite is very robust unless you compile out some of its safety features (or use it from different threads when it is not set up for that). – Poston

Correct, haven't touched pragma before today trying to repair the db. I did the sqlite3_threadsafe() test mentioned here and it returned '2' which is supposed to mean it is threadsafe. – Johnettajohnette

Not quite true (from the Sqlite docs) -> When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time. – Poston

Hmm interesting. I assume one "database connection" is the [sqlite open:writableDBPath] bit? So if I had sqliteThread to use in the threads it should be fine? It's still very odd that this has just become a common issue after iOS 7.1 though. – Johnettajohnette

I would guess that you've accidentally begun accessing the DB in multi-threaded mode. Even when "threadsafe", SQLite cannot actually handle full multi-threading -- you have to, at a minimum, provide external synchronization to assure that two threads don't operate on the DB simultaneously. As far as I can tell, all "threadsafe" does is enable checks that will USUALLY throw an error if you attempt simultaneous access. – Garbage

You're right. I added locking to my DB service to acess SQLite DB only by one thread and it works now. – Piloting

Answer 1

I had this problem too on iOS 7.0.6 using FMDB. I repaired it by copying to the Mac and using these commands:

http://www.dosomethinghere.com/2013/02/20/fixing-the-sqlite-error-the-database-disk-image-is-malformed/

My database dump was rather large at 200MB so I used Hexfiend to cut the transaction and rollback commands.

Matheson

How did you copy DB file from iPhone to Mac ? – Moriahmoriarty

@de_la_vega_66 you can do it via iTunes or if you have XCode, then search for "Managing Containers on a Device" : developer.apple.com/library/ios/recipes/… – Workroom

Answer 2

sqlite: database disk image is malformed

In your case, you may consider checking your Integrity Check results. That's how I've solved my problem.

Proverbs