Home » Databases » ERROR: SQLite database is malformed – SOLVED

ERROR: SQLite database is malformed – SOLVED

Every once in a while if SQLiteyou’re making heavy use of a SQLite database, as with any other DBMS, it will become corrupted. Usually this is due to bugs within the DBMS itself and sometimes hardware failure.

I had a SQLite database go corrupt when an application crashed. Normally I would have a backup to go to but in this case I didn’t because it was a new application that I was in the process of setting up.

Attempting to list tables in the database using sqlite3 resulted in the following message:

Error: near line 1: database disk image is malformed

In SQLite, the consistency checker is pragma integrity_check

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. Pragma integrity_check will return at most N errors before the analysis quits, with N defaulting to 100. If pragma integrity_check finds no errors, a single row with the value ‘ok’ is returned.

PRAGMA integrity_check does not find FOREIGN KEY errors. Use the PRAGMA foreign_key_check command for to find errors in FOREIGN KEY constraints.

sqlite> pragma integrity_check;
Error: database disk image is malformed

Yeah, it’s effectively a boolean response. OK if all is good else “database disk image is malformed”. Not very helpful IMO.

So what to do? Obviously, reverting to an earlier backup is preferred but if you remember, I didn’t have one and I didn’t want to redo all that work.

We need to export the schema and data and create a new database, import the schema and data. Simple:

sqlite> .mode insert
sqlite> .output mydb_export.sql
sqlite> .dump
sqlite> .exit

The .output sets the name of the file that the output of following commands to mydb_export.sql.
The .dump will export the database in SQL format.

Now, move the mydb.db file to mydb.db.original.

mv mydb.db mydb.db.original

We are almost done. Let’s create the database and load the exported schema & data:

sqlite3 mydb.db < mydb_export.sql

We have one last step. If the database had indexes, and all databases should have indexes, you will want to update the statistics for the indexes:

sqlite> analyze;
sqlite> .exit
Share Button

Comments

  1. Peter Paker says:

    Awesome.

  2. Peter Paker says:

    Awesome work.

  3. WynandB says:

    This is great, thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect