detecting database corruption?

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

detecting database corruption?

Wilson Yeung
I am currently writing software that will be shipped directly to
consumers, and have SQLite embedded.

The application is multi-threaded and I have:

Reader-Writer Thread:
  begin transaction; write; write; read; write; read; end transaction;

Reader Thread:
  read; read; read

For several reasons, it is important to me that one thread
continuously writes and reads while another thread continuously reads.

In order to get decent write performance while in FULL synchronous
mode, I have as many as hundreds of pending writes committed in a
single transaction.  This gets me terrific write performance, but the
reader thread doesn't see any of the pending data until the
transaction is actually committed.

If I put the database in synchronous NORMAL or synchronous OFF mode,
then write performance gets a lot better and I can use autocommit
transactions... but then the database is considerably more sensitive
to OS crashes and power failure.

This is actually okay for me as long as I can reliably detect that the
database file is corrupt.  Failure is okay, but byzantine failure is

Does SQLite detect database corruption?  If so, what kind of
corruption is detected and how does it do so?

Reply | Threaded
Open this post in threaded view

Re: detecting database corruption?

D. Richard Hipp
Wilson Yeung <[hidden email]> wrote:
> Does SQLite detect database corruption?  If so, what kind of
> corruption is detected and how does it do so?

SQLite does attempt to detect corruption in database files
and returns SQLITE_CORRUPT when it finds it.  The regression
test suite for SQLite runs thousands of tests in which
random binary data overwrites random small sections of a
database file. These tests confirm that the corruption is
detected and reported properly.


(1) Corruption is only detected when SQLite tries to use
    the corrupted section of the database.  If the corruption
    occurs in table ABC and you are only accessing table PQR,
    then you will never see the SQLITE_CORRUPT error.

(2) If the corruption occurs in the middle of (say) a large
    BLOB, the BLOB will be changed of course, but as SQLite
    does not store checksums or other error detecting codes
    on data, there is no way for SQLite to know this.

(3) Corrupt detection has historically been a buggy area of
    SQLite.  In spite of the many thousands of test cases that
    are run against the library, users do from time to time
    find creative ways to corrupt databases that lead to
    to segfaults.  There are no outstanding issues in this
    area that I am aware of, but it has historically been
    a weakness.