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:
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 188.8.131.52 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 184.108.40.206 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.