[sqlite] "database disk image is malformed" error occurs more frequently...?

[Mario M. Westphal]

Hello,

 

I’m using SQLite in one of my applications for several years with great success.

The databases managed with SQLite are between 1 and maybe 10 GB, with about 50 
tables or so.

The platform is Windows 7 or higher.

 

Recently I get an increasing number of error reports about “database disk image 
malformed” errors from my users. These errors show up out of the blue, with 
databases held on local hard disks or even SSD’s, no power failures, Windows 
crashes or anything that’s in the rule book of “How to damage your SQLite 
database”.

 

The damage is usually detected during “diagnosis” runs. This feature runs an 
“analyze” and a” vacuum” command in order to physically validate the database 
(and to optimize and compact it).

 

Are there any settings/options I can check and which are known to increase the 
likelihood of physical database damage?

 

+ I always use the most recent version of SQLite.

+ I switched to using WAL mode during a larger update about a year ago. 

+ I use syncmode=NORMAL for a good balance between speed and security.

+ I have PRAGMA wal_autocheckpoint=2 to speed up bulk inserts (this tip 
came from drh).

+ I use nested transactions implemented via checkpoints

 

Anything I need to look for or check?

 

I was under the impression that physical damage is very unlikely and only 
happens under well-known conditions. Maybe something has changed in recent 
SQLite builds that somehow causes this to happen more often? I recall that 
physical damage was really, really rare over the past years – but now I get 
reports maybe once a week…

 

Thanks for reading and your ideas and comments.

 

-- Mario

 

 

 

 

 

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

Re: [sqlite] "database disk image is malformed" error occurs more frequently...?

[Richard Hipp]

On 1/28/15, Mario M. Westphal  wrote:
> Hello,
>
>
>
> I’m using SQLite in one of my applications for several years with great
> success.
>
> The databases managed with SQLite are between 1 and maybe 10 GB, with about
> 50 tables or so.
>
> The platform is Windows 7 or higher.
>
>
>
> Recently I get an increasing number of error reports about “database disk
> image malformed” errors from my users. These errors show up out of the blue,
> with databases held on local hard disks or even SSD’s, no power failures,
> Windows crashes or anything that’s in the rule book of “How to damage your
> SQLite database”.
>

This shouldn't happen.  (But read
https://www.sqlite.org/howtocorrupt.html for more information).

Can you provide additional information?
*   Exactly which version (sqlite_source_id()) of SQLite you are running.
*   The output from the "PRAGMA quick_check;" and/or "PRAGMA
integrity_check;" commands.



>
>
> The damage is usually detected during “diagnosis” runs. This feature runs an
> “analyze” and a” vacuum” command in order to physically validate the
> database (and to optimize and compact it).
>
>
>
> Are there any settings/options I can check and which are known to increase
> the likelihood of physical database damage?
>
>
>
> + I always use the most recent version of SQLite.
>
> + I switched to using WAL mode during a larger update about a year ago.
>
> + I use syncmode=NORMAL for a good balance between speed and security.
>
> + I have PRAGMA wal_autocheckpoint=2 to speed up bulk inserts (this tip
> came from drh).
>
> + I use nested transactions implemented via checkpoints
>
>
>
> Anything I need to look for or check?
>
>
>
> I was under the impression that physical damage is very unlikely and only
> happens under well-known conditions. Maybe something has changed in recent
> SQLite builds that somehow causes this to happen more often? I recall that
> physical damage was really, really rare over the past years – but now I get
> reports maybe once a week…
>
>
>
> Thanks for reading and your ideas and comments.
>
>
>
> -- Mario
>
>
>
>
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more frequently...?

[Stephan Beal]

On Wed, Jan 28, 2015 at 4:19 PM, Richard Hipp  wrote:

> On 1/28/15, Mario M. Westphal  wrote:
> > Recently I get an increasing number of error reports about “database disk
> > image malformed” errors from my users. These errors show up out of the
> blue,
> > with databases held on local hard disks or even SSD’s, no power failures,
> > Windows crashes or anything that’s in the rule book of “How to damage
> your
> > SQLite database”.
> >
>
> This shouldn't happen.  (But read
> https://www.sqlite.org/howtocorrupt.html for more information).
>


To paste in part of a recent thread from the Fossil list which _might_ be
relevant here...


On Sat, Jan 24, 2015 at 10:10 PM, Richard Hipp  wrote:

> On 1/24/15, Richard Hipp  wrote:
> > On 1/24/15, Michai Ramakers  wrote:
> ...>>   SQLITE_CORRUPT: database corruption at line 53682 of [1412fcc480]
> >
>
> Actually, Fossil version 331204dc93 contained a dodgy version of
> SQLite which could generate corruption reports such as the above.  The
> database was not really corrupt.  The error was in the corruption
> detection mechanism.  That error has long since been fixed.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more frequently...?

[Simon Slavin]

On 28 Jan 2015, at 3:15pm, Mario M. Westphal  wrote:

> The damage is usually detected during “diagnosis” runs. This feature runs an 
> “analyze” and a” vacuum” command in order to physically validate the database 
> (and to optimize and compact it).

Please don't do that.  Neither of those things does any diagnosis on the 
database.  And they both make changes which can complicate damage or cover it 
up and let it happen again.

The command

PRAGMA integrity_check

checks your database for faults.  That's the way to detect faults and tell 
whether there really is a problem with your database.



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

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

1. I don’t have the damaged databases here so I cannot run the diagnosis 
myself. The databases are usually too large to upload or transfer.

2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation 
and Visual Studio 2012).
But since not every user always keeps up to day, older versions of SQLite are 
also in use, some maybe 3 to 4 four months old.


2. Sorry for being not more specific. With “running analysis” I meant that may 
application runs a 

PRAGMA integrity_check(100)

after running a wide range of logical checks which checks the data stored in 
the database itself. 

My diagnosis routine then runs ANALYZE for good measure and because the data in 
some of the large tables may change over time a lot. 
If an error has been found by integrity_check(), the diagnosis runs a REINDEX 
operation because this could save the database sometimes in the past.
As the final step of the diagnosis routine, my application runs a VACUUM to 
compress the database (I run the database with auto_vacum=OFF).


3.  The "disk image is malformed" error is often encountered during normal 
processing, when one or more SQLite functions return the error e.g. during 
adding large amounts of data. Since my application often works in an unattended 
mode, it records such errors, logs them into a log file and then informs the 
user at the earliest opportunity. I know that an "disk image is malformed" may 
go unnoticed for a long time, especially if the user does not run the diagnosis 
routines frequently.

3. I have read  https://www.sqlite.org/howtocorrupt.html of course. This is 
what I refer to as "rule book of how to damage your SQLite database".

-- Mario



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

Re: [sqlite] "database disk image is malformed" error occurs more

[Richard Hipp]

On 1/28/15, Mario M. Westphal  wrote:
> 1. I don’t have the damaged databases here so I cannot run the diagnosis
> myself. The databases are usually too large to upload or transfer.
>
> 2. The SQLite version I currently use is 3.8.8.1 (complied using the
> Amalgation and Visual Studio 2012).
> But since not every user always keeps up to day, older versions of SQLite
> are also in use, some maybe 3 to 4 four months old.
>
>
> 2. Sorry for being not more specific. With “running analysis” I meant that
> may application runs a
>
> PRAGMA integrity_check(100)
>
> after running a wide range of logical checks which checks the data stored in
> the database itself.

Can you adjust your application to "phone home" with the results of
"SELECT sqlite_source_id(); PRAGMA integrity_check;" when you find a
problem?

Please also consider activating the Error and Warning Log
(https://www.sqlite.org/errlog.html) and having your application phone
home anomalies detected there too.

>
> My diagnosis routine then runs ANALYZE for good measure and because the data
> in some of the large tables may change over time a lot.
> If an error has been found by integrity_check(), the diagnosis runs a
> REINDEX operation because this could save the database sometimes in the
> past.
> As the final step of the diagnosis routine, my application runs a VACUUM to
> compress the database (I run the database with auto_vacum=OFF).
>
>
> 3.  The "disk image is malformed" error is often encountered during normal
> processing, when one or more SQLite functions return the error e.g. during
> adding large amounts of data. Since my application often works in an
> unattended mode, it records such errors, logs them into a log file and then
> informs the user at the earliest opportunity. I know that an "disk image is
> malformed" may go unnoticed for a long time, especially if the user does not
> run the diagnosis routines frequently.
>
> 3. I have read  https://www.sqlite.org/howtocorrupt.html of course. This is
> what I refer to as "rule book of how to damage your SQLite database".
>
> -- Mario
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ROWID-based queries slow in FTS db

[farkas andras]

Hi all, Im using FTS through DBD::SQLite (perl) to query large text 
databases (~10GB, ~10 million records). The regular FTS MATCH searches work 
fine (they usually run under a second), but searches based on ROWID are 
atrociously slow and hog massive amounts of memory. Im trying to retrieve 
a couple of adjacent rows like so:
my $q_c = $dbh->prepare( "SELECT * FROM ftstable WHERE (ROWID BETWEEN 1000 AND 
1040)" );
# my $q_c = $dbh->prepare( "SELECT * FROM ftstable LIMIT 1040 OFFSET 1000" ); # 
tried this too, it isnt any better
$q_c->execute();
The execute takes several minutes and uses ~600 MB of memory. Now, 
http://www.sqlite.org/changes.html writes that:
3.8.1: FTS4 queries are better able to make use of docid<$limit constraints to 
limit the amount of I/O required
Theres also this thread, indicating that rowid searches on FTS databases 
are optimized: 
http://sqlite.1065341.n5.nabble.com/FTS-full-text-query-vs-query-by-rowid-td77534.html
 I was using 3.7.x so I updated DBD::SQLite and that got me up to SQLite 3.8.7, 
but I see no significant improvement. Explain query gives the same result as 
the linked thread: 0|0|0|SCAN TABLE tmdata VIRTUAL TABLE INDEX 393216.
Maybe there is a better way to write the query? If not, is there any hope that 
this will get fixed? Thanks,András Farkas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[RSmith]

On 2015/01/28 20:06, Mario M. Westphal wrote:

1. I don’t have the damaged databases here so I cannot run the diagnosis 
myself. The databases are usually too large to upload or transfer.

2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation 
and Visual Studio 2012).
But since not every user always keeps up to day, older versions of SQLite are 
also in use, some maybe 3 to 4 four months old.


Hi Mario,

Thank you for being specific. I have to ask, what were the changes you implemented in your application a few months ago?  SQLite 
seems to not be in general worse for wear, but almost weekly you are getting serious problems (in DB terms) cropping up - and this 
only started some months ago. The common denominator seems to be "some months ago", so your system may have changed in a way that 
somehow facilitates the error.


Of course you already pointed this out, so it's understood, but my aim is that: if you could list the changes you have made recently 
in general  and maybe specific to SQLite usage, we might better guess at which things to check or recognise similarities with 
problems we've faced.  That said, there are not many design choices that might cause "Database malformed" errors and since you are 
already familiar with all the documentation, we could assume you would have noticed anything obvious.


All this makes it very hard to guess. Getting specific logs with the improved error reporting would be helpful as Richard suggested, 
or making your app "phone home".  Are the come-backs all random? Do you have your own server running a user version or test version 
under full load at your own offices perhaps?


Also, getting one Malformed Database a week out of how many? 5, 500, 500 000?  (Not that it changes anything, there should be no 
incidents, but it might tell us something about the prevalence). Does your system  have a back-up mechanism? (The DB sizes you 
describe seem to suggest you would shy away from an auto-multi-backup scenario).



I know all of the above do not help directly, but this error seems strange so I 
am simply prompting for more information.

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

[sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

This is a bit of a speculative question related to a problem I'm having -
are there legal values of a C++ double that would get truncated when
written into and read from an SQLite database?  The column is specified as
having REAL affinity though I gather that shouldn't matter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Igor Tandetnik]

On 1/28/2015 5:47 PM, Donald Shepherd wrote:

This is a bit of a speculative question related to a problem I'm having -
are there legal values of a C++ double that would get truncated when
written into and read from an SQLite database?


Written into and read from how, exactly? Do they, say, round-trip 
through a string representation at any point?


I'm pretty sure that, if you put a value into a column with correct 
affinity using sqlite3_bind_double, you'd get the exact same value out 
using sqlite3_column_double (signalling NaN possibly excepted).

--
Igor Tandetnik

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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

I can say there's no string round-trips with fairly high confidence and I
am using _bind_double and _column_double.

I can also confirm NaNs are a special case, as I've had to write code to
store those as a BLOB.

On Thu Jan 29 2015 at 9:57:14 AM Igor Tandetnik  wrote:

> On 1/28/2015 5:47 PM, Donald Shepherd wrote:
> > This is a bit of a speculative question related to a problem I'm having -
> > are there legal values of a C++ double that would get truncated when
> > written into and read from an SQLite database?
>
> Written into and read from how, exactly? Do they, say, round-trip
> through a string representation at any point?
>
> I'm pretty sure that, if you put a value into a column with correct
> affinity using sqlite3_bind_double, you'd get the exact same value out
> using sqlite3_column_double (signalling NaN possibly excepted).
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[RSmith]

On 2015/01/29 01:00, Donald Shepherd wrote:

I can say there's no string round-trips with fairly high confidence and I
am using _bind_double and _column_double.

I can also confirm NaNs are a special case, as I've had to write code to
store those as a BLOB.


So you have a case where you have a 64-bit IEEE754 Float (C - double) which isn't a NaN and add it to a query by 
sqlite3_bind_double() and then retrieve it back using column_double() and the bit pattern across the 8 bytes is different to the 
original?


Please  do share!



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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Simon Slavin]

On 28 Jan 2015, at 10:47pm, Donald Shepherd  wrote:

> This is a bit of a speculative question related to a problem I'm having -
> are there legal values of a C++ double that would get truncated when
> written into and read from an SQLite database?

In theory there should be no problem here.

C doubles have 15 to 16 digits of precision.

In SQLite databases, numbers which can't be stored as integers are stored as 
IEEE 754-2008 64-bit floating point numbers, sometimes known as 'binary64'.  
These give 15 to 17 digits of precision.

My understanding is that it is possible to store every distinct C double value 
as a distinct binary64 value.

If it's the conversion that worries you, you can read the SQLite source code to 
find the programming used to encode and decode numbers into this format.  I am 
not competent to read that source code and tell you definitely that it works 
for all C double values.

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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Donald Shepherd]

Thanks for the reassurances.  I have a case where differences in doubles
would explain what I'm seeing but I have no evidence that it is the case
(evidence compilation is still underway), hence my attempt to plumb the
depths of the list's knowledge to see if there was any known edge cases to
be aware of (other than the NaN one I'd already run into). :)

On Thu Jan 29 2015 at 10:10:35 AM Simon Slavin  wrote:

>
> On 28 Jan 2015, at 10:47pm, Donald Shepherd 
> wrote:
>
> > This is a bit of a speculative question related to a problem I'm having -
> > are there legal values of a C++ double that would get truncated when
> > written into and read from an SQLite database?
>
> In theory there should be no problem here.
>
> C doubles have 15 to 16 digits of precision.
>
> In SQLite databases, numbers which can't be stored as integers are stored
> as IEEE 754-2008 64-bit floating point numbers, sometimes known as
> 'binary64'.  These give 15 to 17 digits of precision.
>
> My understanding is that it is possible to store every distinct C double
> value as a distinct binary64 value.
>
> If it's the conversion that worries you, you can read the SQLite source
> code to find the programming used to encode and decode numbers into this
> format.  I am not competent to read that source code and tell you
> definitely that it works for all C double values.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

[James K. Lowden]

On Wed, 28 Jan 2015 23:09:21 +
Simon Slavin  wrote:

> > This is a bit of a speculative question related to a problem I'm
> > having - are there legal values of a C++ double that would get
> > truncated when written into and read from an SQLite database?
> 
> In theory there should be no problem here.
> 
> C doubles have 15 to 16 digits of precision.
> 
> In SQLite databases, numbers which can't be stored as integers are
> stored as IEEE 754-2008 64-bit floating point numbers, sometimes
> known as 'binary64'.  These give 15 to 17 digits of precision.

We can make stronger statements than that, can't we?  It's not like
there's some mystery about it: database and memory share a single
floating-point format.  

If the caller uses _bind_double and _column_double, he's storing and
fetching a 64-bit double.  SQLlite's only job is to collect those 8
bytes, store them, and return them unmolested.  It has no private
definition of "double", offers no conversion between different
representations of double.  The bit pattern -- IEEE 754-2008, as you
say -- is defined by the *hardware*.  That format is shared up and down
the stack, regardless of whether or not SQLite and the application were
compiled using the same compiler.  

(That's no 100% true, of course.  Is anyone seen SQLite running on a
VAX?  Has it ever been done?)  

So I think it's a bit disingenuous to speak in terms of digits of
precision.  Application and library alike share a single IEEE
double-precision floating point representation.  There's no reason to
think, if the data are provided in binary form, that they won't be
returned in the identical form absent an explicit conversion.  If
that's not so, I'd sure like to know why.  

I'm faintly surprised NaNs can't be stored, too.  Why should SQLlite
interpret them if they're bound to a double?  

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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Simon Slavin]

On 29 Jan 2015, at 3:05am, James K. Lowden  wrote:

> We can make stronger statements than that, can't we?  It's not like
> there's some mystery about it: database and memory share a single
> floating-point format.  
> 
> If the caller uses _bind_double and _column_double, he's storing and
> fetching a 64-bit double.  SQLlite's only job is to collect those 8
> bytes, store them, and return them unmolested.

Ah well, the reason I didn't state this was that I didn't know it.  Thanks for 
the information.

I wonder what happens if you put SQLite on a computer with no native IEEE maths 
library.

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

Re: [sqlite] Truncation of floating point numbers in SQLite?

[Igor Tandetnik]

On 1/28/2015 10:05 PM, James K. Lowden wrote:

I'm faintly surprised NaNs can't be stored, too.  Why should SQLlite
interpret them if they're bound to a double?


Signaling NaN may trigger a hardware exception (aka a signal) merely by 
being loaded into an FPU register (that's what makes it "signaling"), 
depending on how FPU is configured; which in turn could happen merely by 
passing it to a function, or returning from one. Quiet NaNs should 
round-trip just fine.

--
Igor Tandetnik

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