Failing Over to In-Memory Databases on I/O Errors

When the storage volume under a SQLite database goes bad mid-flight — an SD card that starts returning SQLITE_IOERR on every write, a partition that fills and answers SQLITE_FULL, a filesystem remounted read-only after an error so the engine reports SQLITE_READONLY — the naive outcome is that ingestion simply stops and the process either crashes or spins raising the same exception on every frame. For a data collector that cannot pause its source (a sensor bus, a message stream, a meter), stopping is itself data loss. A more resilient path is to degrade: switch the active connection to a :memory: database, keep accepting writes into RAM, tag everything captured during the outage, and drain it back to disk once the volume recovers — all without corrupting or abandoning the primary on disk. This page covers that specific failover, one deterministic path within the Fallback Routing Strategies topic of the SQLite Architecture & Production Hardening discipline. It is a stopgap to survive a transient outage, not a durability plan, and the gotchas at the end are where its limits live.

Diagnosis

The trigger is not a single error code but a class of them, and distinguishing “the disk is unusable right now” from “this one statement was malformed” is the whole diagnosis. Three primary codes justify a failover to memory: SQLITE_IOERR (code 10, a physical read/write fault from the VFS layer, usually with a more specific extended code such as SQLITE_IOERR_WRITE), SQLITE_FULL (code 13, no space left on the volume), and SQLITE_READONLY (code 8, the database or its directory can no longer be written). Crucially, you must not fail over on SQLITE_CORRUPT (code 11) or SQLITE_NOTADB (code 26) — those mean the bytes themselves are damaged, and copying a corrupt page image into memory just carries the damage forward; that case belongs to Integrity Checking & Corruption Recovery, not here.

Inspect the extended code, not just the primary one, so the classification is precise:

import sqlite3

IO_FAILOVER_CODES = {sqlite3.SQLITE_IOERR, sqlite3.SQLITE_FULL, sqlite3.SQLITE_READONLY}

def is_io_failover(exc: sqlite3.Error) -> bool:
    primary = getattr(exc, "sqlite_errorcode", None)
    if primary is None:
        return False
    # Extended codes are (primary | (sub << 8)); mask back to the primary class.
    return (primary & 0xFF) in IO_FAILOVER_CODES

try:
    conn.execute("INSERT INTO readings(sensor, value) VALUES (?, ?);", row)
except sqlite3.OperationalError as exc:
    if is_io_failover(exc):
        ...   # volume-level fault -> degrade to :memory:
    else:
        raise  # SQLITE_CORRUPT / programming errors must NOT trigger failover

Confirm the volume is genuinely the problem before degrading: a single probe write to a throwaway file in the database’s directory reproduces the fault out-of-band, so you are reacting to storage state rather than to one unlucky statement.

Figure — on an I/O-class fault the active connection switches to a :memory: buffer that keeps ingesting and tags each row; a periodic probe write detects the volume’s return, and the buffer drains back into the restored disk database.

In-memory failover and drain-back around a storage outage A left-to-right flow. The disk primary is the normal write target. On an I/O-class fault — SQLITE_IOERR, SQLITE_FULL, or SQLITE_READONLY — the active connection switches to an in-memory buffer that keeps accepting writes and tags each row as buffered. A periodic probe write tests whether the volume has returned; once it succeeds the buffered rows drain back and reconcile into the restored disk database. disk primary normal write path :memory: buffer keep ingesting tag rows buffered=1 disk restored probe write ok drain + reconcile resolve PK collisions IOERR/FULL/READONLY probe succeeds

Solution

The failover wrapper holds two connections and a flag for which one is active. On an I/O-class fault it flips the active connection to an in-memory database that carries the same schema, marks every row written while degraded with a buffered column, and — when a probe confirms the disk is back — copies the tagged rows into the primary and returns to normal.

import sqlite3
import logging

logger = logging.getLogger("io_failover")

SCHEMA = """
CREATE TABLE IF NOT EXISTS readings(
    id       INTEGER PRIMARY KEY,          -- assigned on the disk side; see gotchas re: collisions
    sensor   TEXT NOT NULL,
    value    REAL NOT NULL,
    captured REAL NOT NULL,                 -- monotonic capture time; the real dedup/ordering key
    buffered INTEGER NOT NULL DEFAULT 0     -- 1 => written while degraded, pending drain-back
);
"""

class FailoverStore:
    def __init__(self, db_path: str):
        self.db_path = db_path
        self.degraded = False
        self.disk = sqlite3.connect(db_path, isolation_level=None, timeout=5.0)
        self.disk.execute("PRAGMA journal_mode=WAL;")      # readers unblocked; baseline for the primary
        self.disk.execute("PRAGMA synchronous=NORMAL;")    # fsync at checkpoint, not every commit
        self.disk.executescript(SCHEMA)
        self.mem: sqlite3.Connection | None = None

    def _enter_degraded(self) -> None:
        # ":memory:" is a private database that lives only in this process's RAM.
        self.mem = sqlite3.connect(":memory:", isolation_level=None)
        self.mem.execute("PRAGMA journal_mode=MEMORY;")    # no disk journal exists to write anyway
        # Read the mode back: a build could refuse MEMORY and silently leave DELETE.
        mode = self.mem.execute("PRAGMA journal_mode;").fetchone()[0].lower()
        assert mode in ("memory", "off", "wal"), f"unexpected in-memory journal_mode {mode!r}"
        self.mem.executescript(SCHEMA)
        self.degraded = True
        logger.warning("degraded to in-memory buffer after storage fault")

    def write(self, sensor: str, value: float, captured: float) -> None:
        target = self.mem if self.degraded else self.disk
        flag = 1 if self.degraded else 0
        try:
            target.execute(
                "INSERT INTO readings(sensor, value, captured, buffered) VALUES (?, ?, ?, ?);",
                (sensor, value, captured, flag),
            )
        except sqlite3.Error as exc:
            if not self.degraded and is_io_failover(exc):
                self._enter_degraded()
                self.write(sensor, value, captured)        # replay this row into memory
            else:
                raise

    def try_recover(self) -> bool:
        """Probe the volume; on success, drain buffered rows back and resume disk writes."""
        if not self.degraded:
            return True
        try:
            self.disk.execute("PRAGMA user_version;")      # cheap read to see if the handle is alive
            probe = f"{self.db_path}.probe"
            with open(probe, "wb") as f:                    # out-of-band write proves the FS accepts writes
                f.write(b"ok")
        except (sqlite3.Error, OSError):
            return False                                    # still down; stay in memory
        rows = self.mem.execute(
            "SELECT sensor, value, captured FROM readings WHERE buffered = 1 ORDER BY captured;"
        ).fetchall()
        self.disk.execute("BEGIN IMMEDIATE;")
        for sensor, value, captured in rows:
            self.disk.execute(
                "INSERT INTO readings(sensor, value, captured, buffered) VALUES (?, ?, ?, 0);",
                (sensor, value, captured),                 # id omitted -> disk assigns a fresh rowid
            )
        self.disk.execute("COMMIT;")
        drained = self.disk.execute(
            "SELECT changes();"                            # rows the drain actually inserted
        ).fetchone()[0]
        logger.warning("drained %d buffered rows back to disk", drained)
        self.mem.close()
        self.mem = None
        self.degraded = False
        return True

The buffered rows omit their id so the disk side assigns fresh integer primary keys on drain-back rather than replaying the in-memory rowids, which sidesteps the collision trap detailed below. The captured timestamp — not the surrogate key — is what preserves order and enables deduplication across the seam.

Verification

First, prove the failover actually engages on an I/O-class code and that ingestion continues. Force the classifier and confirm the store degrades and still accepts writes:

store = FailoverStore("/var/lib/collector/data.db")
store._enter_degraded()                                    # simulate the trigger
store.write("bme280", 21.4, captured=1000.0)
pending = store.mem.execute("SELECT count(*) FROM readings WHERE buffered = 1;").fetchone()[0]
assert pending == 1, "degraded write did not land in the in-memory buffer"

Second, verify the round trip: drain back and confirm every buffered row reached disk exactly once, tagged clean.

import os
open(store.db_path + ".probe", "w").close(); os.remove(store.db_path + ".probe")  # ensure FS writable
assert store.try_recover() is True, "recovery probe failed on a healthy volume"
on_disk = store.disk.execute(
    "SELECT count(*) FROM readings WHERE buffered = 0 AND captured = 1000.0;"
).fetchone()[0]
assert on_disk == 1, "buffered row did not drain back exactly once"
assert store.degraded is False, "store should have resumed the disk path"

A clean run leaves zero rows with buffered = 1 anywhere and the store back on its disk connection — that transition, not merely surviving the outage, is the proof the failover is complete.

Failure Modes & Gotchas

A prolonged outage exhausts RAM and takes the whole process down. An in-memory database grows without any disk ceiling, so a volume that stays broken for hours will push the buffer until the kernel OOM-killer reaps the collector — losing everything, which is strictly worse than a bounded pause. Cap the buffer explicitly: track its size (SELECT count(*) or the in-memory page_count × page_size) and, past a threshold, shed the oldest low-priority rows or spill to a different volume rather than growing unbounded. On a constrained target that ceiling should be computed from the memory budget, the same way a page cache is sized in tuning cache_size for embedded Linux.

Everything in the buffer is lost if the process crashes before drain-back. A :memory: database has no persistence whatsoever — a power cut, a segfault, or a SIGKILL while degraded discards every buffered row instantly. This is the failover’s fundamental limit: it trades durability for availability during the window. If the buffered data is not disposable, spill to a real file on a secondary path via ATTACH DATABASE '/mnt/spare/spill.db' AS spill; and write the tagged rows there instead of to pure memory, accepting the extra I/O for the durability. Never present in-memory failover as a substitute for backups; pair it with Integrity Checking & Corruption Recovery for the case where the disk returns damaged.

Replaying in-memory primary keys collides with rows the disk already holds. If the buffer assigns its own INTEGER PRIMARY KEY values starting from 1 and you copy those ids verbatim into a disk table that already contains ids 1…N, the drain-back either raises a UNIQUE constraint violation or, with INSERT OR REPLACE, silently overwrites real rows. Two disciplines avoid it: omit the surrogate id on drain-back so the disk assigns fresh keys (as the solution does), and key deduplication on a natural column — here captured plus sensor — rather than on the rowid. A read-only consumer that reads across the seam, such as an embedded dashboard replica, must tolerate the id renumbering the drain performs. The semantics of the in-memory database itself are documented in SQLite’s in-memory databases reference.