Cross-Platform SQLite Integrity Check Discrepancies on Mac vs. Windows

Diagnosing Integrity Check Variations Between SQLite Implementations on Windows and macOS

Issue Overview: Integrity Check Inconsistencies Across Operating Systems

The core issue involves SQLite databases that pass integrity checks on macOS but fail the same checks when validated on Windows. The databases in question are derived from the same backup files, restored using identical procedures, and compressed with ZIP compression. Both platforms use software tools that wrap SQLite’s command-line interface (CLI) to perform operations such as integrity checks, index rebuilding, phantom record removal, and file compaction. Despite procedural parity, the macOS implementation of the software fails to detect errors that the Windows version identifies. This discrepancy suggests platform-specific differences in how SQLite interacts with the database files, executes validation routines, or handles low-level file operations.

Critical observations include:

• The macOS and Windows software tools invoke SQLite CLI commands programmatically, with confirmed identical command-line arguments.
• The development team reproduced the issue using Delphi-compiled binaries for both platforms.
• The databases are functionally cross-platform (i.e., they operate correctly on both systems), but integrity checks yield divergent results.

Underlying challenges stem from SQLite’s reliance on platform-specific behaviors, such as file locking mechanisms, byte-order handling, filesystem semantics (e.g., case sensitivity), and compilation flags that influence error detection logic. For example, macOS’s APFS and Windows’ NTFS handle file writes, journaling, and metadata differently, which may expose or mask database inconsistencies. Additionally, variations in SQLite library versions, compiler toolchains (Delphi for Windows vs. macOS), or linking methods (static vs. dynamic libraries) could alter how integrity checks are performed.

Possible Causes of Divergent Integrity Check Results

1. SQLite Version Mismatches
SQLite’s integrity check algorithm evolves across versions. If the macOS and Windows builds of the software link against different SQLite versions, the validation logic may differ. For instance, older SQLite versions might lack checks for specific corruption patterns (e.g., freelist inconsistencies, b-tree imbalances) that newer versions detect. The .version CLI command or SELECT sqlite_version() query can reveal discrepancies.

2. Compilation Flags and Platform-Specific Optimizations
SQLite’s behavior is heavily influenced by compile-time options. Platform-specific builds may enable or disable features like:

• SQLITE_DEBUG: Adds assertion checks and debugging utilities.
• SQLITE_DEFAULT_FILE_FORMAT: Sets the default database schema format.
• SQLITE_ENABLE_ICU: International Components for Unicode support, altering text comparisons.
• Page size configurations (SQLITE_DEFAULT_PAGE_SIZE), which affect how data is stored and validated.
  A macOS build compiled with -DSQLITE_OMIT_INTEGRITY_CHECK (hypothetically) would skip integrity checks entirely, though this flag is uncommon.

3. Filesystem Semantics and Journaling Modes
APFS (macOS) and NTFS (Windows) handle file locks, atomic commits, and journaling differently. If a database is opened in WAL (Write-Ahead Logging) mode on one platform and DELETE (rollback journal) mode on another, the recovery mechanisms and lock files may interact unpredictably with the host OS. For example, macOS’s case-preserving but case-insensitive filesystem could misinterpret attached database filenames, leading to incomplete checks.

4. Byte Order and Data Alignment
While SQLite databases are platform-independent, the host OS’s native byte order (little-endian vs. big-endian) and memory alignment requirements could influence low-level read/write operations. Corruption caused by misaligned writes on one platform might not be detected if the other platform’s SQLite build uses different memory alignment assumptions.

5. Backup and Restoration Artifacts
ZIP compression tools on macOS and Windows may apply different compression algorithms or preserve file metadata inconsistently. A macOS-restored database might retain resource forks or extended attributes that interfere with SQLite’s file parsing, while Windows strips these during extraction.

6. CLI Invocation Differences
The software’s database tools might invoke the SQLite CLI with environment variables or shell wrappers that alter the execution context. For example, macOS’s default sqlite3 binary could be an outdated version from /usr/bin, while the Windows version uses a newer build bundled with the application.

Troubleshooting Steps, Solutions, and Fixes

Step 1: Verify SQLite Version Consistency

• Action: Execute SELECT sqlite_version() in both macOS and Windows versions of the software. Compare the output.
• Resolution: If versions differ, standardize by embedding the same SQLite amalgamation source in both builds. Use the amalgamation distribution from SQLite.org to ensure identical codebases.
• Example Code:

  // Delphi code snippet to execute and log SQLite version
  Query := 'SELECT sqlite_version() AS version';
  DatabaseConnection.Execute(Query, ResultSet);
  Log('SQLite Version: ' + ResultSet.FieldByName('version').AsString);
  

Step 2: Validate Database Byte Identity

• Action: Compute SHA-256 hashes of the database files post-restoration on both platforms.
• Command:

  # macOS
  shasum -a 256 /path/to/database.db
  # Windows (PowerShell)
  Get-FileHash -Algorithm SHA256 C:\path\to\database.db
  

• Resolution: If hashes differ, investigate the restoration process. Use a hex editor (e.g., HxD for Windows, Hex Fiend for macOS) to compare low-level file structures. Check for ZIP extraction settings (e.g., ASCII vs. binary mode).

Step 3: Test with Official SQLite CLI

• Action: Download precompiled CLI tools from SQLite.org for both platforms. Run integrity checks independently of the software.
• Command:

  sqlite3 database.db "PRAGMA integrity_check;"
  

• Resolution: If discrepancies persist, the issue lies within SQLite itself or the database file. If resolved, the problem stems from the software’s SQLite integration (e.g., compilation flags, linked libraries).

Step 4: Audit Compilation Flags and Build Configuration

• Action: Compare SQLite compilation flags used for macOS and Windows builds.
• Tool: Extract build configuration from binaries using sqlite3_compileoption_get().

  SELECT sqlite_compileoption_used('SQLITE_DEBUG');
  

• Resolution: Align flags across platforms. Rebuild SQLite with -DSQLITE_ENABLE_DBSTAT_VTAB to enable advanced diagnostics.

Step 5: Filesystem and Journaling Analysis

• Action: Convert the database to WAL mode on both platforms and retest.

  PRAGMA journal_mode=WAL;
  

• Resolution: If inconsistencies resolve, investigate journaling implementation differences. Check for filesystem permissions, lock file handling, and -DSQLITE_USE_FCNTL_TRACE to log file control operations.

Step 6: Provide Sample Database for Debugging

• Action: Share a reproducible test case with the SQLite team via a non-disclosure agreement (NDA). Include:
  • Database file (sanitized if necessary).
  • Exact CLI commands and outputs from both platforms.
  • Build configurations and OS versions.
• Resolution: Leverage SQLite’s sqlite3_test_control() interface to enable internal diagnostics during integrity checks.

Final Fixes:

1. Embed a uniform SQLite version across all platforms using static linking.
2. Standardize compilation flags, disabling platform-specific optimizations.
3. Implement pre-integrity-check file validation (hash checks) to ensure byte-for-byte parity.
4. Use PRAGMA quick_check followed by PRAGMA integrity_check for comprehensive validation.

By methodically isolating variables—SQLite versions, build settings, filesystems, and restoration pipelines—developers can pinpoint whether the discrepancy arises from environmental factors or SQLite’s internals. Cross-platform database tools must rigorously control these variables to ensure consistent behavior.