skip to content

A phone app's crash reports show SQLite returning 'database disk image is malformed' for some users; what corrupts a SQLite file, and how do you confirm and recover it?

level: seniorimportance: should knowfreq 45%

answer

  1. crashes alone rarely do it
  2. the file plus its journals
  3. locks the library cannot see
  4. two copies of the library
  5. quick_check, integrity_check, .recover

basics

~20 s

SQLite files corrupt when something outside SQLite breaks its rules: a file copied or restored without its -wal or journal, broken locks, two library copies in one process, or disabled syncs. Confirm with PRAGMA integrity_check; salvage with the CLI's .recover.

solid answer

~50 s

`SQLITE_CORRUPT`, `database disk image is malformed`, rarely comes from a crash: SQLite rolls back an interrupted transaction automatically on the next open. It comes from breaking the rules around the file: copying or restoring it while a transaction is active, separating it from a hot `-journal` or from its `-wal` file, which is part of the database's persistent state, file locks that do not work, a thread that `open()`s and `close()`s the file directly and so cancels the process's POSIX advisory locks, two copies of SQLite linked into one app, a connection carried across `fork()`, or `synchronous=OFF` or `journal_mode=OFF` meeting a crash. To confirm, copy the database with its `-wal` and `-journal` aside, then run `PRAGMA quick_check` (O(N)) or `PRAGMA integrity_check` (O(N log N), adding UNIQUE and index checks); both return `ok` when clean. Salvage with the CLI's `.recover` into a new file, then remove the cause.

code

console · 10 lines
console
$ sqlite3 chat.db "PRAGMA integrity_check(3);"
*** in database main ***
Tree 2 page 31 cell 22: invalid page number 1476657282
Tree 2 page 31 cell 21: Rowid -1 out of order
Tree 2 page 31 cell 21: invalid page number 1459880066
$ sqlite3 chat.db .recover > recovered.sql
$ sqlite3 fixed.db < recovered.sql
          defensive off
$ sqlite3 fixed.db "PRAGMA integrity_check;"
ok

go deeper

for a junior

Recall the error text 'database disk image is malformed' and that PRAGMA integrity_check returns ok on a healthy file.

for a middle

Explain why a crash alone rarely corrupts SQLite, why the -wal and -journal files belong with the database, and how quick_check differs from integrity_check.

for a senior

Diagnose from fleet evidence: look for copy or restore code, direct file reads, a second linked SQLite, fork, or disabled syncs; preserve all files, salvage with .recover, and remove the cause.

for a principal

Treat corruption as a fleet risk: ban unsafe pragmas and direct file access in review, decide whether startup quick_check is worth its cost, and define what the app does when a user's file is damaged.

## Why SQLite is hard to corrupt, and what that implies SQLite is designed so that an application crash, an operating-system crash or a power failure in the middle of a transaction leaves a consistent file: the partial transaction is rolled back automatically the next time the database is opened, using the **hot journal** (the `-journal` file in rollback mode) or the **write-ahead log** (the `-wal` file in WAL mode). So when a fleet reports `SQLITE_CORRUPT` (result code 11, `database disk image is malformed`), the useful assumption is that something broke one of SQLite's preconditions. A file whose header is not SQLite's at all gives a different code, `SQLITE_NOTADB` (26, `file is not a database`). ## The causes, grouped | Group | Concrete examples | Why it corrupts | |---|---|---| | File copied or replaced underneath SQLite | a backup or restore while a transaction is active; copying the `.db` without its `-wal` or hot `-journal`; deleting or renaming a leftover journal | the database's true state is the file **plus** its journal or WAL; separate them and committed work is lost or a half-applied state survives | | Locks that do not work | a network filesystem with broken locking; a thread that reads the file with plain `open()` and `close()`; two processes using different locking schemes | concurrent writers that cannot see each other overwrite each other's pages | | Two copies of the library | the app links its own SQLite while another component in the same process uses a second copy | each copy keeps its own list of open files to work around POSIX lock quirks; a `close()` through one silently drops locks the other relies on | | Process tricks | opening a connection and then `fork()`ing; unlinking or renaming an open database; opening one file through two hard links | journals are found by file name and locks by process, so recovery uses the wrong journal or none | | Disabled protections | `PRAGMA synchronous=OFF` with a power loss or OS crash; `journal_mode=OFF` or `MEMORY` with an app crash mid-write | without sync barriers or a durable journal, a crash can leave pages from different moments | | Hardware | flash or disks that acknowledge a sync before the data is durable; bit flips | SQLite's ordering assumptions do not hold | The POSIX lock case surprises people most: `close()` on any descriptor for a file cancels **every** POSIX advisory lock that process holds on it, across all threads. SQLite's Unix code works around this for its own connections, which is why many threads using SQLite are safe; a crash reporter, file-type sniffer or backup routine that reads the database directly is not. Since 3.51.0 SQLite adds defences for WAL databases used by several processes, but the documentation still says to never `close()` a database file while connections are open. ## Phone-specific ways in 1. A restore or migration step that copies only the main file while the app is running in WAL mode. The `-wal` can hold committed transactions not yet checkpointed into the main file. 2. Cleanup code that deletes "stray" `-wal` or `-journal` files to save space. 3. A diagnostics or upload feature that reads the database file's bytes directly while connections are open. 4. An app that bundles its own SQLite build while a library it uses talks to the platform's copy on the same file. 5. Speed tweaks such as `synchronous=OFF` or `journal_mode=OFF` that someone copied from a benchmark. The default `synchronous` setting is FULL, which the documentation recommends for maximum robustness. ## Confirming corruption - **`PRAGMA integrity_check`** checks out-of-order entries, misformatted records, missing pages, missing or surplus index entries, UNIQUE, CHECK and NOT NULL violations, the freelist, and pages used twice or never. It returns the single row `ok` when clean, otherwise up to 100 error rows by default; `integrity_check(N)` changes that cap, and `integrity_check(table)` checks one table and its indexes. - **`PRAGMA quick_check`** does the same except UNIQUE constraints and whether index content matches the table, so it runs in O(N) rather than O(N log N). It suits a startup or background check. - **Neither checks foreign keys.** Use `PRAGMA foreign_key_check` for orphaned rows, which are a logic problem, not file corruption. Before running anything, copy the database together with any `-wal` and `-journal` files. Opening the file can trigger recovery, and you want the original evidence. ## Recovering 1. Work on the copy, never on the user's only file. 2. Run the `sqlite3` command-line tool's `.recover`. Unlike `.dump`, which stops at the first sign of damage, it rebuilds SQL from every readable page, and rows it cannot attribute to a table land in a `lost_and_found` table. 3. Load that script into a fresh file, run `PRAGMA integrity_check` on it, and compare row counts with what you expected. 4. If a good backup exists, it is usually better than a salvage; how to take one safely belongs with the lesson on SQLite's files and backups. 5. Fix the cause. A rebuilt file corrupts again if the copy routine or second library is still there.

  • Why is a SQLite database safe from an app crash with PRAGMA synchronous=OFF but not from a power loss?
    `synchronous=OFF` skips the fsync calls that act as write-ordering barriers. After an app crash, writes already handed to the operating system still reach storage in full, so the file stays consistent. After a power loss or OS crash, later writes may be durable while earlier ones are not, which can corrupt the file. The documentation recommends the default, FULL, for maximum robustness.
  • PRAGMA integrity_check returns ok, yet some SQLite message rows reference chats that no longer exist. Is the file corrupt?
    No. `integrity_check` does not check FOREIGN KEY constraints, and SQLite enforces them only on connections that set `PRAGMA foreign_keys=ON`, which is off by default. Orphans usually mean some connection deleted chats without enforcement. `PRAGMA foreign_key_check` lists the violating rows; turn `foreign_keys` on for every connection.

  • A power cut mid-transaction normally corrupts a SQLite file.
  • Copying only the .db file is a complete backup in WAL mode.
  • Deleting a leftover -journal or -wal file is a safe cleanup step.
  • Reading the database file with plain open() and close() is harmless.
  • PRAGMA integrity_check also verifies foreign key constraints.
  • Two copies of SQLite in one app cannot interfere with each other.