Help! My SQLite database is malformed!

Recently I came across a rather worrying SQLite database error:

Error: database disk image is malformed

Hrm, that's odd. Upon double-checking, it looked like the database was functioning (mostly) fine. The above error popping up randomly was annoying though, so I resolved to do something about it. Firstly, I double-checked that said database was actually 'corrupt':

sudo sqlite3 path/to/database.sqlite 'PRAGMA integrity_check';

This outputted something like this:

*** in database main ***
Main freelist: 1 of 8 pages missing from overflow list starting at 36376
Page 23119: btreeInitPage() returns error code 11
On tree page 27327 cell 30: 2nd reference to page 27252

Uh oh. Time to do something about it then! Looking it up online, it turns out that the 'best' solution out there is to export to an .sql file and then reimport again into a fresh database. That's actually quite easy to do. Firstly, let's export the existing database to an .sql file. This is done via the following SQL commands (use sqlite3 path/to/database.db to bring up a shell)

.mode insert
.output /tmp/database_dump.sql
.dump
.exit

With the database exported, we can now re-import it into a fresh database. Bring up a new SQLite3 shell with sqlite3, and do the following:

.save /tmp/new_database.sqlite
.read /tmp/database_dump.sql
.exit

...that might take a while. Once done, swap our old corrupt database out for your shiny new one and you're done! I would recommend keeping the old one as a backup for a while just in case though (perhaps bzip2 path/to/old_database.sqlite?).

Also, if the database is on an embedded system, you may find that downloading it to your local computer for the repair process will make it considerably faster.

Found this useful? Still having issues? Comment below!

Sources

• Fixing the SQLite error “The database disk image is malformed”
• SQLiteException “database disk image is malformed”