Documentation Source Text

<h2>9.0 Things That Can Go Wrong</h2>

<p>The atomic commit mechanism in SQLite has proven to be robust,
but it can be circumvented by a sufficiently creative
adversary or a sufficiently broken operating system implementation.
This section describes a few of the ways in which an SQLite database
might be corrupted by a power failure or system crash.
(See also: [How To Corrupt Your Database Files].)</p>

<tcl>hd_fragment brokenlocks</tcl>
<h3>9.1 Broken Locking Implementations</h3>

<p>SQLite uses filesystem locks to make sure that only one
process and database connection is trying to modify the database
at a time.  The filesystem locking mechanism is implemented

<h2>9.0 Things That Can Go Wrong</h2>

<p>The atomic commit mechanism in SQLite has proven to be robust,
but it can be circumvented by a sufficiently creative
adversary or a sufficiently broken operating system implementation.
This section describes a few of the ways in which an SQLite database
might be corrupted by a power failure or system crash.
(See also: [how to corrupt | How To Corrupt Your Database Files].)</p>

<tcl>hd_fragment brokenlocks</tcl>
<h3>9.1 Broken Locking Implementations</h3>

<p>SQLite uses filesystem locks to make sure that only one
process and database connection is trying to modify the database
at a time.  The filesystem locking mechanism is implemented

    according to the specified collation and that the comparisons associate with
    the compound query use the native collation.  Ticket
    [http://www.sqlite.org/src/info/6709574d2a8d8 | 6709574d2a8d8].
<li>Bug fix: Makes sure the [sqlite3_set_authorizer | authorizer] callback gets
    a valid pointer to the string "ROWID" for the column-name parameter when
    doing an [UPDATE] that changes the rowid.  Ticket
    [http://www.sqlite.org/src/info/0eb70d77cb05bb2272 | 0eb70d77cb05bb2272]



}

chng {2013-04-12 (3.7.16.2)} {
<li>Fix a bug (present since version 3.7.13) that could result in database corruption
    on windows if two or more processes try to access the same database file at the
    same time and immediately after third process crashed in the middle of committing
    to that same file.  See ticket 

    according to the specified collation and that the comparisons associate with
    the compound query use the native collation.  Ticket
    [http://www.sqlite.org/src/info/6709574d2a8d8 | 6709574d2a8d8].
<li>Bug fix: Makes sure the [sqlite3_set_authorizer | authorizer] callback gets
    a valid pointer to the string "ROWID" for the column-name parameter when
    doing an [UPDATE] that changes the rowid.  Ticket
    [http://www.sqlite.org/src/info/0eb70d77cb05bb2272 | 0eb70d77cb05bb2272]
<li>Bug fix: Do not move WHERE clause terms inside OR expressions that are
    contained within an ON clause of a LEFT JOIN.  Ticket 
    [http://www.sqlite.org/src/info/f2369304e4 | f2369304e4]
}

chng {2013-04-12 (3.7.16.2)} {
<li>Fix a bug (present since version 3.7.13) that could result in database corruption
    on windows if two or more processes try to access the same database file at the
    same time and immediately after third process crashed in the middle of committing
    to that same file.  See ticket 

Internet searches such as "fake capacity usb" will turn up lots of
disturbing information about this problem.

<h2>5.0 Memory corruption</h2>

<p>SQLite is a C-library that runs in the same address space as the 
application that it serves.  That means that stray pointers, buffer
overruns, heap corruption, or other malfunctions in application can
corrupt internal SQLite data structure and ultimately result in a
corrupt database file.  Normally these kinds of problems manifest themselves
as segfaults prior to any database corruption occurring, but there have
been instances where application code errors have caused SQLite to
malfunction subtly so as to corrupt the database file rather than
panicking.</p>








<h2>6.0 Other operating system problems</h2>

<p>Sometimes operating systems will exhibit non-standard behavior which
can lead to problems.  Sometimes this non-standard behavior is deliberate,
and sometimes it is a mistake in the implementation.  But in any event,
if the operating performs differently from they way SQLite expects it to
perform, the possibility of database corruption exists.</p>
................................................................................
the memory obtained from the first mmap() call to be zeroed.  SQLite on
unix uses mmap() to create a shared memory region for transaction 
coordination in [WAL | WAL mode], and it will call mmap() multiple times
for large transactions.  The QNX mmap() has been demonstrated to corrupt
database file under that scenario.  QNX engineers are aware of this problem
and are working on a solution; the problem may have already been fixed by
the time you read this.</p>






<h2>7.0 Bugs in SQLite</h2>

<p>SQLite is [testing | very carefully tested] to help ensure that it is
as bug-free as possible.  Among the many tests that are carried out for
every SQLite version are tests that simulate power failures, I/O errors,
and out-of-memory (OOM) errors and verify that no database corrupt occurs

Internet searches such as "fake capacity usb" will turn up lots of
disturbing information about this problem.

<h2>5.0 Memory corruption</h2>

<p>SQLite is a C-library that runs in the same address space as the 
application that it serves.  That means that stray pointers, buffer
overruns, heap corruption, or other malfunctions in the application can
corrupt internal SQLite data structure and ultimately result in a
corrupt database file.  Normally these kinds of problems manifest themselves
as segfaults prior to any database corruption occurring, but there have
been instances where application code errors have caused SQLite to
malfunction subtly so as to corrupt the database file rather than
panicking.</p>

<p>The memory corruption problem becomes more acute when
using [memory-mapped I/O].
When all or part of the database file is mapped into the application's
address space, then a stray pointer the overwrites any part of that
mapped space will immediately corrupt the database file, without
requiring the application to do a subsequent write() system call.</p>

<h2>6.0 Other operating system problems</h2>

<p>Sometimes operating systems will exhibit non-standard behavior which
can lead to problems.  Sometimes this non-standard behavior is deliberate,
and sometimes it is a mistake in the implementation.  But in any event,
if the operating performs differently from they way SQLite expects it to
perform, the possibility of database corruption exists.</p>
................................................................................
the memory obtained from the first mmap() call to be zeroed.  SQLite on
unix uses mmap() to create a shared memory region for transaction 
coordination in [WAL | WAL mode], and it will call mmap() multiple times
for large transactions.  The QNX mmap() has been demonstrated to corrupt
database file under that scenario.  QNX engineers are aware of this problem
and are working on a solution; the problem may have already been fixed by
the time you read this.</p>

<p>When running on QNX, it is recommended that [memory-mapped I/O] never
be used.  Furthermore, to use [WAL mode], it is recommended that applications
employ the [locking_mode | exclusive locking mode] in order to 
use [WAL without shared memory].

<h2>7.0 Bugs in SQLite</h2>

<p>SQLite is [testing | very carefully tested] to help ensure that it is
as bug-free as possible.  Among the many tests that are carried out for
every SQLite version are tests that simulate power failures, I/O errors,
and out-of-memory (OOM) errors and verify that no database corrupt occurs

contains [testcase macros] to verify that both sides of each boundary
have been tested.</p>

<tcl>hd_fragment regressiontesting</tcl>
<h2>5.0 Regression Testing</h2>

<p>Whenever a bug is reported against SQLite, that bug is not considered
fixed until new test cases have been added to the TCL test suite which
would exhibit the bug in an unpatched version of SQLite.  Over the years,

this has resulted in thousands and thousands of new tests being added
to the TCL test suite.  These regression tests ensure that bugs that have
been fixed in the past are not reintroduced into future versions of
SQLite.</p>

<tcl>hd_fragment leakcheck</tcl>
<h2>6.0 Automatic Resource Leak Detection</h2>

<p>Resource leak occurs when system resources
................................................................................
quickly.  SQLite is designed to never leak memory, even after
an exception such as an OOM error or disk I/O error.  The test
harnesses are zealous to enforce this.</p>

<tcl>hd_fragment coverage {test coverage}</tcl>
<h2>7.0 Test Coverage</h2>

<p>The SQLite core has 100% branch test coverage under [TH3] as of
2009-07-25, in its default configuration as measured by
[http://gcc.gnu.org/onlinedocs/gcc/Gcov.html | gcov]
utility on SuSE Linux 10.1 on x86 hardware with the GCC 4.0.1 compiler.</p>

<p>The "SQLite core" in the previous paragraph excludes the
operating-system dependent [VFS] backends, since it is
not possible to write cross-platform tests for those modules.  Extensions
such as FTS3 and RTree are also excluded from the analysis.</p>

<tcl>hd_fragment stmtvbr</tcl>

contains [testcase macros] to verify that both sides of each boundary
have been tested.</p>

<tcl>hd_fragment regressiontesting</tcl>
<h2>5.0 Regression Testing</h2>

<p>Whenever a bug is reported against SQLite, that bug is not considered
fixed until new test cases that would exhibit the bug have been added 
to either the TCL or TH3 test suites.
Over the years,
this has resulted in thousands and thousands of new tests.
These regression tests ensure that bugs that have
been fixed in the past are not reintroduced into future versions of
SQLite.</p>

<tcl>hd_fragment leakcheck</tcl>
<h2>6.0 Automatic Resource Leak Detection</h2>

<p>Resource leak occurs when system resources
................................................................................
quickly.  SQLite is designed to never leak memory, even after
an exception such as an OOM error or disk I/O error.  The test
harnesses are zealous to enforce this.</p>

<tcl>hd_fragment coverage {test coverage}</tcl>
<h2>7.0 Test Coverage</h2>

<p>The SQLite core has 100% branch test coverage under [TH3] in
its default configuration as measured by
[http://gcc.gnu.org/onlinedocs/gcc/Gcov.html | gcov].</p>


<p>The "SQLite core" in the previous paragraph excludes the
operating-system dependent [VFS] backends, since it is
not possible to write cross-platform tests for those modules.  Extensions
such as FTS3 and RTree are also excluded from the analysis.</p>

<tcl>hd_fragment stmtvbr</tcl>

a WAL database without using shared memory if that
process is guaranteed to be the only process accessing the database.
^This feature allows WAL databases to be created, read, and written
by legacy [VFSes] that lack the "version 2" shared-memory
methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the
[sqlite3_io_methods] object.</p>


<p>^(If EXCLUSIVE locking mode is set prior to the first WAL-mode 
database access, then SQLite never attempts to call any of the
shared-memory methods and hence no shared-memory
wal-index is ever created.)^
^(In that case, the database connection remains in EXCLUSIVE mode
as long as the journal mode is WAL; attempts to change the locking
mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.)^
^The only way to change out of EXCLUSIVE locking mode is to first

a WAL database without using shared memory if that
process is guaranteed to be the only process accessing the database.
^This feature allows WAL databases to be created, read, and written
by legacy [VFSes] that lack the "version 2" shared-memory
methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the
[sqlite3_io_methods] object.</p>

<p>^(If [locking_mode | EXCLUSIVE locking mode]
is set prior to the first WAL-mode 
database access, then SQLite never attempts to call any of the
shared-memory methods and hence no shared-memory
wal-index is ever created.)^
^(In that case, the database connection remains in EXCLUSIVE mode
as long as the journal mode is WAL; attempts to change the locking
mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.)^
^The only way to change out of EXCLUSIVE locking mode is to first