// do something here

Well, it has been over a year since I last had a customer with a corrupted database problem. Which means that I guess I was overdue. And what problem it is.

My customer is reporting that they now get the following error on their iPad when they try to do something with their database:

Just in case you can’t read the text in the photo, it says:

The operation couldn’t be completed. (Cocoa error 19).

After some trouble getting the file (it is over 4 GB, not easy to e-mail), I saw a bunch of errors pop up in my debug console, including the dreaded “The database disk image is malformed”, which to me indicated that I needed to try and repair the database file. So I punched up my blog post from February of last year to try and run a repair on it.

Unfortunately, I ran into one dead end after another. I was able to dump the data out of the original database file, which produced a text file of over 8 GB. However, no matter what I tried to do to import this data back into a new database, I would end up with an empty new database.

On a lark, I decided to switch to Windows to try and fix the database file. After downloading the tools from the SQLite web site, Here is what I ended up with on my Windows computer:

C:\database>dir
 Volume in drive C is Windows7_OS
 Volume Serial Number is 50B0-39AA
 
 Directory of C:\database
 
03/14/2014  06:27 PM    <DIR>          .
03/14/2014  06:27 PM    <DIR>          ..
03/14/2014  06:16 PM     4,251,181,056 DB.sqlite
03/11/2014  11:33 AM           536,064 sqlite3.exe
03/11/2014  11:34 AM         1,388,032 sqlite3_analyzer.exe
               3 File(s)  4,253,105,152 bytes
               2 Dir(s)  240,466,235,392 bytes free

So I fired up a command prompt and tried to run some SQLite commands:

C:\database>sqlite3
SQLite version 3.8.4.1 2014-03-11 15:27:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open DB.sqlite
sqlite> .tables
.
.
Some table names listed here
.
.
sqlite> pragma integrity_check;
*** in database main ***
On tree page 1033353 cell 0: Failed to read ptrmap key=-280958431
On tree page 1033353 cell 0: 152 of 407 pages missing from overflow list starting at 1037333
Page 1037588 is never used
Page 1037589 is never used
.
.
You get the idea...
.
.
Page 1037684 is never used
Page 1037685 is never used

After going through the series of steps that I outlined in my message from last year, it appeared to process the 8 GB text file into a new database that was almost the same size as the original database file. Here was what my directory then looked like:

C:\database>dir
 Volume in drive C is Windows7_OS
 Volume Serial Number is 50B0-39AA
 
 Directory of C:\database
 
03/14/2014  06:45 PM    <DIR>          .
03/14/2014  06:45 PM    <DIR>          ..
03/14/2014  06:41 PM     8,428,761,778 dump_all.sql
03/14/2014  06:16 PM     4,251,181,056 DB.sqlite
03/14/2014  06:45 PM     4,239,932,416 DB2.sqlite
03/11/2014  11:33 AM           536,064 sqlite3.exe
03/11/2014  11:34 AM         1,388,032 sqlite3_analyzer.exe
               5 File(s) 16,921,799,346 bytes
               2 Dir(s)  227,795,791,872 bytes free

This had me feeling pretty good. Just to check, here is what I did next:

C:\database>sqlite3 DB2.sqlite
SQLite version 3.8.4.1 2014-03-11 15:27:36
Enter ".help" for usage hints.
sqlite> .tables
.
.
Some table names listed here
.
.
sqlite> select count(*) from table_name;
8716
sqlite> pragma integrity_check;
ok
sqlite> vacuum;
sqlite> .exit

It looks like success. I will have to take this data file and put it back on one of my test iPads when I get back into the office and try it out.

BTW, Happy Pi Day to everyone out there, whether you are a math person or not.

ADDENDUM: After putting the rebuilt SQLite database file back onto my own test iPad and simulator, it worked fine with no issues or cryptic error messages.

I suppose it was only a matter of time before someone’s database got corrupted. Of course, customers don’t want to hear that their issue is a once-in-a-year issue, they just want to get their data back. (Of course, they have used the app for a long time and not done any backups, but that topic could fill an entire blog.) Here are the steps I took to fix the SQLite error “The database disk image is malformed”:

1. Use the sqlite3 app in the Mac OS X Terminal to create a .SQL export file

So I fired up the Terminal and changed to the directory where I had saved the bad database file, and entered the command:

sqlite3 DB.sqlite

This launches the sqlite> prompt, at which I entered the following commands:

.mode insert
.output dump_all.sql
.dump
.exit

At this point, you have the .SQL file in the same directory as the bad database file.

2. Remove transaction statements from the file

I received some errors in the next step, so I would recommend that you manually edit the .SQL file and remove any kind of transaction statements. In my example, there was a BEGIN TRANSACTION statement on the 2nd line of the file and a ROLLBACK statement on the last line. I removed both of these lines and re-saved the file.

3. Use the SQLite Manager extension for Firefox to create a new database file and import the .SQL file

The last step is to launch your Firefox and open the SQLite Manager extension, create a brand new database, select Import from the Database menu, click Select File and find the .SQL file, make sure the BEGIN TRANSACTION/COMMIT check box is clear, and click OK.

At this point, I had a new SQLite file that did not give the malformed error message any more. As with any database file corruption issues, I probably got a bit lucky that the file was not too badly damaged, or damaged beyond repair.

Here was the blog post by Sergei Dorogin that I found that got me part way to the solution in my instance:

SQLiteException “database disk image is malformed”

BTW, Happy Birthday to Roy Face, the former great Pittsburgh Pirates pitcher. He was one of the coaches at this past year’s Pirates Fantasy Camp, and seemed like a very nice person.

As a follow up to Stir Trek, thanks to my friend and former co-worker John B., who reminded me that if you have a lot of inserts you want to do, execute this command on your database before you get started:

BEGIN TRANSACTION;

Then, after all your inserts have been sent , execute this:

END TRANSACTION;

If you do this, I promise that your users will be much more happier. (In my iOS app instance, the insert times went from about 90 seconds to 0.7 seconds.)

BTW, happy birthday to Amy Heckerling, who directed two of my all time most favorite movies, National Lampoon’s European Vacation and Johnny Dangerously.