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] printf() - Local decilmal separator

[James K. Lowden]

On Tue, 12 Mar 2019 16:08:24 +
Alexandre Billon  wrote:

> 1st question : Is there a way to tell printf() to display the decimal
> separator set in the OS ?
> 
> For example, the decimal separator in my OS is set to comma (,), I
> would like printf() to display the comma as the decimal separator.

You may want to return the value in native form and use your C library
to format it.  Posix defines the single-quote character as a modifier
that does just what you want:


 ''' (apostrophe)
Decimal conversions (d, u, or i) or the integral portion of a
floating point conversion (f or F) should be grouped and
separated by thousands using the non-monetary separator
returned by localeconv(3).

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

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

[Ted Goldblatt]

On Tue, Mar 12, 2019 at 11:47 AM Hick Gunter  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
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

[sqlite] printf() - Local decilmal separator

[Alexandre Billon]

Hello,

1st question : Is there a way to tell printf() to display the decimal separator 
set in the OS ?

For example, the decimal separator in my OS is set to comma (,), I would like 
printf() to display the comma as the decimal separator.

sqlite> select printf('%.2f %%', 25.365419);
25.37 %

2nd question : Is there a way that printf displays thousand separators and 
decimal separators ?
sqlite> select printf('%,.2f %%', 25566425.365419);
25566425.37 %
sqlite> select printf('%,f %%', 25566425.365419);
25566425.365419 %
sqlite> select printf('%,d %%', 25566425.365419);
25,566,425 %

I haven't found anything here : https://www.sqlite.org/printf.html

Best Regards

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

[Hick Gunter]

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:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ted.goldbl...@gmail.com
Gesendet: Dienstag, 12. März 2019 15:37
An: sqlite-users@mailinglists.sqlite.org
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

Re: [sqlite] Recursive CTE on tree with doubly linked items

[James K. Lowden]

On Mon, 11 Mar 2019 10:39:06 +0100
Jean-Luc Hainaut  wrote:

> Your implementation of trees is that of network databases at the 
> pointer-based physical level but definitely not relational. Try this:
> 
> create table TREE(
>ID integer not null primary key,
>Parent  integer references TREE on delete ... on update cascade);
> -- Notice the absence of "not null"
> create index XTREE on TREE(Parent); -- Only useful for large sets of
> nodes
> 
> That's all.

Bravo!  

To the OP: this is the answer you want, whether you want it or not.  

> > I've a tree with doubly linked items. 

That's the root of your problem, as it were.  It's hard to solve in SQL
because you're trying to use SQL in a nonrelational way.  

--jkl
___
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

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

Re: [sqlite] Maximum result set size

[Simon Slavin]

On 12 Mar 2019, at 6:38am, niklas  wrote:

> Surely that works the same way as sqlite3_step in respect to memory use since 
> it's using step internally and just forwards the results to the callback 
> function of exec.
> 
> Or did I miss something?

You missed nothing.  _exec() returns only the same integer result code as most 
other API calls.  It does not return the result from a SELECT, for example.

Just as with individual calls to _step(), _exec() never has to accumulate a 
complete set of results.  So it doesn't have to accumulate results in memory, 
using a lot of memory if there are a lot of results.

You can make it call your callback with each row, one by one, but it does not 
remember the rows itself.

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

Re: [sqlite] Maximum result set size

[niklas]

A clarification about sqlite3_exec please.

Surely that works the same way as sqlite3_step in respect to memory use
since it's using step internally and just forwards the results to the
callback function of exec.

Or did I miss something?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Number of open connections

[Chris Locke]

Does SQLite keep a count of the number of current open connections to the
database?
On the DB4S mailing list, there is an enquiry (
https://github.com/sqlitebrowser/sqlitebrowser/issues/1798) about
encryption failing due to the database being open.  Was wondering whether a
PRAGMA or function returned the number of open database connections.


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

[sqlite] Bug: Documentation typo

[Kent Ross]

At https://www.sqlite.org/rescode.html#cantopen the text says ..."or on of
several"... which should probably be "or one of several".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe (atomic) db file snapshot and update

[anton]

My main goal - to avoid blocks and interruptions in this two scenarios.

Thanks for "vacuum into" - I will test that.

Is the "foo.db" in Scenario 1 the same "foo.db" in scenario 2 or do they just 
have the same name by happenstance?  



Or the same database of different machines or something?


Yes. Same db, different machines.

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

Re: [sqlite] Number of open connections

[Chris Locke]

Thanks Richard for the reply.  Appreciated.

On Tue, Mar 12, 2019 at 2:49 AM Richard Hipp  wrote:

> On 3/11/19, Chris Locke  wrote:
> > Does SQLite keep a count of the number of current open connections to the
> > database?
>
> No.
>
> SQLite can find out if some other connection has the database open in
> WAL mode, or if some other database has an active transaction, because
> it needs to know those things.  But there is no counter.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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