VACUUM Resets WAL2 Journal Mode to DELETE Upon Connection Closure

Issue Overview: VACUUM Operation Disrupts WAL2 Journal Mode Persistence
The core issue involves the SQLite VACUUM command causing unintended reversion of the journal_mode from WAL2 to DELETE after all database connections close. This behavior is observed exclusively in databases configured to use the experimental WAL2 journaling mode (associated with the begin-concurrent-pnu-wal2 branch of SQLite). When a VACUUM is executed, subsequent connections to the database report WAL2 as the active journal mode while the initial connection remains open. However, once all connections terminate, the journal mode reverts to DELETE, even though the PRAGMA journal_mode; command initially reports WAL2 post-VACUUM. This discrepancy indicates a mismatch between the runtime journal mode configuration and the persistent state stored in the database file or its associated metadata.

The problem does not manifest with the standard WAL journal mode, which retains its configuration across VACUUM operations and connection cycles. The anomaly is critical because VACUUM is often used for database optimization, and inadvertent use in WAL2-configured databases can degrade performance or disrupt transaction guarantees by forcing a fallback to DELETE mode. This issue has been confirmed across multiple SQLite versions (3.35.3 to 3.40.0) in the experimental begin-concurrent-pnu-wal2 branch, suggesting a systemic problem in how WAL2 metadata is preserved during database reconstruction.

The WAL2 journaling mode is designed to enhance concurrency by maintaining two separate write-ahead logs (WAL files), allowing readers and writers to operate with reduced contention. Unlike DELETE mode, which uses rollback journals and locks the database during writes, WAL2 and WAL modes enable concurrent read and write operations. The VACUUM command rebuilds the entire database file, which inherently requires exclusive access and temporarily bypasses the WAL mechanism. However, the failure to restore WAL2 configuration after VACUUM completion implies that the database header or supporting structures are not updated correctly during the rebuild process.

Possible Causes: Metadata Inconsistencies and Branch-Specific Implementation Flaws

1. Database Header Reset During VACUUM:
   The VACUUM command operates by creating a temporary database, copying data from the original, then replacing the original with the temporary. During this process, the new database may inherit default settings unless explicitly reconfigured. The WAL2 mode, being experimental and branch-specific, might not have its metadata (e.g., the journal_mode flag in the database header) preserved during the rebuild. Standard WAL mode likely has explicit handling in the SQLite core to retain its configuration, whereas WAL2—due to its developmental status—may lack equivalent safeguards.

2. Connection-Local Journal Mode Configuration:
   SQLite’s PRAGMA journal_mode operates at the connection level. When a connection sets journal_mode=WAL2, the change applies only to that connection unless the database is in a state that allows persistent configuration (e.g., no active transactions, exclusive access). The VACUUM command may temporarily revert to DELETE mode during execution (as it requires exclusive access), but a flaw in the begin-concurrent-pnu-wal2 branch could prevent reversion to WAL2 post-VACUUM.

3. WAL2 File Management and Cleanup:
   The WAL2 mode relies on auxiliary files (-wal2 and -shm2) to manage concurrent transactions. During VACUUM, these files are typically deleted or reinitialized. If the VACUUM process fails to recreate these files or update their metadata, subsequent connections may default to DELETE mode due to missing WAL2 infrastructure. This would align with the observed behavior where the journal mode resets only after all connections close—when SQLite performs final checks for WAL file validity.

4. Branch-Specific Code Paths:
   The begin-concurrent-pnu-wal2 branch introduces modifications to support WAL2 and concurrent transactions. If the VACUUM implementation in this branch does not account for WAL2-specific logic—such as updating the database header’s journal mode flag or regenerating WAL2 files—the post-VACUUM database will lack critical markers needed to retain WAL2 configuration.

Troubleshooting Steps, Solutions & Fixes: Diagnosing and Resolving WAL2 Persistence Failures

1. Verify Journal Mode Persistence Mechanisms:

   • Inspect the Database Header: Use a hex editor or the PRAGMA database_list; command to examine the database header’s journal mode flag. Compare the header values before and after VACUUM to determine if WAL2 is being written correctly.
   • Check Auxiliary File Creation: After VACUUM, confirm that the -wal2 and -shm2 files are regenerated. Their absence post-VACUUM indicates a failure in WAL2 initialization during database rebuild.

2. Modify VACUUM Execution Workflow:

   • Explicitly Reapply Journal Mode: Execute PRAGMA journal_mode=WAL2; immediately after VACUUM within the same connection. This forces the new database to adopt WAL2 configuration, bypassing any automatic resets.
   • Disable Automatic Journal Mode Detection: Use PRAGMA journal_mode=WAL2; in conjunction with PRAGMA locking_mode=EXCLUSIVE; before VACUUM to ensure the database remains in a state that favors WAL2 persistence.

3. Branch-Specific Code Adjustments:

   • Patch the VACUUM Command: In the begin-concurrent-pnu-wal2 branch, modify the VACUUM implementation to explicitly set the journal mode to WAL2 in the new database’s header. This may involve editing the sqlite3RunVacuum function to preserve non-standard journal modes.
   • Enhance WAL2 File Recreation Logic: Ensure that VACUUM regenerates -wal2 and -shm2 files with appropriate metadata, mimicking the behavior of standard WAL mode.

4. Workarounds for Production Environments:

   • Avoid VACUUM in WAL2 Databases: Use PRAGMA incremental_vacuum; or manual ANALYZE/REINDEX operations to optimize the database without full reconstruction.
   • Scripted Reconfiguration: Automate post-VACUUM steps to reset journal_mode=WAL2 and restart connections, ensuring configuration persistence.

5. Long-Term Solutions:

   • Mainstream WAL2 Integration: Advocate for merging WAL2 enhancements into the SQLite trunk, ensuring parity with WAL in handling VACUUM and other maintenance operations.
   • Enhanced Testing for Experimental Branches: Implement regression tests targeting VACUUM behavior under non-standard journal modes to catch similar issues during development.

By systematically addressing metadata handling, branch-specific code paths, and operational workflows, users can mitigate the WAL2 journal mode reset issue and maintain desired concurrency configurations post-VACUUM.