database disk image is malformed

What it means

An SQLite file is a series of fixed-size pages, organised as trees: one per table and one per index. When SQLite reads a page that breaks the format (a bad page type, counts that don’t add up, pointers to nowhere), it stops and returns SQLITE_CORRUPT (code 11), “database disk image is malformed”.

Only queries that touch a damaged page fail. In the test below, SELECT count(*) read an intact index and returned the right number, while SELECT sum(plays) read the table and failed. So the error can come and go depending on the query, and a database can look fine for a long time.

Common causes

SQLite’s own list of how files get corrupted, most common first:

1. A copy taken while the database was being written. A backup tool, cp, rsync or a sync service copied the file mid-transaction, so the copy mixes old and new pages.
2. The database separated from its journal. Committed changes can live in the -wal file, and an interrupted write needs the -journal file to roll back. Deleting either, or copying the .db file without them, can leave it corrupt.
3. A network file system (NFS, SMB) whose locking doesn’t work, with more than one process writing.
4. The same file opened under two names (a hard or symbolic link): each name gets its own journal, so processes miss each other’s changes.
5. Power loss or a crash on storage that lies about syncing, which consumer drives and USB sticks often do.
6. Something else writing into the file, such as a program bug writing through a reused file handle.

How to fix it

Stop, then copy everything

Stop every program using the database. Copy the file together with its -wal and -journal files, if they exist, and work on the copy:

mkdir ~/rescue && cp app.db app.db-wal app.db-shm app.db-journal ~/rescue/ 2>/dev/null

If you have a recent good backup, restoring it is often quicker and safer than repair.

See what’s damaged

PRAGMA integrity_check;

It prints ok for a healthy database, or up to 100 problems. PRAGMA quick_check; is faster because it doesn’t verify UNIQUE constraints or that index content matches table content. Each problem names a tree (by its root page) or an index:

*** in database main ***
Tree 2 page 60: btreeInitPage() returns error code 11
wrong # of entries in index tracks_artist

To see which table or index a tree number is, look up its root page:

SELECT type, name FROM sqlite_schema WHERE rootpage = 2;

If only an index is damaged, rebuild it

Indexes hold copies of table data, so you can rebuild them from the table:

REINDEX tracks_artist;
PRAGMA integrity_check;

This worked in our test when the index’s pages could still be read. When the index page itself was unreadable, REINDEX and DROP INDEX both failed with the same error; use .recover then.

Salvage the data with .recover

The sqlite3 shell’s .recover reads every page it can and writes SQL that rebuilds the database:

sqlite3 broken.db .recover > recovered.sql
sqlite3 recovered.db < recovered.sql
sqlite3 recovered.db "PRAGMA integrity_check;"

Rows it finds but can’t place in a table go into a lost_and_found table. Recovery is a salvage: rows on destroyed pages are gone, and deleted rows can come back (--ignore-freelist reduces that). Check row counts against what you expect before you replace the original.

Plain .dump is a worse choice on a corrupt file: when it hits an error it ends its output with ROLLBACK; -- due to errors, so loading it creates nothing unless you edit that line.

Prevent it

Back up a database that’s in use through SQLite, not with a file copy:

sqlite3 app.db ".backup backup.db"
sqlite3 app.db "VACUUM INTO 'backup.db';"

Keep -wal and -journal files with their database, never delete them by hand, and don’t share a database over a network drive between machines that write to it.

Reproduce it

On a scratch copy only. macOS /usr/bin/sqlite3, SQLite 3.51.0: a tracks table with 20,000 rows and an index on artist (244 pages of 4,096 bytes), then 512 random bytes written over the start of page 60, one of the table’s pages:

cp music.db broken.db
head -c 512 /dev/urandom | dd of=broken.db bs=1 seek=$((59*4096)) conv=notrunc
sqlite3 broken.db "SELECT count(*) FROM tracks;"
sqlite3 broken.db "SELECT sum(plays) FROM tracks;"

20000
Error: stepping, database disk image is malformed (11)

The shell adds “Error: stepping,” and the result code; SQLite’s message is database disk image is malformed. PRAGMA integrity_check reported the problems shown above. .recover then rebuilt a database that passed integrity_check with 19,869 of the 20,000 rows: the 131 rows on page 60 were lost. .dump on the same file wrote the same 19,869 rows but ended with ROLLBACK; -- due to errors.

In Inlet

Inlet opens SQLite files directly and shows SQLite’s message when a query reaches a damaged page. Close the database in Inlet, as in every other program, before you copy or recover it; then open the recovered file and compare its row counts with what you expect.