Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Chris Brody]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Igor Korot]

Hi

On Tue, Mar 12, 2019 at 5:03 PM Ted Goldblatt  wrote:
>
> On Tue, Mar 12, 2019 at 12:29 PM Simon Slavin  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Ted Goldblatt]

On Tue, Mar 12, 2019 at 12:29 PM Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Ted Goldblatt]

On Tue, Mar 12, 2019 at 1:55 PM Jim Morris  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Simon Slavin]

On 12 Mar 2019, at 9:41pm, Ted Goldblatt  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.



> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Ted Goldblatt]

On Tue, Mar 12, 2019 at 12:17 PM Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Warren Young]

On Mar 12, 2019, at 11:30 AM, Ted Goldblatt  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[David Raymond]

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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of ted.goldbl...@gmail.com
Sent: Tuesday, March 12, 2019 10:37 AM
To: sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
wrote:


On Tue, 12 Mar 2019 10:36:37 -0400
ted.goldbl...@gmail.com 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Ted Goldblatt]

On Tue, Mar 12, 2019 at 11:45 AM James K. Lowden 
wrote:

> On Tue, 12 Mar 2019 10:36:37 -0400
> ted.goldbl...@gmail.com 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Simon Slavin]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[Simon Slavin]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

[James K. Lowden]

On Tue, 12 Mar 2019 10:36:37 -0400
ted.goldbl...@gmail.com 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users