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
