Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

I'm using SQLCipher in my main app and it's using SQLite 3.15.2. However, my little sample app where I could also easily demonstrate the problem, is using whatever the built-in SQLite version is in macOS Sierra 10.12.4. I couldn't find SQLITE_MMAP_READWRITE in the Couchbase Lite source anywhere,

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 04/19/2017 02:42 AM, Jens Alfke wrote: On Apr 18, 2017, at 2:20 AM, Deon Brewis wrote: It's not like it was subtle - it's a dead on repro. I was able to repro this by doing a power cycle 2 hours after shutting the app down. OSX didn't seem to have any interest in

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Apr 18, 2017, at 9:46 PM, Deon Brewis wrote: > > I did report it: > http://sqlite.1065341.n5.nabble.com/SQLITE-vs-OSX-mmap-inevitable-catalog-corruption-td85620.html Thanks for the pointer to the thread. There was a reply by Dan Kennedy shortly thereafter: >> As of

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

ct: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS > On Apr 18, 2017, at 1: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 track

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Apr 18, 2017, at 2:35 PM, Bob Friesenhahn > wrote: > > If the filesystem implementation is not fully-coherent, then data written by > programmed file I/O may not be reflected in the memory mapped space, > resulting in programs using something other than

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On Tue, 18 Apr 2017, Jens Alfke wrote: On Apr 18, 2017, at 2:20 AM, Deon Brewis wrote: It's not like it was subtle - it's a dead on repro. I was able to repro this by doing a power cycle 2 hours after shutting the app down. OSX didn't seem to have any interest in

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Hi Jens, It would be a good test if you could independently verify my findings using the sample app I wrote to see if you are able to reproduce the corruption with memory mapped I/O turned ON and the fact that there's no corruption when it's turned OFF. I know it seem strange given the

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Apr 18, 2017, at 12:55 PM, Jeffrey Mattox wrote: > > I'm an iOS and macOS developer. Mac app bundles are special in other ways > beside just having a bit set. Brendan is not storing his database in the app bundle. Both he and Simon have already said that. —Jens

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

I'm an iOS and macOS developer. Mac app bundles are special in other ways beside just having a bit set. For one, there's a security check somewhere that verifies that the app bundle has not been changed, as those files are expected to be read-only. Apple says apps should put their data

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Apr 18, 2017, at 2:20 AM, Deon Brewis wrote: > > It's not like it was subtle - it's a dead on repro. I was able to repro this > by doing a power cycle 2 hours after shutting the app down. OSX didn't seem > to have any interest in flushing mmap files until you soft

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 18 Apr 2017, at 7:37pm, Brendan Duddridge wrote: > Well, perhaps the way I've been bundling the db.sqlite file within a > package triggers this particular bug. Although a package is really just a > sub-folder, so I don't see how that would make a difference. I don’t

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> I’m the architect of Couchbase Lite and the lead developer for iOS and > macOS. I enabled SQLite’s memory-mapped I/O at least two years ago. I never > considered it could be problematic since (a) Brendan is IIRC the only > iOS/Mac developer who’s reported database corruption, and (b) I assumed >

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 18 Apr 2017, at 6:46pm, Jens Alfke wrote: > This is a statement about hard disk controller firmware and is true for any > OS. (I used to work for Apple, and corresponded with filesystem architect > Dominic Giampaolo about this back in the day.) Some disk controllers

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Apr 18, 2017, at 1:50 AM, Deon Brewis 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%

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 18 Apr 2017, at 11:52am, Richard Hipp wrote: > I was unaware that memory-mapped I/O was busted on MacOS. I will > investigate further, but probably the solution will be that we will > completely disable memory-mapped I/O on MacOS, just as we have had to > do for OpenBSD.

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 4/18/17, Brendan Duddridge wrote: > > I commented out this line of code in the Couchbase lite CBL_SQliteStorage.m > source file and no more corruption > > //int err = sqlite3_config(SQLITE_CONFIG_MMAP_SIZE, > (SInt64)kSQLiteMMapSize, (SInt64)-1); > > > It would be

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Behalf Of Simon Slavin > Sent: Tuesday, April 18, 2017 1:57 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS > > > On 18 Apr 2017, at 9:50am, Deon Brewis <de...@outlook.com> wrote

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

org> Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS On 18 Apr 2017, at 9:50am, Deon Brewis <de...@outlook.com> wrote: > “From the OSX documentation: > > Note that while fsync() will flush all data from the host to the drive (i.e. > the "perm

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 18 Apr 2017, at 10:07am, Brendan Duddridge wrote: > >// Enable memory-mapped I/O if available Please try an alternative version of that code which definitely does not use memory mapping in any way. You may be able to do it by changing that IFDEF sequence. Or you

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Hi Simon, I'm using Couchbase Lite actually, not Couchbase server. Couchbase Lite 1.4 to be specific. Couchbase Lite 2.0 is still under development and it does in fact use SQLite under the hood as does Couchbase Lite 1.4. They had worked on using ForestDB, but I think they've abandoned that for

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 18 Apr 2017, at 9:50am, Deon Brewis wrote: > “From the OSX documentation: > > Note that while fsync() will flush all data from the host to the drive (i.e. > the "permanent storage device"), Deon, I’m not sure this is related, but have you seen

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Hi Richard, I just did another test with my PowerFailureTest app. I launched it and the count of rows in it was 4402. Without even running the function to import my data that writes to the SQLite file, I cut the power (holding down the power button on my MBP). When my Mac finally rebooted, I saw

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 18 Apr 2017, at 6:01am, Brendan Duddridge wrote: > 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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

lite-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

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 wrote: > Hey Brendan, > > I'm no OSX expert, but from what I've read this afternoon about NSDocument >

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Hey Rowan, In my Mac app I'm actually not using NSDocument. I used to and then I changed my document subclass to extend NSObject instead, just in case NSDocument was doing bad things behind my back. I had also disabled auto-save and versions anyway. For a while though I tried extending

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 4/18/17, Brendan Duddridge wrote: > In both cases though I can cause SQLite file corruption by cutting the > power on my MacBook Pro. Corruption-by-power-loss problems tend to be very sensitive to timing. If you are able to reliably generate the problem by leisurely

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

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

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 4/16/17, Richard Hipp wrote: > I cannot seem to find the implementation for COLLATE JSON anywhere in > your source code. Can you give me a hint as to which source file I > should be looking in? I worked around the "COLLATE JSON" problem (by writing my own JSON collation).

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

I cannot seem to find the implementation for COLLATE JSON anywhere in your source code. Can you give me a hint as to which source file I should be looking in? On 4/16/17, Brendan Duddridge wrote: > Hi, > > I know this is an old thread, but I just found it now when I was

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Hi, I know this is an old thread, but I just found it now when I was doing some research on this topic. Thanks Jens for starting this thread. When Jens said he knew a developer who could create a corrupted database by turning off the power, I'm pretty sure he was talking about me. This has been

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 11/16/16, Simon Slavin wrote: > > There's also a tool from the SQLite team which can analyze a corrupt SQLite > file and tell some things about how it is corrupt. Unfortunately I can't > remember what it's called or where to find it. But I heard about it on this > list

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 16 Nov 2016, at 7:25am, Jens Alfke wrote: > The case I’m concerned about happens on Macs, as I said, and the developer > says he can trigger it in his app via a power failure or a forced shutdown > (holding the power button for 5 seconds.) This version of our library is

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Nov 15, 2016, at 1:46 PM, Simon Slavin wrote: > > Do these people admit they're letting their phones run out of power ? There’s nothing wrong with letting your phone run out of power, and software should be resilient to it. I don’t think that’s the problem,

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 2016/11/15 10:34 PM, Jens Alfke wrote: On Nov 15, 2016, at 10:57 AM, Simon Slavin wrote: sqlite> PRAGMA checkpoint_fullfsync; 1 I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its value is 1 even if I don’t first set pragma fullfsync; i.e. it

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

At 22:41 15/11/2016, you wrote: So if you're truly worried about flush-to-disk what do you do ? Solution 1 is to buy hard disks rated for servers -- sometimes called "enterprise-class hard drives" -- and to set the DIP switches to tell them they're being used on a server. Those things are

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On Tue, 15 Nov 2016, Simon Slavin wrote: Modern storage subsystems (hard disk or SSD) intended for use in a normal user computer always lie to the OS about flushing to disk. The apparent increase in speed from doing this is so big that every manufacturer has to do it, or risk having every

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 15 Nov 2016, at 8:34pm, Jens Alfke wrote: > (Sorry to be frothing at the mouth about this; but my team’s dealing with a > few users/customers whose apps encounter db corruption, on Android as well as > macOS, and we’re getting really frustrated trying to figure out

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 15 Nov 2016, at 8:18pm, Jens Alfke wrote: > The only way to guarantee a true barrier is to really-and-truly flush the > disk controller, which requires not simply flushing but resetting it. That’s > what F_FULLFSYNC on macOS does. (Unfortunately it makes the disk

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 15 Nov 2016, at 8:34pm, Jens Alfke wrote: > On Nov 15, 2016, at 10:57 AM, Simon Slavin wrote: > >> sqlite> PRAGMA checkpoint_fullfsync; >> 1 > > I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its > value is 1 even if I

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Nov 15, 2016, at 10:57 AM, Simon Slavin wrote: > > sqlite> PRAGMA checkpoint_fullfsync; > 1 I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its value is 1 even if I don’t first set pragma fullfsync; i.e. it defaults to 1. (Contradicting the

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

> On Nov 15, 2016, at 10:57 AM, Simon Slavin wrote: > > My understanding is that F_FULLFSYNC still works the way you describe on a > Mac and SQLite still uses it the way the documentation says. But I'm not in > touch with either development group. This seems like a

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

On 15 Nov 2016, at 6:11pm, Jens Alfke wrote: > I verified in the built-in sqlite3 tool on macOS 10.12.1 that the result of > `pragma fullfsync` is 0. The default setting is 0. But you can change it. On my Mac running 10.12.x, SQLite version 3.14.0 2016-07-26 15:17:14

[sqlite] Database corruption, and PRAGMA fullfsync on macOS

I’m seeing conflicting information about SQLite’s use of F_FULLFSYNC on macOS when committing transactions. This is making me nervous about durability and the possibility of database corruption. The SQLite docs for PRAGMA fullfsync (https://www.sqlite.org/pragma.html#pragma_fullfsync