sqlite database disk image malformed on iPhone SDK

I'm having an issue with a new application on the iPhone SDK using SQLite as the DB backend.

Occasionally, my app will stop loading data to my UITableViews and after downloading the device DB via the Organizer I can access the SQLite DB via the command line. I can query certain tables fine but not others without getting an "SQL error: database disk image is malformed" error. See a sqlite session below:

    SQLite version 3.6.17
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite> select * from user;
    1|cpjolicoeur@gmail.com|cpjolicoeur||4d055e38bb1d3758|image/gif|cartoonme_avatar.gif||Crai开发者_开发技巧g|Jolicoeur|1|1
    sqlite> select * from item;
    SQL error: database disk image is malformed
    sqlite> 

In this example my user table works fine but my item table is malformed, which corresponds with what I am seeing in my app where the items dont load. The app doesnt crash, the data just doesnt load because of this malformed error.

Any ideas why this is happening? My only thought is that maybe the DB is being corrupted because I am writing to the SQLite DB via a background thread within the app. I download data from a webserver via an NSOperationQueue in a background thread and update the SQLite DB with the data downloaded. Would writing to the DB in a background thread (while potentially reading from the main thread) corrupt the DB, or is it something else?

You have to be very careful about background threads accessing the database while debugging! This is because when the debugger halts processing (such as at a breakpoint) all threads are paused, including threads that may be in the middle of a database call, somewhere in between a database "open" and a database "close" call.

If you are halted at a breakpoint, and click the stop sign in Xcode, your app exits immediately. This very often causes errors such as the one you saw, or the "corrupted database" error.

There really isn't any solution (because there is no way to modify the behavior of "stop tasks", but I have evolved some techniques to mitigate it: 1. Add code to detect the app entering the background and have your db operations gracefully stop. 2. Never use the stop sign to halt processing while debugging. Instead, when done with a breakpoint then "continue", hit the home button on the simulator or device (which should trigger the code you added in step 1), wait for the app to background, THEN you can stop the run.

In my case this had to do with iOS 7: On iOS 7 Core Data now uses the SQLite WAL journaling mode which writes data to a .db-wal file instead of directly to the .db file. In my app, I would copy a prepared .db file into Library/Application Support during an app update. The problem was that an old .db-wal file was still in that directory and I only replaced the .db file. That way, I ended up with a .db file that was out of sync with the old .db-wal file.

There are two solutions to this problem:

1. Make sure the .db, .db-wal and .db-shm files are deleted before you copy your new .db file into place.
2. Go back to the old pre-iOS 7 behavior like so: https://stackoverflow.com/a/18870738/171933

Depends on how SQLite is compiled, it may or may not be thread-safe. If you're using the built-in one, it may not have the compile-time options you're looking for.

For our app, we had to roll our own SQLite to add full text search. Take a look at this page.