Documentation Source Text

Check-in [bc4e6344c0]
Login

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

Overview
Comment:New warnings about the possibility of corruption the database when certain pragmas are used.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bc4e6344c076b668b3f72928e674fa870b24b7bf
User & Date: drh 2016-09-26 16:02:31
Context
2016-09-26
20:35
Add the "Recommended Compile-time Options" section to the compile.html document. check-in: 67fa7525c1 user: drh tags: trunk
16:02
New warnings about the possibility of corruption the database when certain pragmas are used. check-in: bc4e6344c0 user: drh tags: trunk
13:16
Update the change log to show the 2% performance gain over 3.14.2. check-in: bf03335759 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/howtocorrupt.in.

396
397
398
399
400
401
402


























403
404
405
406
407
408
409
are very reliable, but errors do still occur.  For example, on 2013-10-01
the SQLite database that holds the
<a href="http://wiki.tcl-lang.org/">Wiki for Tcl/Tk</a> went corrupt a few days
after the host computer was moved to a dodgy build of the (linux) kernel
that had issues in the filesystem layer.  In that event, the filesystem
eventually became so badly corrupted that the machine was unusable, but
the earliest symptom of trouble was the corrupted SQLite database.</p>



























<h1> Bugs in SQLite</h1>

<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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
are very reliable, but errors do still occur.  For example, on 2013-10-01
the SQLite database that holds the
<a href="http://wiki.tcl-lang.org/">Wiki for Tcl/Tk</a> went corrupt a few days
after the host computer was moved to a dodgy build of the (linux) kernel
that had issues in the filesystem layer.  In that event, the filesystem
eventually became so badly corrupted that the machine was unusable, but
the earliest symptom of trouble was the corrupted SQLite database.</p>

<tcl>hd_fragment cfgerr cfgerrors*</tcl>
<h1>SQLite Configuration Errors</h1>

<p>SQLite has many built-in protections against database corruption.
But many of these protections can be disabled by configuration options.
If protections are disabled, database corruption may occur.

<p>The following are examples of disabling the built-in protections
mechanisms of SQLite:

<ul>
<li><p>Setting [PRAGMA synchronous=OFF] can cause the database to
go corrupt if there is an operating-system crass or power failure,
though this setting is safe from damage due to application crashes.

<li><p>Changing the [PRAGMA schema_version] while other database
connections are open.

<li><p>Using [PRAGMA journal_mode=OFF] or [PRAGMA journal_mode=MEMORY]
and taking an application crash in the middle of a write transaction.

<li><p>Setting [PRAGMA writable_schema=ON] and then changing the
database schema using DML statements can render the database completely
unreadable, if not done carefully.
</ul>

<h1> Bugs in SQLite</h1>

<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/lang.in.

4906
4907
4908
4909
4910
4911
4912

4913
4914

<ol>
<tcl>
set lx {}
foreach word [lsort $keyword_list] {
  hd_putsnl "<li>$word</li>"
}

</tcl>
</ol>)^







>

<
4906
4907
4908
4909
4910
4911
4912
4913
4914


<ol>
<tcl>
set lx {}
foreach word [lsort $keyword_list] {
  hd_putsnl "<li>$word</li>"
}
hd_putsnl "</ol>)^"
</tcl>

Changes to pages/pragma.in.

15
16
17
18
19
20
21



22
23
24
25
26
27
28
..
39
40
41
42
43
44
45








46
47
48
49
50
51
52
..
66
67
68
69
70
71
72





73
74
75
76
77
78
79
...
149
150
151
152
153
154
155


156
157
158
159
160
161
162
...
591
592
593
594
595
596
597
598

599
600
601
602
603
604
605
...
609
610
611
612
613
614
615
616

617
618
619
620
621
622
623
...
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
....
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331















1332
1333
1334
1335
1336
1337
1338
....
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549

1550
1551
1552
1553
1554
1555
1556
1557
1558
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>schema.</i><b>} content



  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
................................................................................
proc DebugDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    This pragma is intended for use when debugging SQLite itself.  It
    is only available when the [SQLITE_DEBUG] compile-time option
    is used.</p>
  }








}
proc TestDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    The intended use of this pragma is only for testing and validation of
    SQLite.  This pragma is subject to change without notice and is not
    recommended for use by application programs.</p>
................................................................................
}
# Testing pragmas
proc TestPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [TestDisclaimer]] $content]
  global PragmaTest
  foreach x $namelist {set PragmaTest($x) 1}
}






</tcl>

<p>The PRAGMA statement is an SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
................................................................................
       [application file-format] should set the Application ID integer to
       a unique integer so that utilities such as 
       [http://www.darwinsys.com/file/ | file(1)] can determine the specific
       file type rather than just reporting "SQLite3 Database".  A list of
       assigned application IDs can be seen by consulting the
       [http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt
        |magic.txt] file in the SQLite source repository.


}

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;
     <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>

    <p>Query, set, or clear the [automatic indexing] capability.)^
................................................................................
    the first block of a file with zeros.  See also:
    [PRAGMA journal_size_limit] and [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT].</p>

    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>


    <p>^The WAL journaling mode uses a [write-ahead log] instead of a
    rollback journal to implement transactions.  ^The WAL journaling mode
    is persistent; after being set it stays in effect
    across multiple database connections and after closing and
    reopening the database.  A database in WAL journaling mode
    can only be accessed by SQLite [version 3.7.0] ([dateof:3.7.0])
................................................................................
    ^No rollback journal is ever created and hence there is never a rollback
    journal to delete.  The OFF journaling mode disables the atomic
    commit and rollback capabilities of SQLite. The [ROLLBACK] command
    no longer works; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.
    ^If the application crashes
    in the middle of a transaction when the OFF journaling mode is
    set, then the database file will very likely go corrupt.</p>


    <p>^Note that the journal_mode for an [in-memory database]
    is either MEMORY or OFF and can not be changed to a different value.
    ^An attempt to change the journal_mode of an [in-memory database] to
    any setting other than MEMORY or OFF is ignored.  ^Note also that
    the journal_mode cannot be changed while a transaction is active.</p>
}
................................................................................
    a catastrophic disk failure or some other unrecoverable hardware
    fault.  Many applications choose NORMAL when in [WAL mode].</dd>
    <dt><b>OFF</b> (0)</dt>
    <dd>
    ^With synchronous OFF (0), SQLite continues without syncing
    as soon as it has handed data off to the operating system.
    If the application running SQLite crashes, the data will be safe, but
    the database might become corrupted if the operating system
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, commits can be orders of
    magnitude faster with synchronous OFF.
    </dd></dl>
    </p>
 
    <p>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
................................................................................
    value for the column.  ^The "pk" column in the result set is zero
    for columns that are not part of the primary key, and is the index of
    the column in the primary key for columns that are part of the primary
    key.</p>
    <p>^The table named in the table_info pragma can also be a view.</p>
}

Pragma {schema_version user_version} {
    <p><b>PRAGMA DB.schema_version; 
      <br>PRAGMA DB.schema_version = </b><i>integer </i><b>;
      <br>PRAGMA DB.user_version;
      <br>PRAGMA DB.user_version = </b><i>integer </i><b>;</b>

  
<p>    ^The pragmas schema_version and user_version are used to set or get
       the value of the schema-version and user-version, respectively. ^(The
       schema-version and the user-version are big-endian 32-bit signed
       integers stored in the database header at offsets 40 and 60,
       respectively.)^</p>
  
<p>    ^(The schema-version is usually only manipulated internally by SQLite.  
       It is incremented by SQLite whenever the database schema is modified 
       (by creating or dropping a table or index).)^ ^The schema version is 
       used by SQLite each time a query is executed to ensure that the 
       internal cache of the schema used when compiling the SQL query matches 
       the schema of the database against which the compiled query is actually 
       executed.  ^Subverting this mechanism by using "PRAGMA schema_version" 
       to modify the schema-version is potentially dangerous and may lead 
       to program crashes or database corruption. Use with caution!</p>
  
<p>    The user-version is not used internally by SQLite. It may be used by
       applications for any purpose.</p>















}

Pragma compile_options {
    <p><b>PRAGMA compile_options;</b></p>
    <p>^This pragma returns the names of [compile-time options] used when
    building SQLite, one option per row.  ^The "SQLITE_" prefix is omitted
    from the returned option names.  See also the
................................................................................
    <p>^(<b>PRAGMA ignore_check_constraints  = </b><i>boolean</i><b>;</b></p>

    <p>This pragma enables or disables the enforcement of CHECK constraints.)^
    ^The default setting is off, meaning that CHECK constraints are
    enforced by default.</p>
}

Pragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, the SQLITE_MASTER tables in which database
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]

    statements.)^  ^Warning:  misuse of this pragma can easily result in
    a corrupt database file.</p>
}

Section {List Of PRAGMAs} {toc} {{pragma list}}
set lx {}
foreach prag [array names PragmaRef] {
  set ref $PragmaRef($prag)
  if {[info exists PragmaLegacy($prag)]} {







>
>
>







 







>
>
>
>
>
>
>
>







 







>
>
>
>
>







 







>
>







 







|
>







 







|
>







 







|







 







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|




>
|
|







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
..
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
..
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
...
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
...
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
....
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
....
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
....
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
unset -nocomplain PragmaBody PragmaRef PragmaDud PragmaKeys

# Each pragma is recorded by invoking this procedure.
proc Pragma {namelist content} {
  global PragmaBody PragmaRef PragmaKeys
  set main_name [lindex $namelist 0]
  regsub -all {PRAGMA DB\.} $content {PRAGMA </b><i>schema.</i><b>} content
  regsub -all {<warning>} $content \
      {<span style='background-color: #ffff60;'>} content
  regsub -all {</warning>} $content </span> content
  set PragmaBody($main_name) $content
  set PragmaKeys($main_name) $namelist
  foreach x $namelist {
    set PragmaRef($x) $main_name
  }
}
proc LegacyDisclaimer {} {
................................................................................
proc DebugDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    This pragma is intended for use when debugging SQLite itself.  It
    is only available when the [SQLITE_DEBUG] compile-time option
    is used.</p>
  }
}
proc DangerDisclaimer {} {
  return {
    <p><span style='background-color: #ffff60;'>
    <b>Warning:</b>
    Misuse of this pragma can result in [cfgerrors|database corruption].
    </span></p>
  }
}
proc TestDisclaimer {} {
  return {
    <p style='background-color: #f0e0ff;'>
    The intended use of this pragma is only for testing and validation of
    SQLite.  This pragma is subject to change without notice and is not
    recommended for use by application programs.</p>
................................................................................
}
# Testing pragmas
proc TestPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [TestDisclaimer]] $content]
  global PragmaTest
  foreach x $namelist {set PragmaTest($x) 1}
}
proc DangerousPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [DangerDisclaimer]] $content]
  global PragmaTest
  foreach x $namelist {set PragmaTest($x) 1}
}

</tcl>

<p>The PRAGMA statement is an SQL extension specific to SQLite and used to 
modify the operation of the SQLite library or to query the SQLite library for 
internal (non-table) data. The PRAGMA statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
................................................................................
       [application file-format] should set the Application ID integer to
       a unique integer so that utilities such as 
       [http://www.darwinsys.com/file/ | file(1)] can determine the specific
       file type rather than just reporting "SQLite3 Database".  A list of
       assigned application IDs can be seen by consulting the
       [http://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt
        |magic.txt] file in the SQLite source repository.

<p>   See also the [user_version pragma].
}

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;
     <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>

    <p>Query, set, or clear the [automatic indexing] capability.)^
................................................................................
    the first block of a file with zeros.  See also:
    [PRAGMA journal_size_limit] and [SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT].</p>

    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely 
    [cfgerrors|go corrupt].</p>

    <p>^The WAL journaling mode uses a [write-ahead log] instead of a
    rollback journal to implement transactions.  ^The WAL journaling mode
    is persistent; after being set it stays in effect
    across multiple database connections and after closing and
    reopening the database.  A database in WAL journaling mode
    can only be accessed by SQLite [version 3.7.0] ([dateof:3.7.0])
................................................................................
    ^No rollback journal is ever created and hence there is never a rollback
    journal to delete.  The OFF journaling mode disables the atomic
    commit and rollback capabilities of SQLite. The [ROLLBACK] command
    no longer works; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.
    ^If the application crashes
    in the middle of a transaction when the OFF journaling mode is
    set, then the database file will very likely
    [cfgerrors|go corrupt].</p>

    <p>^Note that the journal_mode for an [in-memory database]
    is either MEMORY or OFF and can not be changed to a different value.
    ^An attempt to change the journal_mode of an [in-memory database] to
    any setting other than MEMORY or OFF is ignored.  ^Note also that
    the journal_mode cannot be changed while a transaction is active.</p>
}
................................................................................
    a catastrophic disk failure or some other unrecoverable hardware
    fault.  Many applications choose NORMAL when in [WAL mode].</dd>
    <dt><b>OFF</b> (0)</dt>
    <dd>
    ^With synchronous OFF (0), SQLite continues without syncing
    as soon as it has handed data off to the operating system.
    If the application running SQLite crashes, the data will be safe, but
    the database [cfgerrors|might become corrupted] if the operating system
    crashes or the computer loses power before that data has been written
    to the disk surface.  On the other hand, commits can be orders of
    magnitude faster with synchronous OFF.
    </dd></dl>
    </p>
 
    <p>^In [WAL] mode when synchronous is NORMAL (1), the WAL file is
................................................................................
    value for the column.  ^The "pk" column in the result set is zero
    for columns that are not part of the primary key, and is the index of
    the column in the primary key for columns that are part of the primary
    key.</p>
    <p>^The table named in the table_info pragma can also be a view.</p>
}

DangerousPragma {schema_version} {
    <p><b>PRAGMA DB.schema_version; 
      <br>PRAGMA DB.schema_version = </b><i>integer </i>;

<p>   ^The schema_version pragma will to get or set
       the value of the schema-version integer at offset 40 in the
       [database header]. 

<p>    ^SQLite automatically increments the schema-version whenever the
       schema changes. ^As each SQL statement runs, the schema version is
       checked to ensure that the schema has not changed since the SQL
       statement was [sqlite3_prepare|prepared].
       ^Subverting this mechanism by using "PRAGMA schema_version"
       my cause SQL statement to run using an obsolete schema,
       which can lead to incorrect answers and/or
       [cfgerrors|database corruption].

       DISCLAIMER

<p>    ^For the purposes of this pragma, the [VACUUM] command is considered
       a schema change, since [VACUUM] will usual alter the "rootpage"
       values for entries in the [sqlite_master table].


<p>    See also the [application_id pragma] and [user_version pragma].
}


Pragma {user_version} {
      <p><b>PRAGMA DB.user_version;
      <br>PRAGMA DB.user_version = </b><i>integer </i><b>;</b>

  
<p>    ^The user_version pragma will to get or set
       the value of the user-version integer at offset 60 in the
       [database header].  The user-version is an integer that is
       available to applications to use however they want.  SQLite
       makes no use of the user-version itself.

<p>    See also the [application_id pragma] and [schema_version pragma].
}

Pragma compile_options {
    <p><b>PRAGMA compile_options;</b></p>
    <p>^This pragma returns the names of [compile-time options] used when
    building SQLite, one option per row.  ^The "SQLITE_" prefix is omitted
    from the returned option names.  See also the
................................................................................
    <p>^(<b>PRAGMA ignore_check_constraints  = </b><i>boolean</i><b>;</b></p>

    <p>This pragma enables or disables the enforcement of CHECK constraints.)^
    ^The default setting is off, meaning that CHECK constraints are
    enforced by default.</p>
}

DangerousPragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, the SQLITE_MASTER tables in which database
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.)^  ^<warning><b>Warning:</b>
    misuse of this pragma can easily result in
    a [cfgerrors|corrupt database file].</warning></p>
}

Section {List Of PRAGMAs} {toc} {{pragma list}}
set lx {}
foreach prag [array names PragmaRef] {
  set ref $PragmaRef($prag)
  if {[info exists PragmaLegacy($prag)]} {