SQLite .dump Command Does Not Exit Non-Zero on Database Corruption

Database Corruption Detection in SQLite .dump and Exit Code Handling

Issue Overview

The core problem revolves around SQLite’s sqlite3 command-line tool not returning a non-zero exit code when database corruption is encountered during a .dump operation. When a database is corrupted—such as due to file system errors, incomplete writes, or invalid page structures—SQLite’s .dump command will output corruption warnings like /****** CORRUPTION ERROR *******/ but may still exit with a status code of 0 (success). This behavior is counterintuitive because users expect automated scripts or monitoring tools to detect corruption by checking the exit code. If the exit code does not reflect the error, downstream processes might incorrectly assume the dump succeeded, leading to silent failures in data integrity checks, backups, or migrations.

The .dump command generates SQL scripts to recreate the database schema and data. Corruption detection during this process depends on SQLite’s internal consistency checks. When a corrupt page, invalid b-tree structure, or other integrity violations are encountered, SQLite writes corruption messages to stderr but does not inherently translate these warnings into a non-zero exit status. This disconnect arises because the sqlite3 shell treats .dump as a metadata extraction process, not as an operation that inherently validates the database. The lack of an automatic exit code adjustment forces users to manually parse output for corruption warnings, which complicates automation and reliability.

Potential Causes of Silent Exit Codes During Corrupted .dump Operations

1. SQLite Shell’s Exit Code Semantics:
The sqlite3 command-line tool exits with 0 if it completes without syntax errors in the input script or commands, regardless of runtime errors encountered during execution. For example, a SELECT query that fails due to a missing table will print an error message but still exit with 0 unless specific error-handling flags are enabled. The .dump command operates similarly: it generates SQL output even if corruption is detected, treating corruption warnings as non-fatal diagnostic messages rather than process-terminating errors.

2. Separation of Corruption Detection and Exit Code Logic:
SQLite’s corruption detection mechanisms (e.g., SQLITE_CORRUPT error codes) are primarily designed for runtime database operations, not for the command-line shell’s exit code management. When the .dump command iterates through tables and indexes, it uses sqlite3_declare_vtab and other internal APIs to read schema details. If corruption is detected during this process, the underlying SQLite library returns SQLITE_CORRUPT to the shell, which prints the corruption warning but does not propagate this error to the shell’s exit code logic.

3. Lack of Error Escalation in Batch Mode:
When running .dump in non-interactive mode (e.g., via echo ".dump" | sqlite3 $database), the shell processes commands sequentially without a persistent session. Errors encountered during .dump do not cascade to affect the exit code because the shell’s default behavior is to continue processing subsequent commands unless explicitly instructed to abort. Without flags like -bail or -fail, the shell completes the .dump command, outputs the corruption warning, and exits with 0.

4. Inadequate Integration Between .dump and Integrity Checks:
The .dump command does not implicitly run integrity checks like PRAGMA integrity_check or PRAGMA quick_check. These pragmas actively scan the database for inconsistencies and return errors if corruption is found. Since .dump focuses on extracting existing content—even if that content is corrupt—it does not invoke these checks unless explicitly included in the script.

Resolving Exit Code Issues for Corrupted Database Dumps

Step 1: Enable Error Escalation with the -bail Flag
Modify the command to include -bail, which instructs the sqlite3 shell to exit immediately after the first error, including corruption-related errors. This changes the exit code to 1 if corruption is encountered:

echo ".dump
.exit" | sqlite3 -bail "$database"

The -bail flag ensures that any error during .dump (including SQLITE_CORRUPT) terminates the process and sets a non-zero exit code. However, this approach has limitations:

• It aborts the dump at the first corruption error, which may leave partial output.
• Some environments may require capturing the full dump even with errors, necessitating alternative methods.

Step 2: Integrate Explicit Integrity Checks
Run PRAGMA integrity_check before .dump to detect corruption early. If integrity checks fail, exit manually with a non-zero code:

echo "
PRAGMA integrity_check;
.dump
.exit" | sqlite3 "$database" | grep -q 'ok'
if [ $? -ne 0 ]; then
  exit 1
fi

This script runs PRAGMA integrity_check, checks if the output contains “ok,” and exits with 1 if corruption is detected. However, PRAGMA integrity_check can be slow for large databases and may not catch all forms of corruption (e.g., free-list issues).

Step 3: Capture and Parse Corruption Warnings
Redirect stderr to a file and check for corruption messages. If any are found, exit with a non-zero code:

sqlite3 "$database" ".dump" 2> errors.log
if grep -q "CORRUPTION" errors.log; then
  exit 1
fi

This method ensures the full dump completes but requires post-processing of error logs. It is useful for workflows that need partial dumps despite corruption.

Step 4: Custom Error Handling via SQLite3 Callbacks
For programmatic control, use SQLite’s C API or a scripting language wrapper (e.g., Python) to execute .dump while monitoring for errors. In Python:

import sqlite3
import sys

def progress_handler():
    pass  # Placeholder for cancellation checks

conn = sqlite3.connect(":memory:")
conn.set_progress_handler(progress_handler, 100)
try:
    conn.execute("SELECT * FROM sqlite_master")
except sqlite3.DatabaseError as e:
    if 'corrupt' in str(e).lower():
        sys.exit(1)

This approach allows fine-grained error detection but requires custom code.

Step 5: Combine .dump with -cmd for Pre-Execution Checks
Use the -cmd flag to run integrity checks before .dump:

sqlite3 -cmd ".timeout 20000" -cmd "PRAGMA integrity_check" "$database" ".dump"

If PRAGMA integrity_check fails, the shell exits with 1. However, this still depends on the shell’s handling of pragma errors.

Step 6: Modify SQLite Shell Source Code (Advanced)
For mission-critical environments, modify the sqlite3 shell’s source code to treat corruption errors as fatal. In shell.c, locate the exit command logic and add conditions to check for SQLITE_CORRUPT or specific error messages. Recompile the shell for deployment.

Final Recommendation:
For most users, combining -bail with explicit integrity checks provides a balance between reliability and simplicity. Redirecting stderr to parse corruption warnings is ideal for capturing partial dumps while ensuring automation scripts detect failures. Advanced users with custom tooling should consider integrating low-level error callbacks or modifying the shell’s exit code logic.