CRITICAL: Restore fails with 'nonsequential page numbers' after checkpoint during Litestream downtime

[corylanou]

Bug Summary

Severity: CRITICAL - Complete Data Loss
Version: v0.5.0 (pre-release, main branch)
Impact: Database restoration fails completely after a specific but common failure scenario

Error Message

decode database: decode page 17743: copy page 17750 header: 
nonsequential page numbers in snapshot transaction: 17742,17750

Reproduction Steps

Scenario

1. Litestream is replicating a database with continuous writes
2. Litestream process is killed (crash, OOM, server restart, etc.)
3. Database continues receiving writes while Litestream is down
4. A non-PASSIVE checkpoint occurs (FULL, RESTART, or TRUNCATE)
5. Litestream is restarted
6. User attempts to restore from replica
7. Result: Restore fails with nonsequential page error

Automated Reproduction Script

Save this as reproduce-critical-bug.sh:

#!/bin/bash
set -e

echo "Reproducing Critical Bug: Nonsequential Page Numbers"
echo "======================================================"

# Configuration
DB="/tmp/critical-bug-test.db"
REPLICA="/tmp/critical-bug-replica"
LITESTREAM="litestream"  # or ./bin/litestream if using local build
LITESTREAM_TEST="litestream-test"  # from PR #748

# Clean up
rm -f "$DB"* && rm -rf "$REPLICA"

# Step 1: Create test database (50MB)
echo "[1] Creating test database..."
$LITESTREAM_TEST populate -db "$DB" -target-size 50MB -table-count 2

# Step 2: Start Litestream
echo "[2] Starting Litestream..."
$LITESTREAM replicate "$DB" "file://$REPLICA" > /tmp/litestream.log 2>&1 &
LITESTREAM_PID=$!
sleep 3

# Step 3: Start continuous writes
echo "[3] Starting continuous writes..."
$LITESTREAM_TEST load -db "$DB" -write-rate 100 -duration 2m -pattern constant &
WRITE_PID=$!

# Step 4: Let it run normally
echo "[4] Running normally for 20 seconds..."
sleep 20

# Step 5: Kill Litestream (simulate crash)
echo "[5] Killing Litestream..."
kill -9 $LITESTREAM_PID

# Step 6: Continue writes without Litestream
echo "[6] Continuing writes (Litestream is down)..."
sleep 15

# Step 7: Execute checkpoint while Litestream is down
echo "[7] Executing FULL checkpoint..."
sqlite3 "$DB" "PRAGMA wal_checkpoint(FULL);"

# Step 8: Resume Litestream
echo "[8] Resuming Litestream..."
$LITESTREAM replicate "$DB" "file://$REPLICA" >> /tmp/litestream.log 2>&1 &
NEW_LITESTREAM_PID=$!
sleep 20

# Stop everything
kill $WRITE_PID $NEW_LITESTREAM_PID 2>/dev/null || true

# Step 9: Attempt restore (THIS FAILS)
echo "[9] Attempting restore..."
rm -f /tmp/restored.db
if $LITESTREAM restore -o /tmp/restored.db "file://$REPLICA"; then
    echo "SUCCESS: Restore completed"
else
    echo "CRITICAL BUG: Restore failed!"
    echo "Database cannot be restored after checkpoint during downtime"
fi

Root Cause Analysis

When Litestream resumes after being killed and detects a checkpoint occurred:

1. The verify() function in db.go:650-738 detects WAL changes
2. It triggers a full snapshot with info.snapshotting = true
3. writeLTXFromDB() at db.go:946-991 creates the snapshot
4. The function iterates pages 1 to commit sequentially
5. Problem: Some pages don't exist (weren't modified), creating gaps
6. The ltx decoder expects sequential pages and fails on gaps

Impact

• Data Loss: Complete inability to restore the database
• Common Scenario: Any production outage + checkpoint = unrecoverable
• User Trust: Critical failure in disaster recovery tool

Affected Code

• db.go:950-989 - writeLTXFromDB() function
• replica.go:472 - dec.DecodeDatabaseTo(f) where error occurs
• github.com/superfly/ltx - Decoder expects sequential pages

Potential Solutions

1. Skip non-existent pages in writeLTXFromDB()
2. Modify ltx decoder to handle gaps during restore
3. Full database scan on resume after checkpoint detection
4. Track checkpoint state to handle WAL changes properly

Workaround

Users must avoid non-PASSIVE checkpoints when Litestream is not running. This is difficult to guarantee in production.

Test Requirements

• Test with litestream-test binary from PR feat: Add litestream-test harness for comprehensive database testing #748
• Or manually create database with continuous writes
• Reproducible 100% of the time with the provided script

Related Files

• Full analysis: critical-bug-analysis.md
• Test results: .local/docs/test-results-v0.5.0.md
• Gist: Available upon request

[corylanou]

Important Testing Note:

The reproduction scripts must use the locally built version of Litestream from the current main branch, NOT any system-installed version.

Before running tests:

# Build local litestream (required)
go build -o bin/litestream ./cmd/litestream

# Build test harness (required for automated scripts)
go build -o bin/litestream-test ./cmd/litestream-test

# Verify setup
./verify-test-setup.sh

The test scripts explicitly use ./bin/litestream to ensure we're testing the current code, not an older system version.

[corylanou]

⚠️ CRITICAL UPDATE: Bug is now WORSE with ltx v0.5.0

The recent update to ltx v0.5.0 (commit b3f5b12) has made this bug significantly worse.

Previous Behavior (ltx v0.4.0):

• Error: nonsequential page numbers in snapshot transaction
• Impact: Restore failed but replica files were created

Current Behavior (ltx v0.5.0):

• Error: cannot verify wal state: pos: ltx verification failed: decode page 0: no flags allowed, reserved for future use
• Impact: No replica files created at all - 100% data loss guaranteed

Root Cause:

Breaking change in ltx v0.5.0 that rejects the HeaderFlagNoChecksum flag that Litestream uses.

Affected Code:

• db.go lines 883, 1208, 1298: Flags: ltx.HeaderFlagNoChecksum

Recommendation:

DO NOT RELEASE v0.5.0 until either:

1. Revert ltx to v0.4.0
2. Remove HeaderFlagNoChecksum usage
3. Fix ltx to accept the flag again

Full analysis: validation-results-after-ltx-v0.5.0.md

[corylanou]

Detailed Reproduction Steps (Updated)

Test Environment

• Branch: feat/litestream-test-harness (commit 7a7b520)
• Merged with: main branch (includes ltx v0.5.0 update from commit b3f5b12)
• ltx version: v0.5.0 (see go.mod line 26)
• Build Date: 2025-09-16 16:23 CDT

Setup Instructions

# 1. Checkout and prepare
git checkout feat/litestream-test-harness
git pull origin main  # Get ltx v0.5.0 update

# 2. Build local binaries (REQUIRED - do not use system litestream)
go build -o bin/litestream ./cmd/litestream
go build -o bin/litestream-test ./cmd/litestream-test

# 3. Verify you're using local builds
./verify-test-setup.sh
# Should show: ./bin/litestream (NOT /opt/homebrew/bin/litestream)

Reproduction Script

Save as reproduce-checkpoint-bug.sh:

#!/bin/bash
set -e

# MUST use local build
LITESTREAM="./bin/litestream"
LITESTREAM_TEST="./bin/litestream-test"

# Clean up
rm -rf /tmp/checkpoint-test*

echo "[1] Creating test database..."
$LITESTREAM_TEST populate -db /tmp/checkpoint-test.db -target-size 50MB

echo "[2] Starting Litestream..."
$LITESTREAM replicate /tmp/checkpoint-test.db file:///tmp/checkpoint-replica > /tmp/checkpoint.log 2>&1 &
PID=$!
sleep 3

echo "[3] Starting writes..."
$LITESTREAM_TEST load -db /tmp/checkpoint-test.db -write-rate 100 -duration 2m &
WRITE_PID=$!
sleep 20

echo "[4] Killing Litestream (simulating crash)..."
kill -9 $PID

echo "[5] Continuing writes while Litestream is down..."
sleep 15

echo "[6] Executing checkpoint during downtime..."
sqlite3 /tmp/checkpoint-test.db "PRAGMA wal_checkpoint(FULL);"

echo "[7] Restarting Litestream..."
$LITESTREAM replicate /tmp/checkpoint-test.db file:///tmp/checkpoint-replica >> /tmp/checkpoint.log 2>&1 &
NEW_PID=$!
sleep 20

kill $WRITE_PID $NEW_PID 2>/dev/null || true

echo "[8] Checking for replica files..."
ls -la /tmp/checkpoint-replica/ 2>/dev/null || echo "NO REPLICA CREATED!"

echo "[9] Checking errors..."
grep "ERROR" /tmp/checkpoint.log | head -5

Expected vs Actual Results

Expected (pre-ltx v0.5.0):

• Replica files created (but corrupt)
• Error: nonsequential page numbers in snapshot transaction
• Restore fails

Actual (with ltx v0.5.0):

• NO replica files created at all
• Error: cannot verify wal state: pos: ltx verification failed: decode page 0: no flags allowed, reserved for future use
• Continuous sync errors
• 100% data loss

Root Cause

Breaking change in ltx v0.5.0 that rejects HeaderFlagNoChecksum used by Litestream at:

• db.go:883
• db.go:1208
• db.go:1298

How to Verify

# Run the reproduction script
chmod +x reproduce-checkpoint-bug.sh
./reproduce-checkpoint-bug.sh

# Check for the error
grep "no flags allowed" /tmp/checkpoint.log
# Result: Multiple instances of the error

# Check for replica files
ls /tmp/checkpoint-replica/
# Result: "No such file or directory" - NO REPLICA CREATED

[corylanou]

Update After Rebase from Main (2025-09-17)

Status: Bug behavior has CHANGED but data loss still occurs

New Error Signature

The error has changed from:

• Before: "nonsequential page numbers in snapshot transaction"
• After: "cannot calc restore plan: transaction not available"

Test Results

Running reproduce-critical-bug.sh with the latest main branch:

[STEP 10] Attempting to restore database...
2025/09/17 08:34:22 ERROR failed to run error="cannot calc restore plan: transaction not available"

✓ SUCCESS: Restore completed successfully
  - Restored row count: 0
  - Integrity check: ok  
  - Data integrity: ✗ FAILED (lost 4587 rows)

Critical Analysis

1. Data Loss Still Occurs: Complete data loss (0 rows restored vs 4587 original)
2. Different Error: Now shows "transaction not available" instead of page sequencing error
3. Same Trigger: Still occurs after checkpoint during Litestream downtime
4. Same Impact: Database is unrecoverable

Relationship to Issue #754

This bug may be compounded by the ltx v0.5.0 flag compatibility issue (#754), as both errors involve ltx transaction handling problems.

The checkpoint during downtime bug remains a critical data loss scenario.