We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't usable in any way shape or form. It will inevitably lead to catalog corruption if you hard-reboot OSX, even without the database or application open.
We've tried FULLSYNC and F_FULLFSYNC, but it makes no difference. Repro steps: a) Run our application and write stuff to the database b) Close our app & wait for the .wal file to disappear AND the app to disappear from activity monitor c) Wait another 2 minutes d) Copy your SQLITE db file to a network share e) Hard reboot OSX f) After the reboot, copy the same SQLITE db to a second network share g) File compare (d) vs. (f) Observe: The files in (c) and (e) are virtually never identical. NOTE: There is no application or SQLITE is involved here. The app is closed. It (should have) checkpointed and flushed properly (FULLSYNC + FULLFSYNC). However, the MAC doesn't seem to write the database to disk. So once you hard-reboot you don't have the same file anymore. SQLITE detects this as catalog corruption around 25% of the time, but a file compare shows differences pretty much 100% of the time. Not just benign differences in unused pages - the header is more often than not different as well. Without the Hard reboot our database always survives a close. You can gracefully shutdown the application, pkill it, force terminate, crash it, soft reboot - it all survives. However, once you hard reboot OSX - even AFTER the app is closed - it has a very high probability of corrupting our database. I've seen a worse case scenario where the hard reboot followed an app graceful shutdown by 12 hours, and it still corrupted the database. This is so easy to reproduce I'm not sure why this isn't reported as a large-scale problem? It also only reproduces on OSX (both El Capitan and Mavericks) - Android, iOS, PC all work fine. Anyway, it's not a big deal for us to set mmap_size to 0 to work around this. The big problem I have however is the .wal. We use SQLITE from multiple threads, and as such it's using shared memory to read/write the .wal. However, if persisting memory mapped files on OSX is so unreliable, then how can the .wal be expected to survive a hard reboot... So I'd also like to have a way to not use memory mapped I/O for .wal files, but I don't think there is a way unless I change the architecture of my app to have single-threaded access to SQLITE? Or is there another way? - Deon
