Re: Database corruption, and PRAGMA fullfsync on macOS

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Simon Slavin-3
On 18 Apr 2017, at 6:46pm, Jens Alfke <[hidden email]> 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 don’t flush all cached data to the platters when told to flush, apparently to boost benchmark scores. Darwin has a FULLFSYNC command (an option to ioctl) that triggers an expensive full reset of the disk controller, which does produce a full flush.

That’s what "PRAGMA fullfsync" is about.  From what I can see, SQLite uses it in a way that Apple’s documentation says should work.  But it seems that despite being all Apple says is necessary, it’s not doing the job.  Note, however, that SQLite documentation says that the default value for this flag is off, even when running on a Mac.

> [snip]
>
> This doesn’t seem like it would be specific to memory-mapped I/O, though. Darwin has a universal buffer cache (like many other kernels) so memory-mapped writes and ordinary file writes are treated the same way by the filesystem cache.

Need to check Darwin source code.  The documentation for some operating systems considers memory-mapping to be special, and separate from normal file access.  So things which configure how normal file access is done sometimes don’t apply to memory-mapped file access or memory-mapped I/O in general.  Power-loss is a good test of memory-mapped changes and problems with them.

> Of course it’s possible there’s some kind of OS bug involved here; if so, it seems pretty serious. I’ll be glad to file a bug report with Apple if it turns out to be so.

People who’ve posted things connected to this don’t tend to mention that they’ve set "PRAGMA fullfsync = ON".  It’s the sort of thing you’d only know to do after significant debugging.  Maybe they’re not doing it and that’s the cause of their problems.

Simon.

Obligatory Disclosure: I’m a Macintosh specialist, a Macintosh programmer, and I know a lot about computer security.  But this stuff is at the edge of my area of competence.  I’ve never worked for Apple and don’t know the tiny details of this stuff.  Don't depend on the things I write here for critical decisions.  If you’re a member of Apple’s Developer Programme perhaps you should get Apple involved.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

brendand
In reply to this post by Jens Alfke-2
> 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
> scenarios like this would be covered as part of SQLite testing, either by
> SQLite themselves or by Apple.
>

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'm not sure.
But I've been testing more and more now and by disabling the memory mapped
I/O, the database file corruption bug appears to be gone. It's all working
great now. And I could easily cause the corruption prior to that.

I just reverted back to the build that included the memory mapped I/O call
and I was again able to corrupt the database file with the power failure.
Then I again used the version that had memory mapped I/O disabled and I was
no longer able to corrupt the database. So I've tested it both ways
repeatedly and I think I'm going to conclude that the memory mapped I/O
call is the culprit.

Thanks again Deon for sharing your experience with memory mapped I/O in
SQLite on macOS. And thanks Jens for starting this thread to try and get to
the bottom of this problem.

Brendan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Simon Slavin-3

On 18 Apr 2017, at 7:37pm, Brendan Duddridge <[hidden email]> 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 think this can be the problem.  As you wrote, a package is just a folder with a bit set.  Not only do they not affect anything but it’s difficult for your app to even tell whether the file is in a folder or a package.

If you’re keeping the database inside the application’s bundle, that’s different.  But you’re not doing that.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Jens Alfke-2
In reply to this post by Deon Brewis

> On Apr 18, 2017, at 2:20 AM, Deon Brewis <[hidden email]> 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 reboot the machine.

OK, hang on — I just reread the docs on memory-mapped I/O in SQLite. Memory-mapping is *only used for reads*, never for writes:

>> When updating the database file, SQLite always makes a copy of the page content into heap memory before modifying the page. This is necessary for two reasons. First, changes to the database are not supposed to be visible to other processes until after the transaction commits and so the changes must occur in private memory. Second, SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file.
        — https://www.sqlite.org/mmap.html

Therefore I can’t imagine how using it could trigger database corruption. It doesn’t affect the way data is written at all!

I accept that both of you have experimentally seen that memory-mapping leads to corruption, so I can only assume that either the above documentation is wrong, or that there’s some subtle bug in SQLite that alters the way data is written when memory-mapping is enabled.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Jeffrey Mattox
In reply to this post by Simon Slavin-3
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 elsewhere (e.g., ~/Documents/).  I'd never store a file that I write to in the app bundle.

Jeff


> On Apr 18, 2017, at 1:45 PM, Simon Slavin <[hidden email]> wrote:
>
> I don’t think this can be the problem.  As you wrote, a package is just a folder with a bit set.  Not only do they not affect anything but it’s difficult for your app to even tell whether the file is in a folder or a package.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Jens Alfke-2

> On Apr 18, 2017, at 12:55 PM, Jeffrey Mattox <[hidden email]> 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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

brendand
In reply to this post by Jens Alfke-2
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 documentation you
found.

Can I ask what the benefit of having memory mapped I/O is for SQLite? And
what are the drawbacks of turning it off? I understand what memory mapped
I/O is, reducing the amount of memory needed to load in the data from a
file. I use it in other parts of my app such as adding file attachments to
an email, but I use the higher level NSDataReadingzMappedAlways option on
NSData.

NSData *pdfData = [[NSData alloc] initWithContentsOfURL:pdfURL options:
NSDataReadingMappedAlways error:nil];


I'm just wondering if I'm going to encounter any other issues by disabling
it. Perhaps it needs to be conditionalized for Mac vs. iOS?

On Tue, Apr 18, 2017 at 1:42 PM, Jens Alfke <[hidden email]> wrote:

>
> > On Apr 18, 2017, at 2:20 AM, Deon Brewis <[hidden email]> 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 reboot the
> machine.
>
> OK, hang on — I just reread the docs on memory-mapped I/O in SQLite.
> Memory-mapping is *only used for reads*, never for writes:
>
> >> When updating the database file, SQLite always makes a copy of the page
> content into heap memory before modifying the page. This is necessary for
> two reasons. First, changes to the database are not supposed to be visible
> to other processes until after the transaction commits and so the changes
> must occur in private memory. Second, SQLite uses a read-only memory map to
> prevent stray pointers in the application from overwriting and corrupting
> the database file.
>         — https://www.sqlite.org/mmap.html
>
> Therefore I can’t imagine how using it could trigger database corruption.
> It doesn’t affect the way data is written at all!
>
> I accept that both of you have experimentally seen that memory-mapping
> leads to corruption, so I can only assume that either the above
> documentation is wrong, or that there’s some subtle bug in SQLite that
> alters the way data is written when memory-mapping is enabled.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Bob Friesenhahn
In reply to this post by Jens Alfke-2
On Tue, 18 Apr 2017, Jens Alfke wrote:

>
>> On Apr 18, 2017, at 2:20 AM, Deon Brewis <[hidden email]> 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 reboot the machine.
>
> OK, hang on — I just reread the docs on memory-mapped I/O in SQLite. Memory-mapping is *only used for reads*, never for writes:
>
>>> When updating the database file, SQLite always makes a copy of the page content into heap memory before modifying the page. This is necessary for two reasons. First, changes to the database are not supposed to be visible to other processes until after the transaction commits and so the changes must occur in private memory. Second, SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file.
> — https://www.sqlite.org/mmap.html
>
> Therefore I can’t imagine how using it could trigger database corruption. It doesn’t affect the way data is written at all!

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 what is
in the file, resulting in wrong data being written to the file.

GraphicsMagick has a configure test for this and some operating
systems fail the test.  Sqlite3 is free to adopt this same test.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Jens Alfke-2

> On Apr 18, 2017, at 2:35 PM, Bob Friesenhahn <[hidden email]> 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 what is in the file, resulting in wrong data being written to the file.

Darwin is fully coherent this way (that’s basically what a Universal Buffer Cache guarantees.)

Even if it weren’t, the incoherency would cause problems while making changes, i.e. _before_ the power-loss, not afterwards. Which is not what’s seen.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Deon Brewis
In reply to this post by Jens Alfke-2
> That is a hugely important piece of information, and while I thank you for letting me know, I’m shocked that this hasn’t been conveyed to the SQLite developers (or, apparently, to Apple.) Did you report it anywhere?

I did report it:
http://sqlite.1065341.n5.nabble.com/SQLITE-vs-OSX-mmap-inevitable-catalog-corruption-td85620.html

- Deon

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Jens Alfke
Sent: Tuesday, April 18, 2017 10:47 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS


> On Apr 18, 2017, at 1:50 AM, Deon Brewis <[hidden email]> 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.

That is a hugely important piece of information, and while I thank you for letting me know, I’m shocked that this hasn’t been conveyed to the SQLite developers (or, apparently, to Apple.) Did you report it anywhere?

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 scenarios like this would be covered as part of SQLite testing, either by SQLite themselves or by Apple.

> “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 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 don’t flush all cached data to the platters when told to flush, apparently to boost benchmark scores. Darwin has a FULLFSYNC command (an option to ioctl) that triggers an expensive full reset of the disk controller, which does produce a full flush.

The filesystem periodically performs FULLFSYNCs to ensure durability of changes to filesystem metadata, so that the filesystem itself can’t become corrupted by power loss. So I’m surprised that, in your test, cutting power after macroscopic amounts of time (2 minutes) have passed since closing the SQLite file still resulted in data being lost.

This doesn’t seem like it would be specific to memory-mapped I/O, though. Darwin has a universal buffer cache (like many other kernels) so memory-mapped writes and ordinary file writes are treated the same way by the filesystem cache.

Of course it’s possible there’s some kind of OS bug involved here; if so, it seems pretty serious. I’ll be glad to file a bug report with Apple if it turns out to be so.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Jens Alfke-2

> On Apr 18, 2017, at 9:46 PM, Deon Brewis <[hidden email]> 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 yesterday, SQLite uses a read-only mapping in mmap mode. The db
>> file is written using plain old write(), just as in non-mmap mode:
>>
>>    http://sqlite.org/src/info/67c5d3c646c8198c
>>
>> It would be interesting to know if this clears the problem in your
>> environment.

Have you re-run your test with versions of SQLite from after this change?

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Dan Kennedy-4
In reply to this post by Jens Alfke-2
On 04/19/2017 02:42 AM, Jens Alfke wrote:
>> On Apr 18, 2017, at 2:20 AM, Deon Brewis <[hidden email]> 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 reboot the machine.
> OK, hang on — I just reread the docs on memory-mapped I/O in SQLite. Memory-mapping is *only used for reads*, never for writes:

It was used for writes in versions before 3.10.0 (January 2016). And
still is if SQLITE_MMAP_READWRITE is defined (not the default).

Dan.





>
>>> When updating the database file, SQLite always makes a copy of the page content into heap memory before modifying the page. This is necessary for two reasons. First, changes to the database are not supposed to be visible to other processes until after the transaction commits and so the changes must occur in private memory. Second, SQLite uses a read-only memory map to prevent stray pointers in the application from overwriting and corrupting the database file.
> — https://www.sqlite.org/mmap.html
>
> Therefore I can’t imagine how using it could trigger database corruption. It doesn’t affect the way data is written at all!
>
> I accept that both of you have experimentally seen that memory-mapping leads to corruption, so I can only assume that either the above documentation is wrong, or that there’s some subtle bug in SQLite that alters the way data is written when memory-mapping is enabled.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

brendand
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,
so I'm sure it's not setting it. All I know now is with mmap I/O on, I get
the corruption when the power goes out. With it off, I don't.

On Wed, Apr 19, 2017 at 12:21 AM, Dan Kennedy <[hidden email]> wrote:

> On 04/19/2017 02:42 AM, Jens Alfke wrote:
>
>> On Apr 18, 2017, at 2:20 AM, Deon Brewis <[hidden email]> 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 reboot the
>>> machine.
>>>
>> OK, hang on — I just reread the docs on memory-mapped I/O in SQLite.
>> Memory-mapping is *only used for reads*, never for writes:
>>
>
> It was used for writes in versions before 3.10.0 (January 2016). And still
> is if SQLITE_MMAP_READWRITE is defined (not the default).
>
> Dan.
>
>
>
>
>
>
>
>> When updating the database file, SQLite always makes a copy of the page
>>>> content into heap memory before modifying the page. This is necessary for
>>>> two reasons. First, changes to the database are not supposed to be visible
>>>> to other processes until after the transaction commits and so the changes
>>>> must occur in private memory. Second, SQLite uses a read-only memory map to
>>>> prevent stray pointers in the application from overwriting and corrupting
>>>> the database file.
>>>>
>>>         — https://www.sqlite.org/mmap.html
>>
>> Therefore I can’t imagine how using it could trigger database corruption.
>> It doesn’t affect the way data is written at all!
>>
>> I accept that both of you have experimentally seen that memory-mapping
>> leads to corruption, so I can only assume that either the above
>> documentation is wrong, or that there’s some subtle bug in SQLite that
>> alters the way data is written when memory-mapping is enabled.
>>
>> —Jens
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corruption, and PRAGMA fullfsync on macOS

Fahad
This post has NOT been accepted by the mailing list yet.
I'm just here to chime-in that I've been experiencing this very issue for some time now (ever since I switched to using WAL journal_mode along with SQLITE_THREADSAFE=2). Recently iOS 10 has become super picky about database connections and would kill any app that holds on to a disk resource once it's been backgrounded. In the past our app would connect to a single database connection and close it only on termination. Since we were making the change for iOS, we made the same for macOS where we release open and close the connection rather aggressively to ensure we're not keeping it opened for longer than we have to.

Each background-thread in the Mac app opens and closes a read-only connection whilst we have a single write-only connection that is used exclusively on the main thread. This model works just fine but suddenly resulted in more and more users experiencing database corruption (force-closing the app, powering down the laptop etc).

Here's the rather scary part: We weren't using MMAP at all, neither had we configured it. Does SQLite configure MMAP by default? We're now pushed an update by explicitly issuing "PRAGMA mmap_size=0" on each connection we use, but I'm not entirely sure if this will fix it (although I've read above that this has indeed fixed it for you guys). If SQLite uses MMAP on the Mac by default, then that answers my concerns, but simply running "PRAGMA mmap_size" on the terminal returns "0".
12
Loading...