SQLite database becomes corrupt on iOS

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite database becomes corrupt on iOS

mailing lists
Hi,

does anybody have any similar experience that an SQLite database becomes corrupt on iOS? It seems to be that when the iDevice is running out of battery it can happen that the SQLite becomes corrupt. I have currently not found a possibility to reproduce it but once a while my customers experience this behavior.

I am compiling SQLite with

SQLITE_THREADSAFE=1 SQLITE_ENABLE_RTREE=1 SQLITE_ENABLE_FTS3=1 SQLITE_ENABLE_FTS3_PARENTHESIS=1

Before my app closes I close the database explicitly. Though I do not know if this happens always when the iDevice shuts down due to battery issues.

Any help is appreciated.

PS: I have checked the information how an SQLite can become corrupted but none of them apply to my app.

Regards,
Hartwig
 

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

Re: SQLite database becomes corrupt on iOS

Simon Slavin-3

On 13 Aug 2015, at 6:32pm, skywind mailing lists <[hidden email]> wrote:

> Before my app closes I close the database explicitly. Though I do not know if this happens always when the iDevice shuts down due to battery issues.

iDevices shut down quite a time before they'd actually run out of power.  Before your device shuts down your App will get notified that it's going to background, and then get notified that it's going to quit.  If you're handling those two notifications properly (i.e. closing all SQLite connections at one or the other) then you should not be getting database corruption.

Things to check are running your App inside some sort of memory logger, e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I think it's built in as a 'Tool'.  And also to check the values returned from /all/ your SQLite calls, even ones like _close() where if they fail there's nothing you can do about it.  If one of them is not SQLITE_OK then you should show an error message.  The call which corrupts the database is sometimes an apparently harmless call.

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
|

Re: SQLite database becomes corrupt on iOS

Eric Sink
https://www.sqlite.org/howtocorrupt.html

I know you said you already checked this, so just ignore the following
remark:

iOS is one of the easiest platforms to accidentally end up with "Multiple
copies of SQLite linked into the same application".

Just sayin'.

--
E


On Thu, Aug 13, 2015 at 2:04 PM, Simon Slavin <[hidden email]> wrote:

>
> On 13 Aug 2015, at 6:32pm, skywind mailing lists <[hidden email]>
> wrote:
>
> > Before my app closes I close the database explicitly. Though I do not
> know if this happens always when the iDevice shuts down due to battery
> issues.
>
> iDevices shut down quite a time before they'd actually run out of power.
> Before your device shuts down your App will get notified that it's going to
> background, and then get notified that it's going to quit.  If you're
> handling those two notifications properly (i.e. closing all SQLite
> connections at one or the other) then you should not be getting database
> corruption.
>
> Things to check are running your App inside some sort of memory logger,
> e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I
> think it's built in as a 'Tool'.  And also to check the values returned
> from /all/ your SQLite calls, even ones like _close() where if they fail
> there's nothing you can do about it.  If one of them is not SQLITE_OK then
> you should show an error message.  The call which corrupts the database is
> sometimes an apparently harmless call.
>
> Simon.
> _______________________________________________
> 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
|

Re: SQLite database becomes corrupt on iOS

mailing lists
In reply to this post by Simon Slavin-3
Hi,

I do not get any error message from SQLite. And the database only gets corrupted when the iDevice has to shut down due to battery issues. I have never had a customer complaining about a corrupt database during normal operation.

Regards,
Hartwig

> Am 13.08.2015 um 21:04 schrieb Simon Slavin <[hidden email]>:
>
>
> On 13 Aug 2015, at 6:32pm, skywind mailing lists <[hidden email]> wrote:
>
>> Before my app closes I close the database explicitly. Though I do not know if this happens always when the iDevice shuts down due to battery issues.
>
> iDevices shut down quite a time before they'd actually run out of power.  Before your device shuts down your App will get notified that it's going to background, and then get notified that it's going to quit.  If you're handling those two notifications properly (i.e. closing all SQLite connections at one or the other) then you should not be getting database corruption.
>
> Things to check are running your App inside some sort of memory logger, e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I think it's built in as a 'Tool'.  And also to check the values returned from /all/ your SQLite calls, even ones like _close() where if they fail there's nothing you can do about it.  If one of them is not SQLITE_OK then you should show an error message.  The call which corrupts the database is sometimes an apparently harmless call.
>
> Simon.
> _______________________________________________
> 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
|

Re: SQLite database becomes corrupt on iOS

Simon Slavin-3

On 14 Aug 2015, at 4:10pm, skywind mailing lists <[hidden email]> wrote:

> I do not get any error message from SQLite. And the database only gets corrupted when the iDevice has to shut down due to battery issues. I have never had a customer complaining about a corrupt database during normal operation.

If you run the application on your own iDevice, and let it run out of power, do you get the corruption yourself ?

Do you have any way to log when you're getting background and quit notifications ?  Could you have your application store them in a text file (not a SQLite database, obviously) so you can check to see whether it's getting them before the device runs out of power ?

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
|

Re: SQLite database becomes corrupt on iOS

mailing lists
In reply to this post by Eric Sink
Hi,

I think that I can exclude for 99.99% that there are two SQLite version because I should get linker errors. Anyway, what is definitely guaranteed that these two versions know anything from each other and that they are working on the same database.

Regards,
Hartwig
 

> Am 13.08.2015 um 21:42 schrieb Eric Sink <[hidden email]>:
>
> https://www.sqlite.org/howtocorrupt.html
>
> I know you said you already checked this, so just ignore the following
> remark:
>
> iOS is one of the easiest platforms to accidentally end up with "Multiple
> copies of SQLite linked into the same application".
>
> Just sayin'.
>
> --
> E
>
>
> On Thu, Aug 13, 2015 at 2:04 PM, Simon Slavin <[hidden email]> wrote:
>
>>
>> On 13 Aug 2015, at 6:32pm, skywind mailing lists <[hidden email]>
>> wrote:
>>
>>> Before my app closes I close the database explicitly. Though I do not
>> know if this happens always when the iDevice shuts down due to battery
>> issues.
>>
>> iDevices shut down quite a time before they'd actually run out of power.
>> Before your device shuts down your App will get notified that it's going to
>> background, and then get notified that it's going to quit.  If you're
>> handling those two notifications properly (i.e. closing all SQLite
>> connections at one or the other) then you should not be getting database
>> corruption.
>>
>> Things to check are running your App inside some sort of memory logger,
>> e.g. valgrind .  I don't know if this is possible on the Xcode simulator, I
>> think it's built in as a 'Tool'.  And also to check the values returned
>> from /all/ your SQLite calls, even ones like _close() where if they fail
>> there's nothing you can do about it.  If one of them is not SQLITE_OK then
>> you should show an error message.  The call which corrupts the database is
>> sometimes an apparently harmless call.
>>
>> Simon.
>> _______________________________________________
>> 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
|

Re: SQLite database becomes corrupt on iOS

mailing lists
In reply to this post by Simon Slavin-3
Hi,

this is the problem. I could never reproduce it by myself and even my customers have normally no problems. But it happens once a year or so to one of my customers.
Still this is very annoying as it results in data loss.

Regards,
Hartwig

> Am 14.08.2015 um 17:15 schrieb Simon Slavin <[hidden email]>:
>
>
> On 14 Aug 2015, at 4:10pm, skywind mailing lists <[hidden email]> wrote:
>
>> I do not get any error message from SQLite. And the database only gets corrupted when the iDevice has to shut down due to battery issues. I have never had a customer complaining about a corrupt database during normal operation.
>
> If you run the application on your own iDevice, and let it run out of power, do you get the corruption yourself ?
>
> Do you have any way to log when you're getting background and quit notifications ?  Could you have your application store them in a text file (not a SQLite database, obviously) so you can check to see whether it's getting them before the device runs out of power ?
>
> Simon.
> _______________________________________________
> 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
|

Re: SQLite database becomes corrupt on iOS

RichardR
Are all your customers on the latest version of iOS?  Maybe you need to make your app only compatible with current versions that behave properly?  What version of iOS and model is the customer that has the issue running?

Regards,

A fly on the wall...

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of skywind mailing lists
Sent: Friday, August 14, 2015 10:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite database becomes corrupt on iOS

Hi,

this is the problem. I could never reproduce it by myself and even my customers have normally no problems. But it happens once a year or so to one of my customers.
Still this is very annoying as it results in data loss.

Regards,
Hartwig

> Am 14.08.2015 um 17:15 schrieb Simon Slavin <[hidden email]>:
>
>
> On 14 Aug 2015, at 4:10pm, skywind mailing lists <[hidden email]> wrote:
>
>> I do not get any error message from SQLite. And the database only gets corrupted when the iDevice has to shut down due to battery issues. I have never had a customer complaining about a corrupt database during normal operation.
>
> If you run the application on your own iDevice, and let it run out of power, do you get the corruption yourself ?
>
> Do you have any way to log when you're getting background and quit notifications ?  Could you have your application store them in a text file (not a SQLite database, obviously) so you can check to see whether it's getting them before the device runs out of power ?
>
> Simon.
> _______________________________________________
> 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
This communication is the property of CenturyLink and may contain confidential or privileged information. Unauthorized use of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy all copies of the communication and any attachments.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite database becomes corrupt on iOS

Richard Hipp-3
In reply to this post by mailing lists
On 8/14/15, skywind mailing lists <[hidden email]> wrote:

> I think that I can exclude for 99.99% that there are two SQLite version
> because I should get linker errors.

Sadly, no.

But on the bright side, even if you do have two or more copies of
SQLite linked into your binary, everything will still work fine as
long as you do not open the same SQLite database file at the same time
in the same process using different versions of SQLite for each
connection.

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

Re: SQLite database becomes corrupt on iOS

Simon Slavin-3
In reply to this post by mailing lists

On 14 Aug 2015, at 4:17pm, skywind mailing lists <[hidden email]> wrote:

> this is the problem. I could never reproduce it by myself and even my customers have normally no problems. But it happens once a year or so to one of my customers.
> Still this is very annoying as it results in data loss.

I have seen a problem like this only in one context, and it had nothing to do with SQLite.  When an iDevice's power starts running low, it sends out notifications to all running Apps and expects their cooperation in shutting down.  An App is meant to react to the notification and shut down in less than (IIRC) 6 seconds, without depending on network connections and without using unusual amounts of power.

If one of the running Apps does not do this properly then the operating system is allowed to terminate it at (IIRC) 10 seconds, even if it is still working.  But if the application is really annoying it can block other apps -- like yours -- from shutting down before the power is lost, by hogging resources like CPU or file storage.  So your app gets terminated at 10 seconds even though it wasn't given a chance to close its files.  Either by the OS or because the device just ran out of power.

This could happen because of a bug.  But I saw it happen in a big corporate application where the programmer decided that it just had to do a ton of communication with the server and clean up files every time the app quit.  Reasonable on a desktop computer and a disaster on a phone.

Testing correct quit behaviour is part of Apple's approval process.  But it can't test every state the app may be in when it receives a quit notification.

So the thing that might be triggering your corruption is that your customer is running another application at the same time -- one which hogs resources so much that your own application doesn't get a chance to shut down cleanly before the power runs out.  You're never going to be able to figure out the problem in your own app, because there isn't one.  You need to look at the environment as a whole.

One possible way to proceed is to ask the customer what other apps they were likely to have had running when the device ran out of power.  Then google those apps and see if there is any suggestion that any of them might be badly written or badly behaved on shutdown.

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
|

Re: SQLite database becomes corrupt on iOS

Random Coder
On Fri, Aug 14, 2015 at 8:38 AM, Simon Slavin <[hidden email]> wrote:
> I have seen a problem like this only in one context, and it had nothing to do with SQLite.  When an iDevice's power starts running low, it sends out notifications to all running Apps and expects their cooperation in shutting down.  An App is meant to react to the notification and shut down in less than (IIRC) 6 seconds, without depending on network connections and without using unusual amounts of power.

And just to add to this:  This is is reasonably good conditions.

I've dealt with a bug (unrelated to SQLite) that a tester of mine
could create data corruption on an iOS device.  The tester's device
had a bad battery, so by the time the device thought it had minutes of
power, it had seconds of life left, at best.  The tester was quite
adept at getting their device in a specific state that would cause our
app to destroy a data file.  I could never reproduce the condition on
my device, and indeed this tester couldn't either.  It had to be this
specific device with it's battery.

This is a lot of words to say:  All sorts of bad things are possible
when these devices start to lose power.  I've run into other issues
that lead me to believe the OS is caching file writes until the app
exits in some situations regardless of various sync calls, but I never
did have time to track down if I was just fooling myself, or if the OS
was indeed doing things to "help" me out.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite database becomes corrupt on iOS

Rob Willett
In reply to this post by Simon Slavin-3
Simon,

Thanks very much for this information.

Rob.

> On 14 Aug 2015, at 17:38, Simon Slavin <[hidden email]> wrote:
>
>
> On 14 Aug 2015, at 4:17pm, skywind mailing lists <[hidden email]> wrote:
>
>> this is the problem. I could never reproduce it by myself and even my customers have normally no problems. But it happens once a year or so to one of my customers.
>> Still this is very annoying as it results in data loss.
>
> I have seen a problem like this only in one context, and it had nothing to do with SQLite.  When an iDevice's power starts running low, it sends out notifications to all running Apps and expects their cooperation in shutting down.  An App is meant to react to the notification and shut down in less than (IIRC) 6 seconds, without depending on network connections and without using unusual amounts of power.
>
> If one of the running Apps does not do this properly then the operating system is allowed to terminate it at (IIRC) 10 seconds, even if it is still working.  But if the application is really annoying it can block other apps -- like yours -- from shutting down before the power is lost, by hogging resources like CPU or file storage.  So your app gets terminated at 10 seconds even though it wasn't given a chance to close its files.  Either by the OS or because the device just ran out of power.
>
> This could happen because of a bug.  But I saw it happen in a big corporate application where the programmer decided that it just had to do a ton of communication with the server and clean up files every time the app quit.  Reasonable on a desktop computer and a disaster on a phone.
>
> Testing correct quit behaviour is part of Apple's approval process.  But it can't test every state the app may be in when it receives a quit notification.
>
> So the thing that might be triggering your corruption is that your customer is running another application at the same time -- one which hogs resources so much that your own application doesn't get a chance to shut down cleanly before the power runs out.  You're never going to be able to figure out the problem in your own app, because there isn't one.  You need to look at the environment as a whole.
>
> One possible way to proceed is to ask the customer what other apps they were likely to have had running when the device ran out of power.  Then google those apps and see if there is any suggestion that any of them might be badly written or badly behaved on shutdown.
>
> Simon.
> _______________________________________________
> 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
|

Re: SQLite database becomes corrupt on iOS

Simon Slavin-3
In reply to this post by Random Coder

On 14 Aug 2015, at 5:16pm, Random Coder <[hidden email]> wrote:

> I've run into other issues
> that lead me to believe the OS is caching file writes until the app
> exits in some situations regardless of various sync calls, but I never
> did have time to track down if I was just fooling myself, or if the OS
> was indeed doing things to "help" me out.

The hardware in iDevices varies (obviously) between the many devices which have been produced over the years.  But I believe that you're right in that it's not possible for the OS to tell when data has /really/ been written to permanent storage.  Such writing takes a lot of power and it makes sense that a device would want to do it infrequently.

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
|

Re: SQLite database becomes corrupt on iOS

Scott Perry
On Aug 14, 2015, at 10:13 AM, Simon Slavin <[hidden email]> wrote:
> On 14 Aug 2015, at 5:16pm, Random Coder <[hidden email]> wrote:
>> I've run into other issues
>> that lead me to believe the OS is caching file writes until the app
>> exits in some situations regardless of various sync calls, but I never
>> did have time to track down if I was just fooling myself, or if the OS
>> was indeed doing things to "help" me out.
>
> The hardware in iDevices varies (obviously) between the many devices which have been produced over the years.  But I believe that you're right in that it's not possible for the OS to tell when data has /really/ been written to permanent storage.  Such writing takes a lot of power and it makes sense that a device would want to do it infrequently.

If the device runs out of power normally, your writes are guaranteed. However, your app will get no notice. It's equivalent to getting jetsamed during low-memory conditions.

That said, it's possible to corrupt a database by forcing the device to power off (by holding the power and home buttons) while SQLite's writes are in flight. This is equivalent to the conditions of a kernel panic.

Using the system-provided SQLite helps, the unix VFS is modified to be (somewhat) more robust on Apple's platforms.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite database becomes corrupt on iOS

Simon Slavin-3

On 20 Aug 2015, at 6:22am, Scott Perry <[hidden email]> wrote:

> That said, it's possible to corrupt a database by forcing the device to power off (by holding the power and home buttons) while SQLite's writes are in flight. This is equivalent to the conditions of a kernel panic.

This is true only if your storage subsystem (usually hard disk plus it's driver) does not work properly, and a couple of bad coincidences happen.  In almost all situations, when SQLite next accesses the database it will notice a journal file still exists for it and use the information in the journal file to uncorrupt the database.

The resulting database might or might not reflect the last transaction's changes.  It depends on precisely when power was lost.  But it should not be corrupt, and it should reflect all changes up to the end of a transaction.

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
|

Re: SQLite database becomes corrupt on iOS

Scott Perry
On Aug 19, 2015, at 11:34 PM, Simon Slavin <[hidden email]> wrote:
>
>
> On 20 Aug 2015, at 6:22am, Scott Perry <[hidden email]> wrote:
>
>> That said, it's possible to corrupt a database by forcing the device to power off (by holding the power and home buttons) while SQLite's writes are in flight. This is equivalent to the conditions of a kernel panic.
>
> This is true only if your storage subsystem (usually hard disk plus it's driver) does not work properly, and a couple of bad coincidences happen.  In almost all situations, when SQLite next accesses the database it will notice a journal file still exists for it and use the information in the journal file to uncorrupt the database.
>
> The resulting database might or might not reflect the last transaction's changes.  It depends on precisely when power was lost.  But it should not be corrupt, and it should reflect all changes up to the end of a transaction.

If the NAND controller is interrupted by power loss or kernel panic, it is possible for a write to be partially applied to disk, a condition that SQLite cannot defend against.

These conditions are extremely rare, especially in the wild, but at scale every possible error is a certainty.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users