Integrity Checking & Corruption Recovery #

A SQLite database on an unattended edge node, a desktop machine that loses power mid-write, or a Python job writing over a flaky mount does not announce corruption when it first appears. The b-tree keeps serving rows from the pages it can still reach, and the damage stays silent until a query walks into a broken interior page and the engine returns SQLITE_CORRUPT with the message database disk image is malformed. By then the fault is often days old and has been faithfully copied into every backup you took since. Detecting corruption early — and having a rehearsed recovery route when detection fires — is the core of the Backup, Recovery & Data Integrity discipline: a backup you never verify is a liability, not insurance. This guide covers what PRAGMA integrity_check and PRAGMA quick_check actually validate, where the malformed-image error originates, and how to drive a deterministic quarantine-and-rebuild from Python when a check fails.

Figure — The verification ladder: quick_check screens structure fast, integrity_check adds index cross-verification, and foreign_key_check adds referential validation. A single ok row clears the database; any other rows are corruption reports that route the file into recovery.

Core Mechanism & Crash-Safety Defaults #

PRAGMA integrity_check walks the entire database b-tree and cross-verifies every index against the table rows it indexes. It confirms that pages link correctly, that cells fit within their pages, that the freelist is coherent, that row counts match index entries, and that NOT NULL and UNIQUE constraints hold at the storage level. Because it reads every page and re-derives every index key, its cost scales with database size — on a multi-gigabyte file it can run for minutes and saturate read I/O. By default it reports at most the first 100 problems and then stops; passing an integer argument, PRAGMA integrity_check(1), caps it at a single error for a cheap pass/fail signal.

PRAGMA quick_check runs the same structural verification but deliberately skips the expensive step: it does not cross-check index content against the underlying tables. It catches malformed pages, broken b-tree links, and cell overflow, and it runs several times faster because it never re-derives index keys. That speed is exactly why it is dangerous to over-trust — a corrupted index whose structure is intact but whose content has drifted out of sync with its table will pass quick_check and fail integrity_check. The two are not interchangeable; they occupy different points on a coverage-versus-cost curve.

Both PRAGMAs return a result set of text rows. Success is a single row containing the literal string ok. Anything else is a list of human-readable error descriptions — one row per problem — and the presence of any row other than that lone ok is the corruption signal your automation must key on. Neither PRAGMA raises SQLITE_CORRUPT by itself; they report corruption as data. The SQLITE_CORRUPT error code (and its message database disk image is malformed) is raised later, by ordinary statements, when the engine dereferences a broken page during normal operation. A related code, SQLITE_NOTADB, means the first 16 bytes of the file are not the SQLite header magic at all — the file is empty, truncated to nothing, encrypted, or simply not a database. Distinguishing those two is the first branch of any recovery.

PRAGMA foreign_key_check is a third, orthogonal tool. It does not detect page-level corruption; it reports rows whose foreign-key references have no matching parent — logical inconsistency that corruption, a botched migration, or writes made while foreign_keys was off can all produce. Run it after a structural check clears, not instead of one.

None of these verifications mutate the database, so they are safe to run against a production file — but they take a read lock and, for the full check, hold it long enough to matter. The crash-safety defaults you set elsewhere still govern the file: keep PRAGMA journal_mode=WAL and PRAGMA synchronous=NORMAL in force so that the checkpointed image the checker reads is itself consistent, and so an abrupt power loss during a check leaves recovery to the WAL rather than to a torn main file.

Step-by-Step Implementation #

1. Verify Prerequisites & Run a Scheduled quick_check #

Before scheduling any verification, confirm the connection is in the expected mode and that a consistent image exists to check. On a WAL database, uncheckpointed frames live in the -wal file; a check reads the merged view the engine presents, but you want a recent checkpoint so the on-disk main file is close to current. Establish the baseline and run the fast screen:

PRAGMA journal_mode;        -- expect 'wal'; a read-only mount can silently pin 'delete'
PRAGMA synchronous;         -- expect 1 (NORMAL); OFF here means prior writes were never durable
PRAGMA wal_checkpoint(PASSIVE);  -- fold recent frames into the main file before checking
PRAGMA quick_check;         -- fast structural screen; a single 'ok' row means pages link cleanly

Treat any journal_mode other than wal as a configuration fault to resolve first — the mode transition is covered in the architecture section on file system permissions and ownership, where a wrong owner or a read-only mount is the usual cause of a mode that will not stick. Run quick_check on a frequent cadence (it is cheap) as an early-warning tripwire, and reserve the full check for a slower cadence or for when the fast screen reports anything but ok.

2. Choose Full vs Quick Check Cadence #

quick_check is a screen, not a guarantee. Decide how often to pay for the full integrity_check based on how much you can afford to miss index-content corruption and how large the file is. The decision table below maps the trade-off; the short rule is: quick_check often, integrity_check on a schedule that fits your maintenance window, and always run the full check against a copy when the live file is too large to lock.

The reason quick_check alone is insufficient is concrete: it can return ok on a database whose index pages are structurally valid but whose entries no longer match the rows they point at, so lookups silently return wrong results while the tripwire stays green. Never treat a green quick_check as certification that the data is correct — only that the pages are shaped correctly.

3. Automate integrity_check with Quarantine and Rebuild #

Wire the full check into a routine that opens read-only, interprets the result rows, and — on failure — moves the suspect file aside and rebuilds from the last known-good source rather than continuing to serve corrupt data. Every PRAGMA is read back or its result inspected, and every database call is guarded:

import os
import shutil
import sqlite3
import logging

logger = logging.getLogger("integrity")


def check_database(db_path: str, full: bool = True) -> list[str]:
    """Return [] if healthy, else the list of corruption report rows."""
    conn = None
    try:
        # read-only URI: the check never needs to write, and this refuses to
        # create a fresh empty file if db_path is missing.
        conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True, timeout=30.0)
        pragma = "integrity_check" if full else "quick_check"
        rows = [r[0] for r in conn.execute(f"PRAGMA {pragma};").fetchall()]
        # success is exactly one row containing the literal 'ok'.
        if rows == ["ok"]:
            logger.info("%s clean: %s", pragma, db_path)
            return []
        logger.error("%s found %d problem(s) in %s", pragma, len(rows), db_path)
        return rows
    except sqlite3.DatabaseError as e:
        # SQLITE_NOTADB / SQLITE_CORRUPT surface here if the header itself is broken.
        logger.critical("cannot even open %s for checking: %s", db_path, e)
        return [f"unopenable: {e}"]
    finally:
        if conn:
            conn.close()


def quarantine_and_rebuild(db_path: str, known_good: str) -> None:
    problems = check_database(db_path, full=True)
    if not problems:
        return
    # move the malformed image aside for forensic salvage; do not delete it.
    quarantine = db_path + ".corrupt"
    shutil.move(db_path, quarantine)
    for suffix in ("-wal", "-shm"):                 # the sidecars are suspect too
        if os.path.exists(db_path + suffix):
            os.remove(db_path + suffix)
    shutil.copy(known_good, db_path)                # restore last verified-good copy
    logger.warning("quarantined %s -> %s, restored from %s",
                   db_path, quarantine, known_good)

    # verify the restored file before declaring recovery complete.
    if check_database(db_path, full=True):
        raise RuntimeError(f"restored copy {known_good} is ALSO malformed")
    logger.info("recovery verified clean: %s", db_path)

The restored source must itself be verified — restoring from a backup that was taken after the corruption began simply reinstalls the fault, which is why the final check_database call is not optional. For the mechanics of producing a genuinely consistent source to restore from, see the Online Backup API & Hot Copies guide; a filesystem cp of a live database is exactly the kind of torn image that this routine will later flag. The probe-and-alert side of this loop — emitting a metric instead of blocking a rebuild — is developed in Automating integrity_check in Python, and the salvage path when you have no clean backup is in Recovering a Malformed Database Image.

Workload Profiles & Threshold Reference #

Verification cadence is a function of storage reliability, database size, and how much a stalled writer costs. The bands below are starting points — measure the full check’s wall-clock time against your maintenance window and adjust.

Failure Documentation & Edge Cases #

False ok from quick_check Missing Index Corruption #

Trigger: An index page’s structure is intact but its entries no longer agree with the table rows — a classic result of a partial write or a bit flip in a leaf. quick_check skips index-content cross-verification, so it returns ok while lookups through that index return wrong or missing rows.

Diagnosis: Escalate to the full check, which is the only one that re-derives index keys:

PRAGMA integrity_check;   -- reports 'row N missing from index ...' that quick_check cannot see

Fallback: Treat quick_check strictly as a tripwire and schedule integrity_check on the cadence from the decision table. When the full check reports index mismatches on an otherwise-sound file, REINDEX rebuilds the affected indexes without a full database rebuild.

Corruption from mmap SIGBUS, fsync Lies, or NFS Access #

Trigger: A SIGBUS from a shrinking memory-mapped file, a storage layer that acknowledges fsync() without persisting (cheap SD cards, some virtualized disks), or a database accessed concurrently across NFS where SQLite’s POSIX locking is unreliable. Each can leave the main file internally inconsistent while every write appeared to succeed.

Diagnosis: Correlate the check failure with the environment — confirm the file lives on local storage, not a network mount, and review the Memory-Mapped I/O Configuration guide for the mmap sizing that avoids SIGBUS on shrinking files.

Fallback: Move the database to a local filesystem with honest fsync semantics, cap or disable mmap_size on 32-bit targets, and never share a single file across NFS clients. Corruption from these causes is not repaired by re-running the check — it requires salvage and a rebuild.

Unrecoverable Pages #

Trigger: The damage reaches the database header or a critical interior b-tree page, and the file cannot be opened at all — statements fail immediately with SQLITE_CORRUPT or SQLITE_NOTADB.

Diagnosis: The read-only open in the routine above returns an unopenable: marker instead of a report list, which distinguishes “opens but has bad pages” from “cannot be parsed as a database.”

Fallback: Abandon in-place repair and salvage what the engine can still read into a fresh file, then rebuild. That best-effort route is the subject of Recovering a Malformed Database Image; if no clean backup exists, it is your last option before data loss.

Production Hardening Checklist #

• Run PRAGMA quick_check as a frequent tripwire and treat any non-ok row as an incident.
• Schedule PRAGMA integrity_check on a cadence that fits your maintenance window; run it on a backup copy for large files.
• Open the database read-only (mode=ro) for verification so a missing file is not silently recreated.
• Key automation on the exact ["ok"] result, not on the check merely completing without raising.
• Distinguish SQLITE_CORRUPT (bad pages) from SQLITE_NOTADB (not a database) before choosing a recovery route.
• Quarantine a malformed file (move aside, keep it) rather than deleting it, and remove its -wal/-shm sidecars.
• Verify every restored or rebuilt file with a full integrity_check before returning it to service.
• Keep databases on local storage with honest fsync; never share one file across NFS.

Related Pages #

• Automating integrity_check in Python — wiring the check into a non-blocking health probe that emits a metric and alerts.
• Recovering a Malformed Database Image — salvaging data from a file that already returns the malformed-image error.
• Online Backup API & Hot Copies — producing the consistent, verified source you restore from.
• File System Permissions & Ownership — the mount and ownership faults behind a mode that will not stick.
• Memory-Mapped I/O Configuration — sizing mmap to avoid the SIGBUS that corrupts a shrinking file.

For the authoritative semantics of these PRAGMAs, see the official SQLite PRAGMA documentation. Python developers should review the sqlite3 module documentation for connection and error-handling behaviour.