More sqlite header questions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

More sqlite header questions

Chris Brody
Thanks to DRH for the quick answer. So I would infer that the sqlite
file header would normally be in a consistent state, and only be in an
inconsistent or otherwise incorrect state in the following cases:
1. modification (write) is in progress (until the sqlite3 code has a
chance to finish the modification)
2. application crashed or otherwise terminated without finishing the
modification (until the application or some other sqlite program opens
the database again)
3. rogue code writes data to the file descriptor in use by sqlite
4. other causes described in http://www.sqlite.org/howtocorrupt.html
such as rogue code, rogue process, rogue script, OS bug, dishonest
hardware, etc.

My understanding is that iOS applications with shared databases can be
especially sensitive to sqlite headers as discussed in:
- https://developer.apple.com/library/content/technotes/tn2408/_index.html
- note referenced by SQLCipher project, not sure if I can understand
it 100%
-  https://github.com/sqlcipher/sqlcipher/issues/255#issuecomment-355063368
- discovery that iOS checks header of shared SQLite databases, with
special handling of sqlite databases in WAL mode

I still have the following questions:
- Am I correct to say "that the sqlite file header would normally be
in a consistent state"?
- Am I missing anything or otherwise mistaken here?
- How likely would the header continue to indicate that the database
is an sqlite database in case 1 or 2 above?
- How likely would the header continue to indicate whether the
database is in WAL or any other journal mode in case 1 or 2 above?
- Any recommended explanations or resources that explain how iOS
handles shared sqlite databases (in more detail, with simpler terms)?
- Any recommended explanations or resources to understand how and when
sqlite header may be in inconsistent or otherwise incorrect state?
- Any recommended explanations or resources to understand how it may
be possible to obtain the correct sqlite database information (such as
WAL or other journal mode, actual database size, number of pages,
page/cache size, etc.)?

On Tue, Feb 13, 2018 at 9:10 AM, Richard Hipp <[hidden email]> wrote:

>
> On 2/13/18, Chris Brody <[hidden email]> wrote:
> > I was wondering what would happen if there would be an application crash,
> > system crash, or power failure while SQLite is updating the file header?
> >
> > Did I miss an explanation somewhere?
>
> The content is replicated either in the rollback-journal or in the
> write-head log (depending on whether or not you are in WAL mode) and
> will be recovered automatically when the database is first opened
> after power has been restored.  See
> https://www.sqlite.org/atomiccommit.html for further information.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: More sqlite header questions

Simon Slavin-3
On 13 Feb 2018, at 5:49pm, Chris Brody <[hidden email]> wrote:

> I still have the following questions:

Chris,

There are two possibilities:

A) The SQLite API was used correctly, including being allowed to close all files it opened.
B) Any other situation.

If (A) happened, you can predict things about the database header and you can rely on SQLite documentation about the format of files it uses.  Otherwise you can't.

However, the SQLite library goes through heroic measures, every time it opens a database, to detect whether the database was closed correctly and, if it sees any problem, to rescue an uncorrupt version of the database.  All you need to do is open the SQLite database using the SQLite API, and SQLite will take any measures necessary to remedy the results of any power-loss or crash, including returning an error message if it can't arrive at an uncorrupt version of the database.

> 3. rogue code writes data to the file descriptor in use by sqlite

As is true for any library which handles files, nothing can be done about something external overwriting the file.  However, there is no way within the SQLite API to list the a file handles that a SQLite connection is using.  Anything trying to mess with SQLite's file handles would have to arrive at the handle using random numbers, or at the operating system level.

> My understanding is that iOS applications with shared databases can be
> especially sensitive to sqlite headers

This is not true as long as the iOS applications are using the standard SQLite API to access its databases.  There is nothing strange about iOS in this regard, it's just another version of Unix and works the same as other versions of Unix / Linux.

The Apple reference you provided refer to obsolete versions of iOS.  The current version of iOS is version 10, which no longer makes assumptions about SQLite database.  From the Apple page you referred to:

"This has since been resolved in iOS 8.2."

The SQLCipher page seems to indicate that SQLCipher is not compatible with a particular, rarely-used, feature of iOS 8.  The text in the page you supply states clearly that the use SQLCipher makes of this Apple feature is not properly supported.  This has nothing to do with SQLite.  A comment near the foot of the page you referred to says

"We consider this issue resolved. Thanks @sjlombardo and team!"

> - Any recommended explanations or resources that explain how iOS
> handles shared sqlite databases (in more detail, with simpler terms)?

iOS does not handle shared SQLite databases itself.  The 'sharing' is done by the same SQLite API used on other platforms.  iOS would not even know that a database is being shared.  Details on how SQLite handles shared databases can be found at

<https://sqlite.org/lockingv3.html>

> - Any recommended explanations or resources to understand how and when
> sqlite header may be in inconsistent or otherwise incorrect state?

The SQLite header is less than 100 bytes long.  It all fits within one sector / page of a storage device i.e. the entire header is written in one operation.  If you ever discover an inconsistent header there's a bug in SQLite.  The contents of the header are listed in

<https://sqlite.org/fileformat.html#the_database_header>

> - Any recommended explanations or resources to understand how it may
> be possible to obtain the correct sqlite database information (such as
> WAL or other journal mode, actual database size, number of pages,
> page/cache size, etc.)?

PRAGMAs are available for retrieving all this information.  See

<https://sqlite.org/pragma.html#pragma_journal_mode>
<https://sqlite.org/pragma.html#pragma_page_count>
<https://sqlite.org/pragma.html#pragma_page_size>

The size of the database is page_count * page_size.  Other PRAGMAs on the same page provide other information which might be covered in your "etc.".

If you have any other questions, please do not hesitate to ask them here.

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: More sqlite header questions

Chris Brody
On Tue, Feb 13, 2018 at 2:03 PM, Simon Slavin <[hidden email]> wrote:
> [...]
> There are two possibilities:
>
> A) The SQLite API was used correctly, including being allowed to close all files it opened.
> B) Any other situation.
>
> If (A) happened, you can predict things about the database header and you can rely on SQLite documentation about the format of files it uses.  Otherwise you can't.

Thanks Simon for the quick response. Can you clarify the following:
- Does this imply that a SQLite database may be left in some kind of
unrecoverable, corrupted, or otherwise invalid state in case an
application would terminate without calling sqlite3_close() on all
open database connections?
- If yes, what can a programmer do to protect the data in case an
application is abruptly terminated for any reason?
- Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
mitigate this kind of possible corruption?

I think this is especially important for mobile apps which may be
terminated without notice, especially when using hybrid app frameworks
such as Cordova/PhoneGap.

> However, the SQLite library goes through heroic measures
> [...]

I am sure that this was at the cost of many heroic programmer hours.

> The SQLite header is less than 100 bytes long.  It all fits within one sector / page of a storage device i.e. the entire header is written in one operation.  If you ever discover an inconsistent header there's a bug in SQLite.  The contents of the header are listed in
>
> <https://sqlite.org/fileformat.html#the_database_header>

Thanks for the clarification.

>> - Any recommended explanations or resources to understand how it may
>> be possible to obtain the correct sqlite database information (such as
>> WAL or other journal mode, actual database size, number of pages,
>> page/cache size, etc.)?
>
> PRAGMAs are available for retrieving all this information.  See
>
> <https://sqlite.org/pragma.html#pragma_journal_mode>
> <https://sqlite.org/pragma.html#pragma_page_count>
> <https://sqlite.org/pragma.html#pragma_page_size>
>
> The size of the database is page_count * page_size.  Other PRAGMAs on the same page provide other information which might be covered in your "etc.".

Makes sense ... assuming that the database is not corrupted beyond the
heroic repair mechanism:)

> If you have any other questions, please do not hesitate to ask them here.

Will do. Looking forward to the requested clarification. Thanks for
the answers so far.
_______________________________________________
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: More sqlite header questions

Richard Hipp-3
On 2/13/18, Chris Brody <[hidden email]> wrote:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

No.  The database might be left in a weird state, but it will
automatically recover the next time you or any other process opens the
database.

Unless, that is, you try to get clever and delete the *-journal or
*-wal file or do something else that interferes with the automatic
recovery process.  If you delete the information that SQLite needs to
recover then it won't, and the database will be left in its weird
state, which is now considered corrupt.

Do not do any of the bad things listed under
https://www.sqlite.org/howtocorrupt.html and you will be fine.

--
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: More sqlite header questions

Simon Slavin-3
In reply to this post by Chris Brody
On 13 Feb 2018, at 8:22pm, Chris Brody <[hidden email]> wrote:

> Thanks Simon for the quick response.

You're welcome.

> Can you clarify the following:
> - Does this imply that a SQLite database may be left in some kind of
> unrecoverable, corrupted, or otherwise invalid state in case an
> application would terminate without calling sqlite3_close() on all
> open database connections?

Your program is meant to do one of two things:

A) Close all database connections.
B) Call sqlite3_shutdown(), which will close everything and release all memory in a correct and orderly fashion.

<https://sqlite.org/c3ref/initialize.html>

If you do not do either of these things, it /might/ be possible to find that your database files are left in an inconsistent state.  But even if it does happen, the next time the database is opened using the SQLite API, SQLite will figure out what happened and restore the database to valid state.

Rather than unexpected termination you should be more worried about a program crashing or losing power in the middle of a SQLite call.  But SQLite was written to cope with this, too.

> - If yes, what can a programmer do to protect the data in case an
> application is abruptly terminated for any reason?

If there was anything, the fix would already be built into SQLite, or it would be prominently listed in the "how to use SQLite" pages.

If you think you have a corrupted database, reopen it using the SQLite API, then (after using it if you want) close it properly.  This is always the answer unless you want to forensically investigate the cause of corruption.

> - Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
> mitigate this kind of possible corruption?

I'm gonna let one of the developer team answer this.  I suspect that the answer depends on your operating system, and your storage device and its driver.

Durability is the foe of execution time.  It would be possible to make SQLite one third as subject to corruption -- at the cost of every command that reads or writes the database taking nine times as long.

> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

No mobile OS I'm aware of will allow termination of a program while it's in the middle of a SQLite API call, unless some other part of the application is hung and refusing to terminate.  This is part of the design of mobile operating systems which are designed to expect unpredictable backgrounding and termination.

I can go into great detail about how iOS warns a program about backgrounding and termination, so that it can close in a graceful manner.  I assume Android does something similar.

>> However, the SQLite library goes through heroic measures
>> [...]
>
> I am sure that this was at the cost of many heroic programmer hours.

Not to mention the proportion of SQLite's source code which is devoted to detecting and fixing corruption rather than doing mundane database work.

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: More sqlite header questions

Jens Alfke-2
In reply to this post by Chris Brody


> On Feb 13, 2018, at 12:22 PM, Chris Brody <[hidden email]> wrote:
>
> I think this is especially important for mobile apps which may be
> terminated without notice, especially when using hybrid app frameworks
> such as Cordova/PhoneGap.

On iOS (can’t speak for Android) apps do get notice that they’re going to be terminated. That’s no different for hybrid frameworks; the JS code must be calling a SQLite API backed by native code, and that native code can listen for the appropriate notifications and close databases.

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

Re: More sqlite header questions

Richard Hipp-3
On 2/13/18, Jens Alfke <[hidden email]> wrote:
>
> On iOS (can’t speak for Android) apps do get notice that they’re going to be
> terminated.

I am told that that notification is best-effort and is not guaranteed
to occur, nor is it guaranteed to be delivered to the application
prior to the application being forcibly terminated.
--
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: More sqlite header questions

Simon Slavin-3
On 13 Feb 2018, at 9:17pm, Richard Hipp <[hidden email]> wrote:

> On 2/13/18, Jens Alfke <[hidden email]> wrote:
>
>> On iOS (can’t speak for Android) apps do get notice that they’re going to be
>> terminated.
>
> I am told that that notification is best-effort and is not guaranteed
> to occur, nor is it guaranteed to be delivered to the application
> prior to the application being forcibly terminated.

The precise way this is done has changed from iOS version 6 to iOS version 10.  The documentation states (and has always stated) that your app will get a backgrounding notification and a termination notification where possible.

One problem comes if your application does not acknowledge a backgrounding notification.  If this happens, then your application may not later get notified that it is being terminated.  So the protocol breaks down once either party isn't following it.

Another problem occurs if the phone receives a call at a time when the battery is running low.  Low battery means that the phone has little power to devote to background processing, since by law handling the phone call takes priority.  This is probably the situation that Apple was talking about in Dr Hipp's post.

The most obvious problem happens if the phone runs out of power.  However current versions of iOS shut the device down (giving appropriate notifications) before the battery is truly out of power.  So they're not in much hurry.

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: More sqlite header questions

Dan Kennedy-4
In reply to this post by Simon Slavin-3
On 02/14/2018 04:03 AM, Simon Slavin wrote:

> On 13 Feb 2018, at 8:22pm, Chris Brody <[hidden email]> wrote:
>
>> Thanks Simon for the quick response.
> You're welcome.
>
>> Can you clarify the following:
>> - Does this imply that a SQLite database may be left in some kind of
>> unrecoverable, corrupted, or otherwise invalid state in case an
>> application would terminate without calling sqlite3_close() on all
>> open database connections?
> Your program is meant to do one of two things:
>
> A) Close all database connections.
> B) Call sqlite3_shutdown(), which will close everything and release all memory in a correct and orderly fashion.


(B) is an understandably common misconception. sqlite3_shutdown() frees
resources that were allocated by sqlite3_initialize() or
sqlite3_auto_extension() and must be called after all SQLite connections
have been closed. These resources are either trivial or non-existent on
almost all platforms - so in practice sqlite3_shutdown() is only useful
on embedded systems that do not free such resources automatically when a
process exits, or in other obscure circumstances.

Don't call sqlite3_shutdown()!

   http://sqlite.org/c3ref/initialize.html

Dan.



>
> <https://sqlite.org/c3ref/initialize.html>
>
> If you do not do either of these things, it /might/ be possible to find that your database files are left in an inconsistent state.  But even if it does happen, the next time the database is opened using the SQLite API, SQLite will figure out what happened and restore the database to valid state.
>
> Rather than unexpected termination you should be more worried about a program crashing or losing power in the middle of a SQLite call.  But SQLite was written to cope with this, too.
>
>> - If yes, what can a programmer do to protect the data in case an
>> application is abruptly terminated for any reason?
> If there was anything, the fix would already be built into SQLite, or it would be prominently listed in the "how to use SQLite" pages.
>
> If you think you have a corrupted database, reopen it using the SQLite API, then (after using it if you want) close it properly.  This is always the answer unless you want to forensically investigate the cause of corruption.
>
>> - Would using SQLITE_DEFAULT_SYNCHRONOUS=3 (extra durable) help
>> mitigate this kind of possible corruption?
> I'm gonna let one of the developer team answer this.  I suspect that the answer depends on your operating system, and your storage device and its driver.
>
> Durability is the foe of execution time.  It would be possible to make SQLite one third as subject to corruption -- at the cost of every command that reads or writes the database taking nine times as long.
>
>> I think this is especially important for mobile apps which may be
>> terminated without notice, especially when using hybrid app frameworks
>> such as Cordova/PhoneGap.
> No mobile OS I'm aware of will allow termination of a program while it's in the middle of a SQLite API call, unless some other part of the application is hung and refusing to terminate.  This is part of the design of mobile operating systems which are designed to expect unpredictable backgrounding and termination.
>
> I can go into great detail about how iOS warns a program about backgrounding and termination, so that it can close in a graceful manner.  I assume Android does something similar.
>
>>> However, the SQLite library goes through heroic measures
>>> [...]
>> I am sure that this was at the cost of many heroic programmer hours.
> Not to mention the proportion of SQLite's source code which is devoted to detecting and fixing corruption rather than doing mundane database work.
>
> 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: More sqlite header questions

Simon Slavin-3
On 15 Feb 2018, at 5:30pm, Dan Kennedy <[hidden email]> wrote:

> (B) is an understandably common misconception. sqlite3_shutdown() frees resources that were allocated by sqlite3_initialize() or sqlite3_auto_extension() and must be called after all SQLite connections have been closed. These resources are either trivial or non-existent on almost all platforms - so in practice sqlite3_shutdown() is only useful on embedded systems that do not free such resources automatically when a process exits, or in other obscure circumstances.
>
> Don't call sqlite3_shutdown()!

Thanks for your informative correction.  Is there a chance of amending the documentation with the above text ?  I have two problems with the existing documentation:

1) I can't be the only programmer who learned to make paired calls ("If you initialise something, it needs deinitializing, if you allocate something, deallocate it.").

2) I thought that sqlite3_shutdown() called sqlite3_close() on any open connections.  In other words, that if keeping track of your connections was difficult it might be simpler to just call sqlite3_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: More sqlite header questions

Jens Alfke-2


> On Feb 15, 2018, at 10:09 AM, Simon Slavin <[hidden email]> wrote:
>
> 1) I can't be the only programmer who learned to make paired calls ("If you initialise something, it needs deinitializing, if you allocate something, deallocate it.").

When a process exits, its resources are automatically cleaned up by the kernel — memory and address space freed, file handles and sockets closed, etc. So it’s generally unnecessary to do redundant cleanup like sqlite3_shutdown* on quit, and it can actually slow things down**, especially if freeing a zillion heap blocks that have been paged out or are no longer in CPU caches.

—Jens

* This doesn’t apply to sqlite3_close() on a writeable database, since SQLite may need to update data in the file before closing it.
**  Case in point: One of the optimizations added to MacOS about five years ago was ‘fast termination’, wherein quitting an app will terminate it instantly with a simple call to exit(0) if the app code doesn’t have any special cleanup it needs to do.
_______________________________________________
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: More sqlite header questions

Simon Slavin-3
On 15 Feb 2018, at 11:10pm, Jens Alfke <[hidden email]> wrote:

>> On Feb 15, 2018, at 10:09 AM, Simon Slavin <[hidden email]> wrote:
>>
>>> 1) I can't be the only programmer who learned to make paired calls ("If you initialise something, it needs deinitializing, if you allocate something, deallocate it.").
>
> When a process exits, its resources are automatically cleaned up by the kernel — memory and address space freed, file handles and sockets closed, etc. So it’s generally unnecessary to do redundant cleanup like sqlite3_shutdown* on quit, and it can actually slow things down**, especially if freeing a zillion heap blocks that have been paged out or are no longer in CPU caches.
>
> —Jens
>
> * This doesn’t apply to sqlite3_close() on a writeable database, since SQLite may need to update data in the file before closing it.

Your footnote is an example of the reason I do paired calls.  I didn't write the SQLite API.  I don't know whether sqlite3_shutdown() does anything.  For all I know, sqlite3_shutdown() is the thing that deletes dead journal and shared memory files.  Maybe it slows things down too much to do that on sqlite3_close().  I don't know.

Given the way the documentation is arranged, and the lack of any mention to the contrary,  I will assume sqlite3_shutdown() is paired with sqlite_initialize().  One of the Developer team tells me it'd not needed except for cases where your process isn't about to quit ?  Fine.  But that should really be in the documentation so people who don't read this list know it.  Because the name sqlite3_shutdown(), and the documentation for it, implies you should call it when sqlite3_initialize() has been called and you don't need SQLite any more.

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: More sqlite header questions

Jens Alfke-2


> On Feb 15, 2018, at 4:53 PM, Simon Slavin <[hidden email]> wrote:
>
> Given the way the documentation is arranged, and the lack of any mention to the contrary,  I will assume sqlite3_shutdown() is paired with sqlite_initialize().

There’s a sqlite_initialize function?? O_o

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

Re: More sqlite header questions

Simon Slavin-3


On 16 Feb 2018, at 1:39am, Jens Alfke <[hidden email]> wrote:

> On Feb 15, 2018, at 4:53 PM, Simon Slavin <[hidden email]> wrote:
>
>> Given the way the documentation is arranged, and the lack of any mention to the contrary,  I will assume sqlite3_shutdown() is paired with sqlite_initialize().
>
> There’s a sqlite_initialize function?? O_o

<https://sqlite.org/c3ref/initialize.html>

However, if you open a connection and you haven't already called it, it's called automatically.  So almost nobody cares about it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users