Repairing a Corrupt SQLite Database with .dump and COMMIT

When sqlite3 .recover is unavailable, the .dump-and-COMMIT trick rescues a corrupt database. A real fix for n8n's execution_data btree corruption.

SQLite databases rarely die loudly. They keep answering reads while writes start failing in ways that look like an application bug. That’s exactly what happened when n8n’s database.sqlite went corrupt on my VPS: n8n kept running, but the logs filled with Failed to hard-delete executions at roughly 57 errors per minute.

The symptom

The error pointed at execution_data — n8n’s table for workflow execution payloads. A btree page had a double-reference, so any delete touching that page threw an integrity error and aborted. The app retried in a tight loop, hence the spam.

Why the obvious fix didn’t work

The standard first move is sqlite3 db .recover, which rebuilds the file from salvageable pages. But .recover depends on the sqlite_dbpage virtual table, and the system’s sqlite (3.45) ships without it. No .recover available.

The fix: .dump, then fix the trailer, then reload

  1. Dump everything to SQL: sqlite3 database.sqlite .dump > dump.sql. This reads the database and emits SQL that recreates every table and row. When a page is corrupt, the dump still writes what it can and appends a marker at the end.
  2. Find the trailer. A corrupt dump ends with ROLLBACK; -- due to errors instead of a clean COMMIT;. That trailer is why a naive reload fails — it rolls the whole import back.
  3. Replace it. Swap the trailing ROLLBACK; -- due to errors for COMMIT;.
  4. Load into a fresh file: sqlite3 fresh.db < dump.sql, then set PRAGMA journal_mode=WAL.

The cost

The double-referenced page was unrecoverable, so I lost one old execution row. Everything else came back. For a workflow scheduler that’s a rounding error.

The lesson

Before you reach for a repair tool that isn’t there, check the dump’s trailer. A ROLLBACK; -- due to errors line is the tell that the dump is 99% fine and only needs its ending fixed. And if a table is written in a hot loop (like execution logging), corruption there looks like an app bug, not a database one — check the DB integrity before you blame the code.

Related

Related

Guides & explainers

Filed under: Ops & Automation, Automation, n8n, SQLite