Jump to content
Setting up an AI agent? ReverseEngineering.wiki skill (works from the command line, no configuration): https://reverseengineering.wiki/skill/rewiki/SKILL.md · setup for every client, including MCP: https://reverseengineering.wiki/llms.txt · REWiki:Connect

Format:SQLite database file/Rollback journal and WAL

From ReverseEngineering.wiki

The side files of an SQLite database that hold transactions in flight: the rollback journal (<db>-journal, magic D9 D5 05 F9 20 A1 63 D7, sector-aligned headers and page records with a sparse checksum) and the write-ahead log (<db>-wal, magic 0x377F0682/0x377F0683, 32-byte header and 24-byte frame headers with salted Fibonacci checksums), plus the -shm wal-index.

Overview

The two side files of an Format:SQLite database file that hold transactions in flight. A database uses one or the other (header bytes 0x12/0x13: 1 = rollback journal, 2 = WAL). Both are big-endian. For forensics and data recovery they matter in opposite ways: a rollback journal holds the old content of pages being changed, a WAL holds the new content of committed transactions that may not be in the database file yet.

Rollback journal

<db>-journal (journal_mode DELETE, TRUNCATE, PERSIST; MEMORY and OFF write none). In the default DELETE mode the file exists only while a write transaction is open, or after a crash (a "hot" journal that the next opener rolls back). It is a sequence of segments, each starting on a sector boundary with a header (Fact:SQLite database file/struct/Rollback journal header):

Offset Size Field
0 8 magic D9 D5 05 F9 20 A1 63 D7
8 4 record count nRec (0 until the journal is synced; 0xFFFFFFFF = "up to the end of the file")
12 4 random nonce for the record checksums
16 4 database size in pages before the transaction
20 4 sector size (the header occupies one sector, usually 512)
24 4 page size

Records follow the header sector: u32 page number, the original page content, u32 checksum. The checksum is the nonce plus every 200th byte of the page, from offset page_size - 200 down to the smallest positive offset (bytes at page_size-200, page_size-400, ..., 96 for 4096-byte pages), as unsigned bytes (Fact:SQLite database file/behavior/Rollback journal record checksum). Each time the journal is synced in the middle of a transaction (for example when the page cache spills) a new header with a fresh nonce starts at the next sector boundary, so one journal can hold many segments. A journal of a multi-database transaction ends with a super-journal (formerly "master journal") record: u32 PAGER_SJ_PGNO (the lock-byte page number, never a real page), the super-journal file name in UTF-8, u32 its length, u32 its checksum (the sum of the name bytes as signed chars) and the 8-byte magic again.

A committed journal is deleted (DELETE), truncated to 0 bytes (TRUNCATE) or has its header zeroed (PERSIST): no magic means nothing to roll back.

Write-ahead log

<db>-wal (journal_mode WAL, 3.7.0 and later). A 32-byte header (Fact:SQLite database file/struct/WAL header) and then frames of a 24-byte frame header (Fact:SQLite database file/struct/WAL frame header) plus one page:

Offset Header field Frame header field
0 magic 0x377F0682 or 0x377F0683 page number
4 format version 3007000 for a commit frame the database size in pages after the commit, else 0
8 page size salt-1 (copy)
12 checkpoint sequence number salt-2 (copy)
16 salt-1 (incremented at each checkpoint) checksum-1
20 salt-2 (random at each checkpoint) checksum-2
24 checksum-1 of bytes 0-23
28 checksum-2 of bytes 0-23

The magic's low bit gives the byte order of the checksum words: 0x377F0683 big-endian, 0x377F0682 little-endian; the writer uses its native order (WAL_MAGIC | SQLITE_BIGENDIAN), so files written on little-endian machines (x86, most ARM) start 37 7F 06 82 (Fact:SQLite database file/magic/WAL magic). The checksum is a Fibonacci-weighted sum over pairs of 32-bit words, s1 += x[i] + s2; s2 += x[i+1] + s1, chained from the header through the first 8 bytes of every frame header and its page data (Fact:SQLite database file/behavior/WAL checksum). A frame is valid while its salts match the header and its checksum matches the running value; only frames up to the last valid commit frame belong to the database. For a page, the last valid copy in the log wins over the one in the database file.

The WAL is reused after a checkpoint, so stale frames from earlier generations (old salts) can remain after the valid ones and hold older versions of pages.

<db>-shm is the wal-index: a hash table from page number to frame, rebuilt from the WAL when missing. Its first 136 bytes are two copies of a 48-byte WalIndexHdr (version 3007000, change counter, page size, mxFrame, database size, salts and checksums in native byte order) and the checkpoint info with the lock bytes at offset 120. It can be deleted when no connection is open.

Pitfalls

  • To copy a WAL-mode database consistently, copy the -wal file together with the database, or checkpoint first; the -shm file is not needed.
  • A 65536-byte page size is stored as 65536 in the WAL header (a 32-bit field) but as 1 in the database header (a 16-bit field).
  • Opening a copied database with SQLite checkpoints and deletes the copied WAL, or rolls back a hot journal: keep pristine copies.

Evidence & references

  • https://github.com/sqlite/sqlite at commit 43a48f77585a88940c727f33682151418f544059: src/wal.c lines 34-81 (WAL header, frames, validity, checksum), 270-277 (WAL_MAX_VERSION 3007000), 307-334 (WalIndexHdr), 474-491 (sizes and magic), 888-935 (walChecksumBytes), 4178-4180 (magic in native order; the page size stored as a full 32-bit value); src/pager.c lines 757-759 (journal magic), 771 (header size = sector size), 1476-1530 (journal header and nRec), 1734-1747 (super-journal record), 2270-2297 (pager_cksum).
  • Tested on files written by SQLite 3.53.1 (CPython 3.11, Windows x64): a WAL with 7 frames (magic 37 7F 06 82, header and all frame checksums verified with Code:SQLite database file/sqlite3info (Python)) and a live journal copied mid-transaction with cache_size=1, which held 24 one-record segments, each with its own nonce, all checksums verified.