Apparent power fail data loss in embedded use - SQLite newbie

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

Apparent power fail data loss in embedded use - SQLite newbie

ted.goldblatt
I apologize in advance for the length of the following…

I have recently been handed a customer-reported problem against a legacy embedded device (an industrial process monitor) that makes use of SQLite over flash for storing the monitored info (it uses this internally to respond to remote info requests – there is no “user” access to the database).  The device runs dedicated real-time software with SQLite embedded.  To make life fun, no one left here knows the device’s software, there doesn’t appear to be any design documentation, and I am not a database person and know almost nothing about SQLite (besides what I’ve learned in the last week).

The problem is basically that as part of a test, the customer wants to power fail the device, and then to be able to recover (after the device restarts) data that was generated/stored as close to the power failure as possible.  (There are some valid reasons for this test.)  For the purposes of this test, the device can’t have any battery backup, and it doesn’t get any early warning of the failure – it just dies.  Unfortunately, when doing this, it appears that significant amounts of data that should be in the database are missing and/or corrupted, and this doesn’t appear particularly dependent on when the failure occurs.  The overall database isn’t corrupted, however.  For control tests – with no power failure – running for the same elapsed time, 8 or 10 “batches” of data captures are recorded, while for the tests with the power failures there are only 2 batches and the (apparent) second one is odd or worse (most of the fields are 0 or invalid, and the timestamp column entries (while reasonable)are in the past from when the test occurred.

Areas of possible relevance I’ve noted so far include:

Using a very old version of SQLite (3.7.4, from 2010).  The software was apparently being updated/supported by the original developer until a year or 2 ago and I have no idea why it was never updated to a newer version (that is, whether it was just inertia or if there was an active reason not to update).
The update batches are wrapped in transaction boundaries, though I don’t know if this is done (or configured for) correctly.
SQLite is configured to use “temp files always in memory”, which is suspicious for a power fail problem, but I don’t know what the temp files are used for.
The underlying real-time OS appears very simple, and I don’t believe it does any buffering or other special IO handling itself (I think it just acts as a router to the appropriate driver).  However, I haven’t yet determined if the flash drivers, etc. that were written for this device might do so.
While the required database update rate seems low (even for the quite modest hardware involved), there are several references to things (related to the database) that are done for “performance” reasons, and while I haven’t traced them down yet, this could be leaving things in memory longer than desirable.
So – some questions:

While there have been all kinds of bug fixes (and perhaps improvements in transaction resiliency and such) in SQLite since that version, is it likely that I am running into things that have been fixed relative to power fail resilience?
Regardless, I’d like to pick up the current version of SQLite – I am uncomfortable using such an old version.  Any idea of the level of risk I would run to make that big a leap at once?  Aside from any possible dependence on behavior that was “broken”, any incompatibilities (interfaces, intended behavior, file formats) that aren’t automatically/transparently handled would be a problem, as the updated software load would need to be able to be dropped into existing fielded systems with no other user intervention, and there aren’t resources (meaning me) to make more than minor code changes for that.  For what it’s worth, I believe the current database usage is pretty simple – nothing besides creates, inserts/updates, deletes, and simple selects.  The other concern might be if the code size increased substantially – the available code space is limited.
Might the memory only “temp files” be an issue?  What does (did) SQLite use those for?  If the transaction/rollback file or similar, it could obviously be an issue, but that doesn’t make sense to me.
Are there any specific configuration settings or usage that I should look at (again, taking into account this is a very old version)?
Anything else that comes to mind?  (Obviously, doing the update first if it is perceived as pretty safe would at least make any of the remaining issues relative to things as they exist now.  But as noted, there are no resources to deal with anything beyond minor - and easy to find/fix - incompatibilities.)
Thanks much for any feedback!

Ted

_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

James K. Lowden
On Tue, 12 Mar 2019 10:36:37 -0400
[hidden email] wrote:

> The problem is basically that as part of a test, the customer wants
> to power fail the device, and then to be able to recover (after the
> device restarts) data that was generated/stored as close to the power
> failure as possible.  
...
> Unfortunately, when doing this, it appears that significant amounts
> of data that should be in the database are missing and/or corrupted,
> and this doesn?t appear particularly dependent on when the failure
> occurs.  

Only 3 possibilities exist:

1.  SQLite finished its transaction before the power cut, and the data
were committed and do appear in the database later.  

2.  SQLite did not finish its transaction, and the transaction was
rolled back as part of database initialization after power was
restored.  

3.  The hardware or driver reported the data were written when they
were not.  

We can dismiss as statistically insignificant possibility #4, a bug in
SQLite, because of SQLite's excellent testing regimen and gigantic user
base.  

If you can confirm that SQLite finished the transaction whose data the
database does not reflect on restart, you really must suspect the
driver or device.  I don't know much about USB drives, but consumer
grade hard drives *normally* lie about committed data for performance
reasons.  "It's easy to make it fast if it doesn't have to be right."
USB devices face at least as much temptation to misrepresent their
performance.  

--jkl
_______________________________________________
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: [EXTERNAL] Apparent power fail data loss in embedded use - SQLite newbie

Hick Gunter
In reply to this post by ted.goldblatt
Without gaining SQLite shell access to the actual file (e.g. copy from flash to PC/Linux/whatever) or some other possibility to run integrity checks, this is going to be hard.

With an embedded device, not upgrading SQLite is probably due to not wanting to increase image size. Newer releases have more features and may require defining more OMIT macros for the custom build to remain small. If SQLite still accepts the database after a hard powerfail, then the custom vfs layer seems to be working well enough.

I suspect the application may be creating a set of "empty" records (to make sure there is enough space in the file/flash memory) in an "initial" transaction and performing some kind of batch update to write the data when "enough" has been collected. Or possibly logging to temp tables (in memory) for speed and batch copying to persistent tables periodically. The TEMP database is automatically deleted when the connection is closed.

Having upgraded from 3.5.9 -> 3.7.4 -> 3.7.14.1 and recently 3.24 with only minor problems related te extensive use of virtual tables, I think "don't need to" was the main reason for staying on an old version.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Dienstag, 12. März 2019 15:37
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

I apologize in advance for the length of the following…

I have recently been handed a customer-reported problem against a legacy embedded device (an industrial process monitor) that makes use of SQLite over flash for storing the monitored info (it uses this internally to respond to remote info requests – there is no “user” access to the database).  The device runs dedicated real-time software with SQLite embedded.  To make life fun, no one left here knows the device’s software, there doesn’t appear to be any design documentation, and I am not a database person and know almost nothing about SQLite (besides what I’ve learned in the last week).

The problem is basically that as part of a test, the customer wants to power fail the device, and then to be able to recover (after the device restarts) data that was generated/stored as close to the power failure as possible.  (There are some valid reasons for this test.)  For the purposes of this test, the device can’t have any battery backup, and it doesn’t get any early warning of the failure – it just dies.  Unfortunately, when doing this, it appears that significant amounts of data that should be in the database are missing and/or corrupted, and this doesn’t appear particularly dependent on when the failure occurs.  The overall database isn’t corrupted, however.  For control tests – with no power failure – running for the same elapsed time, 8 or 10 “batches” of data captures are recorded, while for the tests with the power failures there are only 2 batches and the (apparent) second one is odd or worse (most of the fields are 0 or invalid, and the timestamp column entries (while reasonable)are in the past from when the test occurred.

Areas of possible relevance I’ve noted so far include:

Using a very old version of SQLite (3.7.4, from 2010).  The software was apparently being updated/supported by the original developer until a year or 2 ago and I have no idea why it was never updated to a newer version (that is, whether it was just inertia or if there was an active reason not to update).
The update batches are wrapped in transaction boundaries, though I don’t know if this is done (or configured for) correctly.
SQLite is configured to use “temp files always in memory”, which is suspicious for a power fail problem, but I don’t know what the temp files are used for.
The underlying real-time OS appears very simple, and I don’t believe it does any buffering or other special IO handling itself (I think it just acts as a router to the appropriate driver).  However, I haven’t yet determined if the flash drivers, etc. that were written for this device might do so.
While the required database update rate seems low (even for the quite modest hardware involved), there are several references to things (related to the database) that are done for “performance” reasons, and while I haven’t traced them down yet, this could be leaving things in memory longer than desirable.
So – some questions:

While there have been all kinds of bug fixes (and perhaps improvements in transaction resiliency and such) in SQLite since that version, is it likely that I am running into things that have been fixed relative to power fail resilience?
Regardless, I’d like to pick up the current version of SQLite – I am uncomfortable using such an old version.  Any idea of the level of risk I would run to make that big a leap at once?  Aside from any possible dependence on behavior that was “broken”, any incompatibilities (interfaces, intended behavior, file formats) that aren’t automatically/transparently handled would be a problem, as the updated software load would need to be able to be dropped into existing fielded systems with no other user intervention, and there aren’t resources (meaning me) to make more than minor code changes for that.  For what it’s worth, I believe the current database usage is pretty simple – nothing besides creates, inserts/updates, deletes, and simple selects.  The other concern might be if the code size increased substantially – the available code space is limited.
Might the memory only “temp files” be an issue?  What does (did) SQLite use those for?  If the transaction/rollback file or similar, it could obviously be an issue, but that doesn’t make sense to me.
Are there any specific configuration settings or usage that I should look at (again, taking into account this is a very old version)?
Anything else that comes to mind?  (Obviously, doing the update first if it is perceived as pretty safe would at least make any of the remaining issues relative to things as they exist now.  But as noted, there are no resources to deal with anything beyond minor - and easy to find/fix - incompatibilities.) Thanks much for any feedback!

Ted

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

Simon Slavin-3
In reply to this post by ted.goldblatt
Other posters have taken care of very important aspects of your circumstances, but I wanted to mention one I didn't see anyone mention.  Settings.

If you compile SQLite without changing compilation settings, and use it without changing defaults, SQLite is extremely good at avoiding corruption, and at recovering after corruption.  This includes corruption due to power-loss at any stage while changes are being made to the database.

However, settings can be made which improve SQLite for some specific uses.  They make it faster.  Or use less memory.  Or use less filespace while working.  Unfortunately some of them all sacrifice harness against corruption.

These settings can be made at three (or more ?  not sure) different places:

1) Compilation settings when the SQLite API is compiled
2) Extra parameters passed when the database is opened
3) PRAGMA settings made at any time while the database is open

To assess how 'hard' your use of SQLite is against corruption, you would have to track down whether any of the above three have been done.

1) May or may not be easy.  Do you know how SQLite is included in your project ?  Is it part of a library downloaded from somewhere or did your programmer compile it themself ?  If the former, you can assume that whoever prepared the library didn't mess with default settings.  If the latter, can you track down the compilation settings they used ?

2) Do you have the source code for your project ?  Can you find all places where a database is opened ?  If it uses the SQLite API directly you can just search for "sqlite3_open".  See whether you can spot whether anything except file name & path are passed.

3) Do you have the source code for your project ?  Can you do a global search for "PRAGMA" ?  Only a few of the PRAGMAs reduce integrity.  Most of them are fine.  But you can look them up and see for yourself.

The whole of the above is merely me being picky.  Millions of SQLite users just leave all settings at their defaults.  But it seems to be the sort of thing you're asking about.

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: Apparent power fail data loss in embedded use - SQLite newbie

Simon Slavin-3
I may have missed this already being discussed.

Will you have access to a copy of the database as it was before corruption testing ?  Can you use SQLite to see whether it is already corrupt ?  Or can the test run on a brand new, freshly-created database ?  If neither of those, your test won't be fair.

Corruption in databases spreads.  One incorrect pointer can lead to new data being lost.  An incorrect row-length can lead to changes being made to the wrong part of the file, overwriting data which shouldn't be changed.

SQLite will continue to work with a corrupt database if it never notices it's corrupt.  But without the above test process there's no reason for it to suddenly get paranoid about every piece of data it finds.

SQLite includes a PRAGMA command which tests the database /fairly/ thoroughly looking for such problems.  This is often included as part of a power-on test, or a weekly/monthly/yearly maintenance procedure.  Without this test, it's possible that the SQLite database file has been corrupt for years.  Or you can run it yourself any time if you can copy the database to your own computer.

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: Apparent power fail data loss in embedded use - SQLite newbie

ted.goldblatt
In reply to this post by James K. Lowden
On Tue, Mar 12, 2019 at 11:45 AM James K. Lowden <[hidden email]>
wrote:

> On Tue, 12 Mar 2019 10:36:37 -0400
> [hidden email] wrote:
>
> > The problem is basically that as part of a test, the customer wants
> > to power fail the device, and then to be able to recover (after the
> > device restarts) data that was generated/stored as close to the power
> > failure as possible.
> ...
> > Unfortunately, when doing this, it appears that significant amounts
> > of data that should be in the database are missing and/or corrupted,
> > and this doesn?t appear particularly dependent on when the failure
> > occurs.
>
> Only 3 possibilities exist:
>
> 1.  SQLite finished its transaction before the power cut, and the data
> were committed and do appear in the database later.
>
This is not the case

>
> 2.  SQLite did not finish its transaction, and the transaction was
> rolled back as part of database initialization after power was
> restored.

This is also not the case

>
> 3.  The hardware or driver reported the data were written when they
> were not.
>
This *may* be the case

>
> We can dismiss as statistically insignificant possibility #4, a bug in
> SQLite, because of SQLite's excellent testing regimen and gigantic user
> base.

I have been writing software for too many decades to casually dismiss the
possibilities of software bugs.  If there couldn't be bugs in SQLite, there
would have been no bug fixes since the version being used here, and having
briefly perused the revision history, it is obvious that isn't the case.
Further, different users run in different environments and do different
things, both of which can shake loose bugs or unexpected behaviors.  That
said, I don't think what is going on here is a "oops" type bug, though it
could be an interaction (or configuration) issue - possibly something that
could have been addressed in one way or other over the last 8 years, hence
my question.

>
>
> If you can confirm that SQLite finished the transaction whose data the
> database does not reflect on restart, you really must suspect the
> driver or device.  I don't know much about USB drives, but consumer
> grade hard drives *normally* lie about committed data for performance
> reasons.  "It's easy to make it fast if it doesn't have to be right."
> USB devices face at least as much temptation to misrepresent their
> performance.
>
I can confirm the state of the database after restart.  As far as the
backing device - this is an industrial device.  The storage is not USB,
consumer grade, or a hard drive.  It is NAND flash parts soldered to the PC
board and directly addressed by the (locally written) low-level device
driver.  I can assure you that there was no one to be impressed by any
performance numbers - that isn't an issue.  What may be an issue, however,
is the (real-time OS supplied) file system driver, which *could* do
buffering and *might *not honor sync() properly.  I am currently looking
into that.

>
>
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

Jim Morris
What is the journal mode?

On 3/12/2019 10:30 AM, Ted Goldblatt wrote:

> On Tue, Mar 12, 2019 at 11:45 AM James K. Lowden <[hidden email]>
> wrote:
>
>> On Tue, 12 Mar 2019 10:36:37 -0400
>> [hidden email] wrote:
>>
>>> The problem is basically that as part of a test, the customer wants
>>> to power fail the device, and then to be able to recover (after the
>>> device restarts) data that was generated/stored as close to the power
>>> failure as possible.
>> ...
>>> Unfortunately, when doing this, it appears that significant amounts
>>> of data that should be in the database are missing and/or corrupted,
>>> and this doesn?t appear particularly dependent on when the failure
>>> occurs.
>> Only 3 possibilities exist:
>>
>> 1.  SQLite finished its transaction before the power cut, and the data
>> were committed and do appear in the database later.
>>
> This is not the case
>
>> 2.  SQLite did not finish its transaction, and the transaction was
>> rolled back as part of database initialization after power was
>> restored.
> This is also not the case
>
>> 3.  The hardware or driver reported the data were written when they
>> were not.
>>
> This *may* be the case
>
>> We can dismiss as statistically insignificant possibility #4, a bug in
>> SQLite, because of SQLite's excellent testing regimen and gigantic user
>> base.
> I have been writing software for too many decades to casually dismiss the
> possibilities of software bugs.  If there couldn't be bugs in SQLite, there
> would have been no bug fixes since the version being used here, and having
> briefly perused the revision history, it is obvious that isn't the case.
> Further, different users run in different environments and do different
> things, both of which can shake loose bugs or unexpected behaviors.  That
> said, I don't think what is going on here is a "oops" type bug, though it
> could be an interaction (or configuration) issue - possibly something that
> could have been addressed in one way or other over the last 8 years, hence
> my question.
>
>>
>> If you can confirm that SQLite finished the transaction whose data the
>> database does not reflect on restart, you really must suspect the
>> driver or device.  I don't know much about USB drives, but consumer
>> grade hard drives *normally* lie about committed data for performance
>> reasons.  "It's easy to make it fast if it doesn't have to be right."
>> USB devices face at least as much temptation to misrepresent their
>> performance.
>>
> I can confirm the state of the database after restart.  As far as the
> backing device - this is an industrial device.  The storage is not USB,
> consumer grade, or a hard drive.  It is NAND flash parts soldered to the PC
> board and directly addressed by the (locally written) low-level device
> driver.  I can assure you that there was no one to be impressed by any
> performance numbers - that isn't an issue.  What may be an issue, however,
> is the (real-time OS supplied) file system driver, which *could* do
> buffering and *might *not honor sync() properly.  I am currently looking
> into that.
>
>>
> _______________________________________________
> 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: Apparent power fail data loss in embedded use - SQLite newbie

David Raymond
In reply to this post by ted.goldblatt
Info on the use of temp files can be found here
https://www.sqlite.org/tempfiles.html

Anything integral to integrity is always put on disk, so SQLITE_TEMP_STORE shouldn't be affecting integrity.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of [hidden email]
Sent: Tuesday, March 12, 2019 10:37 AM
To: [hidden email]
Subject: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

...
SQLite is configured to use “temp files always in memory”, which is suspicious for a power fail problem, but I don’t know what the temp files are used for.
...
Might the memory only “temp files” be an issue?  What does (did) SQLite use those for?  If the transaction/rollback file or similar, it could obviously be an issue, but that doesn’t make sense to me.
...

Ted

_______________________________________________
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: [EXTERNAL] Apparent power fail data loss in embedded use - SQLite newbie

ted.goldblatt
In reply to this post by Hick Gunter
On Tue, Mar 12, 2019 at 11:47 AM Hick Gunter <[hidden email]> wrote:

> Without gaining SQLite shell access to the actual file (e.g. copy from
> flash to PC/Linux/whatever) or some other possibility to run integrity
> checks, this is going to be hard.
>
I do have this - the firmware is able to upload a copy of the raw database
(at least, I believe it is untouched), and I have been able to run SQLite
tools against it on Windows.   This is how I know the state of the doubtful
entries - the "normal" data access mechanisms used by the device and its
management software (MODBUS requests to a defined register map) don't show
the "bad" entries at all.

>
> With an embedded device, not upgrading SQLite is probably due to not
> wanting to increase image size. Newer releases have more features and may
> require defining more OMIT macros for the custom build to remain small. If
> SQLite still accepts the database after a hard powerfail, then the custom
> vfs layer seems to be working well enough.
>
It is likely a combination of desire not to increase the ROM footprint and
a lack of time to deal with it (I'm sure this was one of half a dozen
devices the engineer was supporting along with new development.  In that
case, you generally don't give yourself additional work if there is no
demonstrated need to.)

>
> I suspect the application may be creating a set of "empty" records (to
> make sure there is enough space in the file/flash memory) in an "initial"
> transaction and performing some kind of batch update to write the data when
> "enough" has been collected. Or possibly logging to temp tables (in memory)
> for speed and batch copying to persistent tables periodically. The TEMP
> database is automatically deleted when the connection is closed.
>
This is possible (and I will look for that), but I haven't seen such yet.
Unfortunately, the code is a pile of C++ abstractions that make it harder
for me to follow than I'd like.

>
> Having upgraded from 3.5.9 -> 3.7.4 -> 3.7.14.1 and recently 3.24 with
> only minor problems related te extensive use of virtual tables, I think
> "don't need to" was the main reason for staying on an old version.
>
> Given your lack of issues doing the upgrade, I will look into possibly
doing it to just be more current even if there is no expectation it will
help with this specific issue.

Ted
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

Warren Young
In reply to this post by ted.goldblatt
On Mar 12, 2019, at 11:30 AM, Ted Goldblatt <[hidden email]> wrote:
>
> I have been writing software for too many decades to casually dismiss the
> possibilities of software bugs.  If there couldn't be bugs in SQLite, there
> would have been no bug fixes since the version being used here, and having
> briefly perused the revision history, it is obvious that isn't the case.

All true, but which is more likely: a bug in code you, your coworkers, and your niche suppliers wrote, or that which has been in continuous development for 15 years, used by millions of developers, and by billions of end users?

(15 years counts from SQLite 3.0.0.  Prior major versions were built atop differing core technology.)

I’d expect bugs in SQLite to be about as rare as bugs in top-tier language compilers for the most popular programming languages.  From your stated experience, you should know by now how rarely correct the claim “Compiler bug!” is.

> Further, different users run in different environments and do different
> things, both of which can shake loose bugs or unexpected behaviors.

Also true, but consider those millions of SQLite developers: they’ve tried configurations you’ve never even thought about.

The inverse is also true: maybe you’ve managed to come up with a configuration that no one else has tried, or at least reported on.  But statistically, the chances of that being true are really low.  “When you hear hoofbeats, think horses, not zebras.”

> It is NAND flash parts soldered to the PC board and directly addressed by the (locally written) low-level device driver. I can assure you that there was no one to be impressed by any performance numbers

Why then does the first page of a data sheet usually give specs and other claims that get whittled away by subsequent pages?

I think the marketing departments of every silicon vendor on the planet would disagree that there’s no need to be impressing their customers with performance numbers.

I’m reminded of an op-amp I once used that gave its headline specs in unity gain terms on the first page, but on page 9 they showed a graph that guaranteed that it’d take off into oscillation if presented an input signal around about 40 MHz with unity gain.  And it did!  The circuit I used it in wasn’t anywhere near that wide in bandwidth, but ambient RFI doesn’t care what bandwidth I intended the circuit to accept.

I just checked the current version of that data sheet, and it’s still making the same dodgy claim.  And the chip is from a top-tier US-based vendor.

> What may be an issue, however, is the (real-time OS supplied) file system driver, which *could* do buffering and *might *not honor sync() properly.

Quite possible.  It’s not just hardware that lies about fsync().
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

ted.goldblatt
In reply to this post by Simon Slavin-3
On Tue, Mar 12, 2019 at 12:17 PM Simon Slavin <[hidden email]> wrote:

> Other posters have taken care of very important aspects of your
> circumstances, but I wanted to mention one I didn't see anyone mention.
> Settings.
>
> If you compile SQLite without changing compilation settings, and use it
> without changing defaults, SQLite is extremely good at avoiding corruption,
> and at recovering after corruption.  This includes corruption due to
> power-loss at any stage while changes are being made to the database.
>
> However, settings can be made which improve SQLite for some specific
> uses.  They make it faster.  Or use less memory.  Or use less filespace
> while working.  Unfortunately some of them all sacrifice harness against
> corruption.
>
> These settings can be made at three (or more ?  not sure) different places:
>
> 1) Compilation settings when the SQLite API is compiled
> 2) Extra parameters passed when the database is opened
> 3) PRAGMA settings made at any time while the database is open
>
> To assess how 'hard' your use of SQLite is against corruption, you would
> have to track down whether any of the above three have been done.
>
> 1) May or may not be easy.  Do you know how SQLite is included in your
> project ?  Is it part of a library downloaded from somewhere or did your
> programmer compile it themself ?  If the former, you can assume that
> whoever prepared the library didn't mess with default settings.  If the
> latter, can you track down the compilation settings they used ?
>
SQLite is built from (the combined) source as part of the project build. I
don't see signs in the project options that any special (SQLite) settings
are used.  However, the interface is done through the CppSQLite3 interface
layer.  (There have been some local changes to CppSQLite3, but they appear
to be limited to adding error logging.)  There is also a local header file
(that is, one written as part of the project) with what appear to be SQLite
compilation options (SQLITE_OMIT_xxx, SQLITE_DEFAULT_CACHE_SIZE,
SQLITE_THREADSAFE, etc.  One of these is SQLITE_TEMP_STORE, which is set to
"Always use memory" which strikes me a suspicious relative to a power fail
problem.  However, I cannot find anything that seems to reference most of
these, and specifically not the TEMP_STORE define.  And this file is only
included by a project specific DB interface file that invokes CppSQLite3
methods but not by sqlite3.c, so I'm not sure of the point.

>
> 2) Do you have the source code for your project ?  Can you find all places
> where a database is opened ?  If it uses the SQLite API directly you can
> just search for "sqlite3_open".  See whether you can spot whether anything
> except file name & path are passed.
>
I have full sources.   The DB opens are directly by CppSQLite3 (which uses
sqlite_open_v2()), and all of those calls have OPEN_READONLY or
OPEN_READWRITE as the 3rd param and 0 as the 4th.  The CppSQLite3 methods
themselves take only a filename.

3) Do you have the source code for your project ?  Can you do a global
> search for "PRAGMA" ?  Only a few of the PRAGMAs reduce integrity.  Most of
> them are fine.  But you can look them up and see for yourself.
>
 It doesn't appear that any PRAGMAs appear outside the CppSQLite3_16.cpp
and sqlite3.h files.

>
> The whole of the above is merely me being picky.  Millions of SQLite users
> just leave all settings at their defaults.  But it seems to be the sort of
> thing you're asking about.
>
It is.  I am guessing that the problem isn't in SQLite, per se, but in the
local config or usage.  However, clues about where to look are always
welcome...
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

Simon Slavin-3
On 12 Mar 2019, at 9:41pm, Ted Goldblatt <[hidden email]> wrote:

> SQLITE_OMIT_xxx, SQLITE_DEFAULT_CACHE_SIZE,
> SQLITE_THREADSAFE

None of these are a problem, assuming your embedded app does not do multi-threading.

> One of these is SQLITE_TEMP_STORE, which is set to
> "Always use memory" which strikes me a suspicious relative to a power fail
> problem.

That's fine.  It just means that using memory is 'cheaper' to that embedded app than using backing store.  Losing power while temporary storage is in use won't suffer from that setting.

> The DB opens are directly by CppSQLite3 (which uses
> sqlite_open_v2()), and all of those calls have OPEN_READONLY or
> OPEN_READWRITE as the 3rd param and 0 as the 4th.  The CppSQLite3 methods
> themselves take only a filename.

Good.  No shared memory, or messing with caching, or disabling of locking.

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

> It doesn't appear that any PRAGMAs appear outside the CppSQLite3_16.cpp
> and sqlite3.h files.

It seems very unlikely that a general-purpose C++ wrapper would set any setting to increase the possibility of corruption.  So that's good too.

Okay, I clear you for all the above.  As far as I can tell, your software doesn't intentionally disable any of SQLite's safety measures.
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

ted.goldblatt
In reply to this post by Jim Morris
On Tue, Mar 12, 2019 at 1:55 PM Jim Morris <[hidden email]> wrote:

> What is the journal mode?
>
> That is a very interesting question, as a journal file certainly seems
relevant to power fail issues.  Unfortunately, you have exceeded the limits
of my knowledge of either SQLite or of the underlying software on this
device.  What are the mode choices and where would this be defined?  (Just
searching for journal found a lot of references in the OS's file system -
which I find disturbing - and some in SQLite and related files, but none
that were defines.)
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

ted.goldblatt
In reply to this post by Simon Slavin-3
On Tue, Mar 12, 2019 at 12:29 PM Simon Slavin <[hidden email]> wrote:

> I may have missed this already being discussed.
>
> Will you have access to a copy of the database as it was before corruption
> testing ?  Can you use SQLite to see whether it is already corrupt ?  Or
> can the test run on a brand new, freshly-created database ?  If neither of
> those, your test won't be fair.
>

We are able to repro this problem, so clean databases aren't a problem.
 At least the first time this was done in-house, it was on a brand new
database (not sure about all the subsequent tests - at least some were on
databases which had already been hit with a power failure).
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

Chris Brody
In reply to this post by ted.goldblatt
> SQLite is built from (the combined) source as part of the project build.
> [...]

If you want extra safety, I would recommend you consider using
SQLITE_DEFAULT_SYNCHRONOUS=3 which is the equivalent to using PRAGMA
synchronous=EXTRA.

Quick references:
* https://www.sqlite.org/pragma.html#pragma_synchronous
* https://www.sqlite.org/compile.html#default_synchronous

> What is the journal mode?
>
> That is a very interesting question, as a journal file certainly seems
relevant to power fail issues

The most common journal modes are the following:
* default journal mode is to use a rollback journal
* newer journal mode is WAL

Recommended reading: https://www.sqlite.org/wal.html

I think WAL could be safer if you do not use SQLITE_DEFAULT_SYNCHRONOUS=3.

What I don't like about WAL is the need for occasional checkpointing
ref: https://www.sqlite.org/wal.html#automatic_checkpoint

I hope this helps a little.
_______________________________________________
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: Apparent power fail data loss in embedded use - SQLite newbie

Igor Korot
In reply to this post by ted.goldblatt
Hi

On Tue, Mar 12, 2019 at 5:03 PM Ted Goldblatt <[hidden email]> wrote:

>
> On Tue, Mar 12, 2019 at 12:29 PM Simon Slavin <[hidden email]> wrote:
>
> > I may have missed this already being discussed.
> >
> > Will you have access to a copy of the database as it was before corruption
> > testing ?  Can you use SQLite to see whether it is already corrupt ?  Or
> > can the test run on a brand new, freshly-created database ?  If neither of
> > those, your test won't be fair.
> >
>
> We are able to repro this problem, so clean databases aren't a problem.
>  At least the first time this was done in-house, it was on a brand new
> database (not sure about all the subsequent tests - at least some were on
> databases which had already been hit with a power failure).

So what is the exact steps you did to see the problem?

Thank you.

> _______________________________________________
> 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: [EXTERNAL] Re: Apparent power fail data loss in embedded use - SQLite newbie

Hick Gunter
In reply to this post by ted.goldblatt
If the original author bothered to set TEMP_STORE to "always memory", I would take that as a strong indication that they are using TEMP tables for performance reasons.

Off the top of my head I would probably do:

CREATE TABLE log (id integer primary key, timestamp integer, data_item_1 float, ...);
CREATE TEMP TABLE log_buffer (id integer primary key, timestamp integer, data_item_1 float, ...);

Insert 256 rows into log_buffer with id 0..255.

Keep track of the current id in software.

Logging executes the prepared statement: UPDATE log_buffer SET ... WHERE id = :CURR; and increments the rowid

If the current rowid hits 128 (or 256, where it wraps to 0 without extra action if you use an uint8_t):

BEGIN;
INSERT INTO log (SELECT * FROM log_buffer WHERE id < 128);
UPDATE log_buffer SET ... WHERE id <128);
COMMIT;

This would copy the buffered records to persistent storage while significantly reduding the IO load to flash. The copy over is fast and thus reduces the risk of corruption.  Any data in the TEMP table would be lost during a hard power fail. Too bad. A copy over interrupted by the hard power fail would be rolled back on recovery. Hard luck. But the database would be quite resistant to corruption

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Ted Goldblatt
Gesendet: Dienstag, 12. März 2019 22:42
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

On Tue, Mar 12, 2019 at 12:17 PM Simon Slavin <[hidden email]> wrote:

> Other posters have taken care of very important aspects of your
> circumstances, but I wanted to mention one I didn't see anyone mention.
> Settings.
>
> If you compile SQLite without changing compilation settings, and use
> it without changing defaults, SQLite is extremely good at avoiding
> corruption, and at recovering after corruption.  This includes
> corruption due to power-loss at any stage while changes are being made to the database.
>
> However, settings can be made which improve SQLite for some specific
> uses.  They make it faster.  Or use less memory.  Or use less
> filespace while working.  Unfortunately some of them all sacrifice
> harness against corruption.
>
> These settings can be made at three (or more ?  not sure) different places:
>
> 1) Compilation settings when the SQLite API is compiled
> 2) Extra parameters passed when the database is opened
> 3) PRAGMA settings made at any time while the database is open
>
> To assess how 'hard' your use of SQLite is against corruption, you
> would have to track down whether any of the above three have been done.
>
> 1) May or may not be easy.  Do you know how SQLite is included in your
> project ?  Is it part of a library downloaded from somewhere or did
> your programmer compile it themself ?  If the former, you can assume
> that whoever prepared the library didn't mess with default settings.
> If the latter, can you track down the compilation settings they used ?
>
SQLite is built from (the combined) source as part of the project build. I don't see signs in the project options that any special (SQLite) settings are used.  However, the interface is done through the CppSQLite3 interface layer.  (There have been some local changes to CppSQLite3, but they appear to be limited to adding error logging.)  There is also a local header file (that is, one written as part of the project) with what appear to be SQLite compilation options (SQLITE_OMIT_xxx, SQLITE_DEFAULT_CACHE_SIZE, SQLITE_THREADSAFE, etc.  One of these is SQLITE_TEMP_STORE, which is set to "Always use memory" which strikes me a suspicious relative to a power fail problem.  However, I cannot find anything that seems to reference most of these, and specifically not the TEMP_STORE define.  And this file is only included by a project specific DB interface file that invokes CppSQLite3 methods but not by sqlite3.c, so I'm not sure of the point.

>
> 2) Do you have the source code for your project ?  Can you find all
> places where a database is opened ?  If it uses the SQLite API
> directly you can just search for "sqlite3_open".  See whether you can
> spot whether anything except file name & path are passed.
>
I have full sources.   The DB opens are directly by CppSQLite3 (which uses
sqlite_open_v2()), and all of those calls have OPEN_READONLY or OPEN_READWRITE as the 3rd param and 0 as the 4th.  The CppSQLite3 methods themselves take only a filename.

3) Do you have the source code for your project ?  Can you do a global
> search for "PRAGMA" ?  Only a few of the PRAGMAs reduce integrity.
> Most of them are fine.  But you can look them up and see for yourself.
>
 It doesn't appear that any PRAGMAs appear outside the CppSQLite3_16.cpp and sqlite3.h files.

>
> The whole of the above is merely me being picky.  Millions of SQLite
> users just leave all settings at their defaults.  But it seems to be
> the sort of thing you're asking about.
>
It is.  I am guessing that the problem isn't in SQLite, per se, but in the local config or usage.  However, clues about where to look are always welcome...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users