Fix the SQLite error “The database disk image is malformed”
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.