Residual WAL and SHM Files Persist After sqlite3_analyzer Execution on WAL-Mode Databases

Understanding WAL Mode Behavior and sqlite3_analyzer’s Residual File Retention

WAL Mode Fundamentals and File Management Expectations

SQLite’s Write-Ahead Logging (WAL) mode introduces two auxiliary files: the *-wal (Write-Ahead Log) and *-shm (Shared Memory) files. These files are critical for WAL’s concurrency model. The -wal file contains uncommitted changes and committed changes not yet merged into the main database. The -shm file coordinates access to the WAL file across processes.

Under normal operation, when a database connection closes cleanly in WAL mode, SQLite performs a checkpoint to integrate WAL content into the main database file. If no other connections exist, the -wal and -shm files are truncated or deleted. Residual files typically indicate either an active connection, an unclean shutdown, or a failure to execute a checkpoint.

sqlite3_analyzer is a diagnostic tool that generates reports on database structure and storage efficiency. It operates by opening the database, querying internal B-tree structures, and populating a temporary database with statistics. When run against a WAL-mode database, proper cleanup of -wal/-shm files is expected after the tool exits.

The observed issue—residual -wal/-shm files after sqlite3_analyzer execution—contravenes this expectation. Residual files may cause confusion, occupy unnecessary disk space, or interfere with backup processes. The anomaly occurs despite the absence of concurrent connections, suggesting a flaw in how sqlite3_analyzer manages its database connection lifecycle.

Connection Lifecycle Mismanagement and Checkpoint Omission

The root cause lies in how sqlite3_analyzer opens and closes the database. SQLite’s WAL mode requires explicit or implicit checkpointing to reintegrate WAL content into the main database. When an application opens a WAL-mode database, it creates or attaches to the -shm file. Upon closure, if the connection is the last one accessing the database, SQLite attempts a checkpoint and deletes the auxiliary files.

sqlite3_analyzer’s failure to clean up these files implies one of three scenarios:

1. Unreleased Database Locks: The tool retains a lock on the database, preventing SQLite from determining that no other connections exist. This inhibits checkpointing and file cleanup.
2. Omission of Final Checkpoint: The tool closes the database without triggering a checkpoint, leaving unmerged data in the WAL file. SQLite preserves the WAL file for future connections to process.
3. Incomplete SHM File Detachment: The -shm file is memory-mapped. If the tool does not properly release this mapping, the OS may retain the file handle, preventing deletion.

Version mismatches exacerbate the issue. sqlite3_analyzer linked against an older SQLite version (e.g., 3.44.2) may lack fixes for WAL handling present in newer versions (e.g., 3.45.3). However, the problem persists even with version 3.46.1, indicating a deeper flaw in the tool’s logic.

Resolving Residual Files via Connection Hygiene and Version Alignment

Step 1: Validate SQLite and sqlite3_analyzer Version Compatibility

Confirm the installed versions using:

sqlite3 --version
sqlite3_analyzer --version

Ensure both tools use the same major.minor SQLite version. Anaconda environments often bundle outdated tools. If mismatched, update sqlite3_analyzer using a source build or a package manager that provides version alignment.

Step 2: Explicit Checkpoint Execution Before Closure

Modify sqlite3_analyzer’s source code to invoke PRAGMA wal_checkpoint(FULL); before closing the database. This forces a checkpoint, merging WAL content into the main database and allowing cleanup.

For precompiled binaries, execute a checkpoint manually after analysis:

sqlite3 test.db "PRAGMA wal_checkpoint(FULL);"

This ensures residual files are cleaned up post-analysis.

Step 3: Ensure Clean Connection Closure

SQLite connections should be closed using sqlite3_close_v2(), which finalizes all statements and releases resources. If sqlite3_analyzer uses deprecated sqlite3_close(), pending statements or virtual table locks may prevent full cleanup. Rebuilding the tool with SQLITE_ENABLE_API_ARMOR helps detect such issues.

Step 4: Address Shared Memory Mapping Leaks

On Unix-like systems, inspect open file handles using lsof | grep test.db-shm. If sqlite3_analyzer leaks SHM file handles, restarting the process or unmounting the filesystem may be necessary. Rebuilding SQLite with SQLITE_MAX_MMAP_SIZE=0 disables memory mapping, avoiding SHM file retention at the cost of performance.

Step 5: Apply the Trunk Fix for WAL Cleanup Logic

The forum discussion references a fix in SQLite’s trunk. This patch adjusts connection closure logic to ensure SHM file detachment. To apply it:

1. Clone SQLite’s Fossil repository:

   fossil clone https://www.sqlite.org/src sqlite.fossil
   fossil open sqlite.fossil
   

2. Update to the patched version:

   fossil update 94ceac98845e3124
   

3. Rebuild sqlite3_analyzer:

   ./configure && make sqlite3_analyzer
   

Replace the Anaconda-provided binary with the rebuilt version.

Step 6: Use WAL Mode Alternatives for Analysis

If residual files are unacceptable, temporarily switch to DELETE journal mode before analysis:

sqlite3 test.db "PRAGMA journal_mode=DELETE;"
sqlite3_analyzer test.db

This avoids WAL file creation entirely. Revert to WAL mode afterward if needed.

Step 7: Monitor File Cleanup with Scripted Workflows

In automation scenarios, wrap sqlite3_analyzer execution in a script that:

• Checks for residual files
• Executes checkpoints
• Logs anomalies
  Example:

#!/bin/bash
DB="test.db"
sqlite3_analyzer "$DB"
if [ -f "${DB}-wal" ] || [ -f "${DB}-shm" ]; then
  sqlite3 "$DB" "PRAGMA wal_checkpoint(FULL);"
  rm -f "${DB}-wal" "${DB}-shm"
fi

By addressing connection lifecycle management, enforcing checkpoints, and aligning tool versions, users can eliminate residual WAL/SHM files after sqlite3_analyzer execution. For persistent issues, rebuilding the tool with the trunk fix provides a definitive resolution.