On 11/16/15, Deon Brewis <deon at mylio.com> wrote: > 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.
No. It uses shared memory for the ".shm" file, which is only a performance optimization and is not used for recovery. The ".wal" file is written using write() or pwrite(). > 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? -- D. Richard Hipp drh at sqlite.org