Are you by change using memory mapped IO (MMAP_SIZE something other than 0)?
This does not work on OSX. Not even remotely. I tracked an issue down in November 2015, and was able to trivially corrupt a database 100% of the time using the repro steps below. This happens long after our app gets shut down and SQLITE is flushed. It got fixed when I set MMAP_SIZE to 0. Repro (100%): 1) Run our app 2) Shutdown our app 3) Wait for our app to cleanly shut down – nothing showing in Activity Monitor – and app.db-wal deleted from disk (i.e. SQLITE clean close) 4) Wait 2 minutes (so our app isn’t running in this 2 minute period at all) 5) Copy the our db file to a NAS 6) Hard reboot the machine (power cycle). 7) Copy the db file to the NAS again (no reopening the app, just copy the file back to the NAS after the reboot). Observe… The file from #5 still works fine. The file from #7 is corrupted. Note that the app or sqlite is nowhere involved in between #5 and #7 I made this note in the bug when I fixed it - I believe it's related, but don't have the exact context: “From the OSX documentation: Note that while fsync() will flush all data from the host to the drive (i.e. the "permanent storage device"), the drive itself may not physically write the data to the platters for quite some time and it may be written in an out-of-order sequence. Specifically, if the drive loses power or the OS crashes, the application may find that only some or none of their data was written. The disk drive may also re-order the data so that later writes may be present, while earlier writes are not. This is not a theoretical edge case. This scenario is easily reproduced with real world workloads and drive power failures.” - Deon -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Brendan Duddridge Sent: Tuesday, April 18, 2017 1:36 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS Hi Rowan, My apologies. My sample app does use NSDocument. But my production app doesn't. And they both exhibit the same problem. On Tue, Apr 18, 2017 at 12:29 AM, Rowan Worth <row...@dug.com> wrote: > Hey Brendan, > > I'm no OSX expert, but from what I've read this afternoon about > NSDocument and friends combined with what I know about sqlite I have > to say you are completely mad to continue passing sqlite databases to > NSDocument, > *especially* as you don't define your own sub-class to do any of the > file management. > > Relying on some NSDocument sub-class builtin to the system and then > also opening the DB with CouchbaseLite may well violate section 2.2.1 > of "how to corrupt an sqlite database" (multiple copies of sqlite > linked into the same application). Even if not, it sounds like > NSDocument has a tendency to copy files around for eg. auto-save purposes. > > I'd be very *very* surprised if there's an sqlite bug here. I'd take > the advice given to you on bountysource and watch your application's > file system operations to begin to understand what is going on behind > the scenes. > > -Rowan > > > > On 18 April 2017 at 13:01, Brendan Duddridge <brend...@gmail.com> wrote: > > > Thanks Richard for your reply. > > > > Sorry about the COLLATE problem. That's a Couchbase Lite thing. > > > > I find it weird that a different WAL file is getting in there > > somehow > when > > a power failure occurs. I'm a bit stumped at how it can even write > > to the file system the moment the power shuts down so I don't even > > understand > how > > the database file can get corrupted. Unless the corruption happens > > when the database file is opened up next and tries to use this > > improper WAL file. > > > > In my main project I'm using SQLCipher as my SQLite layer. But in > > the > demo > > project I posted, I'm just using the built-in macOS Sierra SQLite > library. > > In both cases though I can cause SQLite file corruption by cutting > > the power on my MacBook Pro. > > > > I'm going to do further investigations to see if there's any misuse > > of SQLite that I can find. Although the developers of Couchbase Lite > > are far smarter than I and I'm sure they're doing things by the book. > > > > Perhaps the only think I can think of that I'm doing that may be > unorthodox > > is by storing the SQLite files inside a macOS package. But at the > > unix layer that's really just a directory, so I don't know how that > > could > cause > > a problem. Unless macOS treats the package in a way differently than > > a normal folder and is causing things to get moved around or written > > when a power failure occurs. > > > > This problem has been plaguing me for quite a long time actually. I > > hope that I can find a solution somehow. > > > > Thanks, > > > > Brendan > > > > > I worked around the "COLLATE JSON" problem (by writing my own JSON > > > collation). That allows me to analyze your database and WAL file. > > > > > > It appears that the WAL file is not the correct WAL file for that > > > database. It is as if someone has taken an unrelated WAL file and > > > renamed it to have the same base name as your database. Or the > > > other way around - someone has renamed your database to have the > > > same base name as the WAL file. > > > > > > Your demonstration application does not call SQLite directly. > > > Instead it appears to use two libraries that in turn call SQLite: > > > libsqlcrypt.a and the CouchbaseLite.framework. You do not provide > > > sources to these other libraries, so I am unable to deduce what > > > they are doing. > > > > > > So, in the absence of further evidence, I am going to diagnose > > > this as a misuse of SQLite by one of the two libraries that you > > > are linking - probably a misuse in the form of trying to rename or > > > unlink or otherwise modify the database file using ordinary > > > operating system calls while a connection to the database is open. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
