Database Corruption on macOS Sonoma with ExFAT Drives During Bulk Inserts

macOS Sonoma and ExFAT Drive Corruption During Large Transactional Inserts

The issue at hand involves database corruption occurring specifically on macOS Sonoma when performing bulk inserts of large records into an SQLite database stored on an ExFAT-formatted external drive. The corruption manifests as inconsistencies in the database’s B-tree structure, leading to errors such as incorrect overflow list lengths and btreeInitPage() failures. This issue is highly reproducible under specific conditions: large transactions involving thousands of records, each containing sizable binary data (e.g., 100 KB), and only when the database resides on an ExFAT or FAT32 drive. Notably, the corruption does not occur when using Write-Ahead Logging (WAL) journal mode, when inserting records in smaller transactions, or when disabling synchronous writes (pragma synchronous=off).

The problem appears to be tied to the interaction between SQLite’s transactional behavior and macOS Sonoma’s handling of ExFAT file systems. Specifically, the corruption seems to arise during cache spills, where SQLite writes data to disk and calls fsync to ensure durability. The macOS ExFAT driver may exhibit a race condition or bug during these operations, leading to inconsistent writes and subsequent database corruption. This hypothesis is supported by the observation that increasing the cache size to prevent spills, or reducing the frequency of fsync calls, mitigates the issue.

macOS ExFAT Driver Bugs and SQLite Cache Spill Behavior

The root cause of the corruption appears to be a combination of macOS Sonoma’s flawed ExFAT driver implementation and SQLite’s handling of cache spills during large transactions. When SQLite performs bulk inserts, it accumulates data in its page cache before writing it to disk. If the cache fills up, SQLite spills the excess data to disk and calls fsync to ensure the writes are durable. On macOS Sonoma with ExFAT drives, this process seems to trigger a race condition or bug in the file system driver, resulting in incomplete or inconsistent writes.

Several factors contribute to this issue:

1. ExFAT File System Limitations: ExFAT is not a journaled file system, meaning it lacks mechanisms to recover from partial or inconsistent writes. This makes it more susceptible to corruption when the underlying driver or hardware fails to ensure atomicity.
2. macOS Sonoma’s ExFAT Driver: The macOS release notes for Sonoma hint at changes and potential issues with FAT file system support. These changes may have introduced bugs that manifest during high-frequency fsync operations or large data transfers.
3. SQLite’s Cache Spill Mechanism: SQLite’s default behavior of spilling cache contents to disk and calling fsync during large transactions exacerbates the issue. The frequent fsync calls may overwhelm the ExFAT driver, leading to race conditions or incomplete writes.

The corruption is avoided in scenarios where cache spills are minimized or eliminated, such as when using WAL mode (which reduces the frequency of fsync calls), increasing the cache size, or inserting records in smaller transactions. These workarounds suggest that the issue lies in the interaction between SQLite’s cache management and macOS’s ExFAT driver during high-intensity write operations.

Mitigating Corruption: Workarounds and Best Practices

To address the corruption issue, several strategies can be employed, each targeting different aspects of the problem:

1. Use Write-Ahead Logging (WAL) Mode: WAL mode significantly reduces the frequency of fsync calls by decoupling writes to the database file from writes to the journal. This minimizes the likelihood of triggering the ExFAT driver bug. To enable WAL mode, execute the following command:

   PRAGMA journal_mode=WAL;
   

2. Increase SQLite Cache Size: By increasing the page cache size, you can reduce the frequency of cache spills and subsequent fsync calls. This can be achieved using the cache_size pragma:

   PRAGMA cache_size = -10000; -- Set cache size to 10,000 pages
   

   Adjust the value based on your system’s memory capacity and the size of your transactions.

3. Reduce Transaction Size: Breaking large transactions into smaller chunks can prevent cache spills and reduce the load on the ExFAT driver. For example, instead of inserting 10,000 records in a single transaction, insert 100 records per transaction:

   BEGIN;
   INSERT INTO albumart (hash, albumart) VALUES (random(), randomblob(100 * 1000));
   -- Repeat for 100 records
   COMMIT;
   

4. Disable Synchronous Writes: While not recommended for production environments due to the risk of data loss, disabling synchronous writes (pragma synchronous=off) can prevent the corruption by bypassing fsync calls entirely. Use this setting with caution:

   PRAGMA synchronous=OFF;
   

5. Avoid Redundant Inserts: If your application frequently inserts the same data, consider adding checks to avoid redundant inserts. For example, query the database for existing records before inserting new ones:

   SELECT id FROM albumart WHERE hash = ?;
   -- If no results, proceed with insert
   

6. Monitor and Validate Database Integrity: Regularly check the database for corruption using the integrity_check pragma. This can help detect issues early and prevent further damage:

   PRAGMA integrity_check;
   

7. Consider Alternative File Systems: If possible, format the external drive using a more robust file system such as APFS or HFS+. These file systems are better suited for high-intensity write operations and are less prone to corruption.

8. File System-Level Workarounds: If switching file systems is not an option, consider using macOS’s native file system features to mitigate the issue. For example, enabling fullfsync (though it did not help in this case) or using disk arbitration to manage external drives more effectively.

By implementing these strategies, you can significantly reduce the risk of database corruption on macOS Sonoma with ExFAT drives. However, it is important to note that these are workarounds rather than permanent fixes. The ultimate resolution will likely require Apple to address the underlying issues in the ExFAT driver. Until then, careful configuration and monitoring are essential to maintaining database integrity.

In summary, the corruption issue arises from a complex interplay between SQLite’s transactional behavior and macOS Sonoma’s ExFAT driver. By understanding the root causes and applying targeted workarounds, you can mitigate the risk and ensure the stability of your database operations. However, vigilance and regular integrity checks remain crucial, especially when working with external drives and large datasets.