Hi Deon, That's interesting about the memory mapped IO. I just looked in the source for Couchbase Lite and they do something like that on setup:
+ (void) firstTimeSetup { // Test the version of the actual SQLite implementation at runtime. Necessary because // the app might be linked with a custom version of SQLite (like SQLCipher) instead of the // system library, so the actual version/features may differ from what was declared in // sqlite3.h at compile time. Log(@"Couchbase Lite using SQLite version %s (%s)", sqlite3_libversion(), sqlite3_sourceid()); #if 0 for (int i=0; true; i++) { const char* opt = sqlite3_compileoption_get(i); if (!opt) break; Log(@"SQLite option '%s'", opt); } #endif sSQLiteVersion = sqlite3_libversion_number(); Assert(sSQLiteVersion >= 3007000, @"SQLite library is too old (%s); needs to be at least 3.7", sqlite3_libversion()); // Enable memory-mapped I/O if available #ifndef SQLITE_CONFIG_MMAP_SIZE #define SQLITE_CONFIG_MMAP_SIZE 22 /* sqlite3_int64, sqlite3_int64 */ #endif int err = sqlite3_config(SQLITE_CONFIG_MMAP_SIZE, (SInt64) kSQLiteMMapSize, (SInt64)-1); if (err != SQLITE_OK) Log(@"FYI, couldn't enable SQLite mmap: error %d", err); sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, NULL); } From your description, it sounds like that be causing this problem. Hmm... Brendan On Tue, Apr 18, 2017 at 2:50 AM, Deon Brewis <de...@outlook.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users