Database corruption question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Database corruption question

Doug Nebeker-2
Been using SQLite for a long time and a huge fan.  We occasionally see database corruption on a local NTFS Windows drive and I've been trying to figure it out.

I finally have some logs from the SQLITE_CONFIG_LOG callback that may be of help:

(11) database corruption at line 78267 of [3d862f207e]
statement aborts at 29: [DELETE FROM StatData WHERE StatID IN (SELECT StatID FROM Statistic WHERE OwnerType IN (4) AND OwningComputer='f7ab745b-1aa7-4159-bbf7-b0b4d1262804') AND Date < 1350715577;]

This is for version 3.11, and the line is for a OP_NotExists case.
There was no power outage or other OS problem, this was not a restored database but had been in use for at least 12 hours.  It was not using WAL, and there is only one process using the database file (though multiple threads, but they each have their own database handle).

I've been through https://www.sqlite.org/howtocorrupt.html a number of times over the years :)

Would it be possible for a file scanning process (anti-virus, backup, etc) to grab hold of a database file at just the right moment, momentarily blocking a write or delete, and causing corruption?

There is a greater chance that this is my bug or environmental than being in SQLite, so I'm looking for anything I can do to decrease these occurrences.

Thanks for any input.

Doug
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database corruption question

Simon Slavin-3

On 19 Jul 2016, at 5:43pm, Doug Nebeker <[hidden email]> wrote:

> Would it be possible for a file scanning process (anti-virus, backup, etc) to grab hold of a database file at just the right moment, momentarily blocking a write or delete, and causing corruption?

It might prevent the program from accessing the file in a way that the program interprets as corruption.  It should not actually corrupt the file on disk.  In that if you later reopen the file and run

PRAGMA integrity_check

it should not find a problem.

Does that command find a problem with your file ?  Once you have noticed the problem are you restoring to an uncorrupt version before continuing use of the file ?

> There is a greater chance that this is my bug or environmental than being in SQLite, so I'm looking for anything I can do to decrease these occurrences.

Are you using any PRAGMAs after you open the file or special modes in your open command ?

Do you check the result codes returned by all commands run on the file to make sure they're SQLITE_OK ?  The command which notices that the file is corrupt is run sometime /after/ the command which corrupts it.  It's probably not the command which causes the corruption.

When you make changes to the file do you use _exec() or _prepare,_step,_finalize ?

Is the database short enough to allow you to run 'PRAGMA integrity_check' occasionally (when your app starts or quits ?  At 3am every morning ?) and check the result ?

Do not forget that your mention of 'environmental' includes the possibility of a faulty hard disk.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database corruption question

Richard Hipp-3
In reply to this post by Doug Nebeker-2
On 7/19/16, Doug Nebeker <[hidden email]> wrote:
>  there is only one process using the database file (though multiple
> threads, but they each have their own database handle).

That threading mode (https://www.sqlite.org/threadsafe.html) are you
using?  Are you sure that you are using the threading mode that you
think you are using?  If you have not done so already, have you tried
setting the threading mode to SERIALIZED to see if that clears your
problem?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database corruption question

Tim Streater-3
In reply to this post by Doug Nebeker-2
On 20 Jul 2016 at 20:29, Richard Hipp <[hidden email]> wrote:

> On 7/19/16, Doug Nebeker <[hidden email]> wrote:
>>  there is only one process using the database file (though multiple
>> threads, but they each have their own database handle).
>
> That threading mode (https://www.sqlite.org/threadsafe.html) are you
> using?  Are you sure that you are using the threading mode that you
> think you are using?  If you have not done so already, have you tried
> setting the threading mode to SERIALIZED to see if that clears your
> problem?

Is there a way to determine which threading mode is in use? I'm running the SQLite built-in to an IDE (Xojo) which uses SQLite 3.9.2. I couldn't see a pragma to use.

--
Cheers  --  Tim

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database corruption question

LincolnBurrows
This post has NOT been accepted by the mailing list yet.
In reply to this post by Doug Nebeker-2
you need to check for the memory mappings I/O if it occur due to this, otherwise you can go through Sqlite File Recovery Tool