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.
Thanks!, got me started to.
While you are using the sqlite shell application, i would suggest continuing with this, instead of using a firefox plugin.
Step 3:
sqlite3 newdatabase.db < dump_all.sql
Thanks! It worked! I repired malformed database this way
Thanks! It really helped. Gizmocuz’s part is also handy.
[…] 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 […]
Unfortunately I discovered that large SQL export files do not import back into a new database file on the Mac very well. If your SQL file is very large, I would recommend switching over to a Windows 7 machine and doing the SQLite import, it worked for me when I tried it. You can find my blog post on this topic by searching for “Cocoa error 19”, or by clicking on the link in the reply right above this one.
I am new to Sql and got this issue when I am doing a commit to my database.
I was not able to follow completely on the above article.
To resolve this issue do i need to install sqllite3 on my machine
can you please elaborate the step 1 ..I have windows 7.. what does it mean by “Mac OS X Terminal to create a .SQL export file”
Thanks In Advance
You can download the Windows version of SQLite from this page:
http://sqlite.org/download.html
Once you get the SQLite application on your computer, the easiest way to use it is to put the database file and SQLite application in the same directory. Then, you should be able to use the same commands shown above.
Thanks for sharing this helpful tip. I have found another helpful article for the same, see here: http://sqliteviewer.com/blog/how-to-resolve-sqlite-error-code-11.html
Seven years later, thanks! I was able to use these steps to repair my Billings database. My backups were out of date and the program wouldn’t launch. I would have lost months of billing transactions.
Also late to the party — bookmarked nonetheless for any future problems, thanks!
In case it helps anyone, for me running
VACUUM;
REINDEX;
within the sqlite3 client solved the problem (which had to do with “pages not being used”, according to running “PRAGMA integrity_check”).
This tip is still useful!
The knocking off of the transaction in the dump.sql file saved my life (well data!)