Documentation Source Text

Check-in [af7d790e13]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved hyperlinks on various documents.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: af7d790e1309581f78d6eecdc20823cc5a871269
User & Date: drh 2013-05-14 02:37:04.346
Context
2013-05-14
11:12
Fix a typo in queryplanner.html. (check-in: d52ed00775 user: drh tags: trunk)
02:37
Improved hyperlinks on various documents. (check-in: af7d790e13 user: drh tags: trunk)
2013-05-08
18:04
Update the change log. (check-in: f88ad9fa6a user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/atomiccommit.in.
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
<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







|







1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
<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
Changes to pages/changes.in.
104
105
106
107
108
109
110



111
112
113
114
115
116
117
    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 







>
>
>







104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
    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 
Changes to pages/howtocorrupt.in.
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309







310
311
312
313
314
315
316
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>







|







>
>
>
>
>
>
>







295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
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>
336
337
338
339
340
341
342





343
344
345
346
347
348
349
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







>
>
>
>
>







343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
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
Changes to pages/testing.in.
385
386
387
388
389
390
391
392

393
394
395
396
397
398
399
400
401
402
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







|
>
|
|
|







385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
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
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
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>







|
|
|
<







414
415
416
417
418
419
420
421
422
423

424
425
426
427
428
429
430
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>
Changes to pages/wal.in.
380
381
382
383
384
385
386

387
388
389
390
391
392
393
394
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







>
|







380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
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