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 address this issue:
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:
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:
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.