Re: [sqlite] "database disk image is malformed" error occurs more (AGAIN, damage)

[Mario M. Westphal]

Had another damaged database report.

This time it is a configuration database which holds application settings. 

 

The file is stored on a local disk, not shared, and only ever accessed by my 
application and only by one thread.

The database is run in FULL sync mode for maximum security.

I’m puzzled. These files are very small, a few MB only.


The error message is

 

*** in database main ***Page 15: Rowid 3050 out of order (max larger than 
parent max of 3016)Page 128: Rowid 3017 out of order (min less than parent min 
of 3050) 

 

 

___
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

[James K. Lowden]

On Fri, 30 Jan 2015 13:17:26 -0500
Stephen Chrzanowski  wrote:

> 2.1 Filesystems with broken or missing lock implementations
> 
> SQLite depends on the underlying filesystem to do locking as the
> documentation says it will. But some filesystems contain bugs in their
> locking logic such that the locks do not always behave as advertised. 

The problem is even deeper than that.  NFS does not implement Posix
semantics.  The actual behavior is hard to reason about and far outside
SQLite's scope.  

On a local file system the kernel guarantees filebuffer cache
coherency.  If process A reads a block from the disk, and process B
modifies the same block, the next time process A consults that block it
will see B's changes.  Note this happens whether or not locking is
involved, and regardless of the state of the disk.  It's a by-product of
a unified buffer cache.  

On a network filesystem there is no unified buffer cache.  Writes by B
are not seen when A consults its cached block.  NFS does not promise
that a second read by A will reflect changes made by B.  Even if all
locks are implemented corrected and honored, A stands to read invalid
data unless steps are taken to manage the cache, something SQLite
doesn't do afaik.  

The subject has been discussed here before, as it turns out.  The
Googles returned
http://sqlite.1065341.n5.nabble.com/SQLite-on-NFS-cache-coherency-td33697.html,
which contains much more detail and references.  

DBMS implementations are always about arbitrating access to shared
data.  They require a single, unified view of the data.  Getting that
view over a remote filesystem is difficult in the best of circumstances
and requires explicit measures be taken.  SQLite doesn't attempt to
do so, by design.  (A reasonable choice IMO.)  If you want multi-node
access to a database over a network, there are many other options.
Unsurprisingly, none of them use a network filesystem either.  

HTH.  

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

As I wrote above, damaged databases are replaced. No user continues working 
with a damaged database once it has been identified. The issue here is to 
detect this early and avoid it altogether.

 

> One column of one row of one table may get corrupted.  

> If that's the case then the database can be used for years 

> without any problem being noticed.

> Theoretically "PRAGMA integrity_check" will notice it, however.

 

a) As I wrote above. 

b) integrity_check must find such issues. That’s how I understand it and 
Richard told me once.

 

___
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. No client-server, I use MySQL, SQL-Server or ORACLE for that.

 

2. No access to the SQLite database ever by more than one process concurrently 
in writable mode. In readable mode, yes. But the reported damage cases were 
always single user, one PC.

 

3. I cannot prevent or disallow users to keep their databases on NAS or remote 
server storage. Telling them that keeping a file on a NAS box will probably 
damage the file would be sales venom. 

Despite, the reported cases were all databases stored on local disks, except 
one. 

I keep databases between 0.5 and 10 GB on NAS (Linux/SAMBA) and Windows servers 
for my test scenarios and perf/load tests. No troubles, no corruption.

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

I estimate that over 90% of the users keep the database on local disks. I can 
tell from the log files.

Keeping the SQLite database it on a network server really hurts performance. 
That’s not what SQLite is designed for, besides all other aspects of network 
locking mentioned in various SQLite docs. I use a MySQL or other RDBMS backend 
for such scenarios.

___
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

[Stephen Chrzanowski]

On Fri, Jan 30, 2015 at 8:07 AM, Mario M. Westphal  wrote:

>
> When a user encounters the problem he/she restores the last working
> backup. I have a few users who faced this problem more than once. Here I
> always assumed some hardware glirch, a shaky USB connection, disk trouble,
> network problems (if the database is held on a Windows server or NAS),
> buggy SAMBA implementations or similar. Often, when asked, users
> ‘remembered’ a power failure, or some other problems with the disk or
> network. Case closed.
>

*Client/Server model*
It hasn't been mentioned by you yet, but if your software is acting in a
client/server model, ensure that your server is accessing the file LOCALLY
and not at a remote point.  Ensure that you're using the internal SQLite
threading tools and checking every single result for every single call in
the server software.  Do not ever let a remote client directly access to
the database file.

*NAS - Network Attached Storage*

If multiple users are accessing the file that lives on a different
computer, it is remote storage, which means NAS.  Any computer with any
share available on a network, that machine *IS* to be considered a NAS to a
remote machine.  Drobo, FTP, Windows, Unix/Linux,  CIFS/NFS/etc - Whatever
the protocol used, if what you're accessing isn't local to the computer, it
is a NAS.  Windows, Linux, and "Other" network protocols, be it 'buggy'
SAMBA or a Windows file share, it doesn't matter.  *ALL* are prone to
making SQLite have issues.  A single user using a single remote source
should be OK (But I wouldn't trust it), but the SECOND you start throwing
multiple connections at a remote file, you're begging, pleading, and even
offering your first born child to the computer Gods asking for data
problems.  The problem is NOT with Windows, and the problem isn't going to
show up in your event logs anywhere, but with the file sharing protocol
itself at the remote side, and even THAT machine won't make note of bad
file accesses or when a file is accessed.  The remote system isn't properly
releasing the necessary lock information to your computer, which is where
the problem is happening.

Directly from https://www.sqlite.org/howtocorrupt.html

--
2.0 File locking problemsSQLite uses file locks on the database file, and
on the write-ahead log or WAL file, to coordinate access between concurrent
processes. Without coordination, two threads or processes might try to make
incompatible changes to a database file at the same time, resulting in
database corruption.

2.1 Filesystems with broken or missing lock implementations

SQLite depends on the underlying filesystem to do locking as the
documentation says it will. But some filesystems contain bugs in their
locking logic such that the locks do not always behave as advertised. *This
is especially true of network filesystems and NFS in particular.* If SQLite
is used on a filesystem where the locking primitives contain bugs, and if
two or more threads or processes try to access the same database at the
same time, then database corruption might result.

--
{Highlighted by me}

Write your software to detect where the file is being loaded from.  If your
software is written for Windows, it is SIMPLISTIC to find out what kind of
drive you're accessing a file from, and it is even MORE simplistic to find
out if you're accessing a file via a UNC (\\system\share) by just looking
at what the full file path your program is loading the file from.  I've
never coded anything under a 'Nix system except for scripts, but there
should be a way to find out if the path you're accessing is remote by
looking at /etc/fstab (or equiv) and track from there.  The moment your
software sees a that it is accessing something OTHER than a file "local to
the computer, be it HDD/SDD/USB", warn the user of possible data
corruption, log that the attempt was made, and go from there on whatever
path you want to proceed.  Proceed with systems running as usual, or, deny
access to the file, or close out of the application entirely.

Confirm, with ABSOLUTE CERTAINTY, that database files that are being used
are on local storage devices and validate that if these files ARE being
accessed locally, THEN maybe start digging into different kinds of
corruption problems.  Removing a machine from your process is going to make
things MUCH easier to diagnose.
___
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

[Simon Slavin]

On 30 Jan 2015, at 1:07pm, Mario M. Westphal  wrote:

> What worries me more are the incidents where users see this problem happen 
> several times, with q database kept on a local hard disk or SSD.

Just to make it clear, when corruption is reported, the corruption is not 
automatically fixed.  The database will still be corrupt, and as the app 
continues it may notice the same corruption again and report it again.  This is 
why I asked you whether you are noticing more corruption or are just continuing 
to use a corrupt database.

So yes, if the user continues to use the same database, they'll get more error 
messages.  And if they restore a backup it might be a good idea to check to see 
whether that backup is corrupt.  At least until you have tracked down the cause 
of your corruption and stopped it.

> that’s really hard to tell, because unless SQLite has to access a corrupted 
> section of the file during normal operation, or integrity_check() is run, a 
> damaged database may behave perfectly normal for a long time...


One column of one row of one table may get corrupted.  If that's the case then 
the database can be used for years without any problem being noticed.  
Theoretically "PRAGMA integrity_check" will notice it, however.

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]

> Okay.  First, stop doing VACUUM after this.  You're not improving things and 
> you may be making things worse

 

Not important. If this error is encountered the database is marked and the user 
reminded on every open/close to replace it with a backup. The database is not 
supposed to be used after SQLite has reported it as corrupt.

 

When a user encounters the problem he/she restores the last working backup. I 
have a few users who faced this problem more than once. Here I always assumed 
some hardware glirch, a shaky USB connection, disk trouble, network problems 
(if the database is held on a Windows server or NAS), buggy SAMBA 
implementations or similar. Often, when asked, users ‘remembered’ a power 
failure, or some other problems with the disk or network. Case closed.

 

 

What worries me more are the incidents where users see this problem happen 
several times, with q database kept on a local hard disk or SSD. The Windows 
event log shows no reports about anything disk related. No power failure. No 
hard shut-down. No problems reading or writing data in other applications.

 

The database may be several months old or fresh. The error is sometimes 
encountered during a diagnosis run (with integrity_check) or a 
SELECT/INSERT/UPDATE suddenly returns the dreaded SQLITE_CORRUPT error code. 
This can happen for databases with 200 MB or databases with 10 GB. It 
apparently does not necessarily happen during times of high activity or bulk 
inserts. But that’s really hard to tell, because unless SQLite has to access a 
corrupted section of the file during normal operation, or integrity_check() is 
run, a damaged database may behave perfectly normal for a long time...

 

I have now implemented the ErrorCallback routine and future versions will log 
anything reported that way to the log file. Maybe this gives us some more data 
to work with. I assume that this function is safe to use in a scenario where 
multiple instances/connections of SQLite are in use in parallel? My application 
uses multiple threads, but each thread uses a separate instance of SQLite.

 

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

[RSmith]

On 2015/01/30 14:45, Mario M. Westphal wrote:

- The databases in question are stored on a location hard disk or SSD.

- If a user stores his database on a NAS box or Windows server, it is accessed 
directly, via standard Windows file system routines.

- From what I can tell, network-based databases are not more likely to corrupt 
than databases stored on built-in disks or SSDs or databases kept on disks or 
USB sticks connected via USB.


That is simply not true. The report-back on locking success via a local resource (albeit for a removable drive) is under normal 
circumstances absolute and correct. For a network file (remote) source, that is just not true in near all network cases.  If you can 
be sure only one instance of your program access it over the network and nothing else, then it should not be harmed, but this is 
difficult.


Users kill their processes and re-start programs and SQLite connections (unwittingly) that finds hot roll-back journals and all 
kinds of things that might fall into a long "busy" cycle which may again prompt a process-kill, etc.


It's easy to tell though, when you get reports of corruption, require the file location information. A pattern should quickly emerge 
if this is a networking problem.



- My software is updated every 2 to 4 weeks, and I always include and ship with 
the latest SQLite version.

- There is a big variance in when users update so some users may work with 
versions several months old, but not older than 2 months, typically.

- A user may access a database from multiple computers, but then only in 
read-only mode. Write access is only permitted when the database is opened in 
exclusively.

- I use SQLite since about 2008, but the code base is changed frequently. I 
maintain old databases (up to maybe one year old and use them in regression 
tests before shipping).



___
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

[Stephan Beal]

On Fri, Jan 30, 2015 at 1:45 PM, Mario M. Westphal  wrote:

> - From what I can tell, network-based databases are not more likely to
> corrupt than databases stored on built-in disks or SSDs or databases kept
> on disks or USB sticks connected via USB.
>

That's a big assumption. Network filesystems are historically _notorious_
for locking-related problems (the root of many corruption problems).

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

[Mario M. Westphal]

- The databases in question are stored on a location hard disk or SSD.

- If a user stores his database on a NAS box or Windows server, it is accessed 
directly, via standard Windows file system routines.

- From what I can tell, network-based databases are not more likely to corrupt 
than databases stored on built-in disks or SSDs or databases kept on disks or 
USB sticks connected via USB.

- My software is updated every 2 to 4 weeks, and I always include and ship with 
the latest SQLite version. 

- There is a big variance in when users update so some users may work with 
versions several months old, but not older than 2 months, typically.

- A user may access a database from multiple computers, but then only in 
read-only mode. Write access is only permitted when the database is opened in 
exclusively.

- I use SQLite since about 2008, but the code base is changed frequently. I 
maintain old databases (up to maybe one year old and use them in regression 
tests before shipping). 

 

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

[Simon Slavin]

On 29 Jan 2015, at 7:04pm, Mario M. Westphal  wrote:

> The diagnosis log of my application reports the output of integrity_check() 
> already.
> 
> I retrieved the log from the most recent error report. This is my application 
> has logged:
> 
> '*** IN DATABASE MAIN ***
> ON TREE PAGE 385120 CELL 24: INVALID PAGE NUMBER 151192068
> CORRUPTION DETECTED IN CELL 24 ON PAGE 385120
> CORRUPTION DETECTED IN CELL 25 ON PAGE 385120
> MULTIPLE USES FOR BYTE 1612 OF PAGE 385120
> FRAGMENTATION OF 30 BYTES REPORTED AS 0 ON PAGE 385120'

Okay.  First, stop doing VACUUM after this.  You're not improving things and 
you may be making things worse.

Second, a corrupt database may remain corrupt.  So we try to distinguish 
between (A) and (B):

A) Something corrupted my database but that was just once and it has never 
happened again
B) Something is continually corrupting my database.

So have you tried replacing that database with one which isn't corrupt and 
seeing whether the new 'clean' one somehow becomes corrupt ?

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

[Stephen Chrzanowski]

On Thu, Jan 29, 2015 at 2:07 PM, Mario M. Westphal  wrote:


> Most database damaged errors encountered over time could be pinned to
> power failures, disk or *network problems*.
>
> 


Network problems?  I might have missed a good chunk of this thread, but,
this begs to be asked Are you running a client/server model in which
the server is the ONLY machine accessing the database file, or, do you have
multiple machines touching the file via a network share?  If you're running
multiple machines talking via a network interface directly to the database,
you need to stop, ESPECIALLY with the up in frequency you seem to be
running into this problem.  I did note you did read the "How To Corrupt"
page, but you may have missed the whole networking thing that shouldn't be
done.

If you're running client/server in that a client opens a custom network
protocol to a server application, and the server application touches the
database BY ITSELF, then you need to look at what the hardware is doing
between the application and the storage device.

I can't say for certain, and maybe Dr Hipp and others will need to get
involved in looking at the low level SQLite code base, but if YOUR code
base code is from 2008, and it is now 2015, and you've got applications
talking with a single source (Meaning one customer = one source of their
own data) with different versions of the SQLite code, *MAYBE* you're
looking at an older and bugged version of SQLite that is doing one thing to
the raw data while a working version comes back and informs you with the
"WTF?" errors.
___
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]

The core code is in place since about 2008. 

 

I took advantage of changes in SQLite over time, from using the shared cache to 
switching to WAL mode for databases which are not opened in read-only mode.

These changes were made between 12 and six months ago, and tested during beta 
tests and also in the wild. 

Most database damaged errors encountered over time could be pinned to power 
failures, disk or network problems. 

But a too high number of recent reports (couple of months) could not be linked 
to any hardware problem or power failure. 

 

My application uses multiple concurrent threads, but each thread works with its 
own instance of SQLite (on the same database). Transactions are used to improve 
performance and for control 

flow. Every error returned by SQLite is logged to the log file. If a SQLite 
function returns the dreaded “disk image malformed” error, my application 
immediately stores that error and remembers it – the database is marked as 
defective and the user is notified as soon as possible.

 

My users run daily backups of all their important data, including the database 
so usually they can roll-back to the last known working backup and continue. 

 

I will implement Richard’s suggestions to gather more info to the log file. The 
next time a user reports the problem, we may get extra hints about why and when 
this happened.

 

Thanks for the great support and advice.

 

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

[Mario M. Westphal]

The diagnosis log of my application reports the output of integrity_check() 
already.

I retrieved the log from the most recent error report. This is my application 
has logged:

 

'*** IN DATABASE MAIN ***

 

ON TREE PAGE 385120 CELL 24: INVALID PAGE NUMBER 151192068

 

CORRUPTION DETECTED IN CELL 24 ON PAGE 385120

 

CORRUPTION DETECTED IN CELL 25 ON PAGE 385120

 

MULTIPLE USES FOR BYTE 1612 OF PAGE 385120

 

FRAGMENTATION OF 30 BYTES REPORTED AS 0 ON PAGE 385120'

 

 

 

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

My application does not phone home :-/ but I can add output of these functions 
to the log file my application maintains. My users know how to collect these 
log files and send them to me.

I will also add the error logging callback to my wrapper class and route it to 
the log file.

 

This should give additional information in case these errors repeat. I will do 
that right away and ship this with the next update. Will take a couple of weeks 
to saturate the user basis.

 

 

 

 

 

___
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

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

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 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 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...?

[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

[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