Fixing 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 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:

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.

8 Comments

  1. gizmocuz says:

    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

  2. Włodek says:

    Thanks! It worked! I repired malformed database this way

  3. Pluszon says:

    Thanks! It really helped. Gizmocuz’s part is also handy.

  4. […] 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 […]

  5. BP says:

    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.

  6. Teja says:

    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

  7. BP says:

    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.

  8. Johnson says:

    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

Leave a Reply