diff options
Diffstat (limited to 'www/howtocorrupt.html')
| -rw-r--r-- | www/howtocorrupt.html | 813 |
1 files changed, 813 insertions, 0 deletions
diff --git a/www/howtocorrupt.html b/www/howtocorrupt.html new file mode 100644 index 0000000..51a089d --- /dev/null +++ b/www/howtocorrupt.html @@ -0,0 +1,813 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>How To Corrupt An SQLite Database File</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +How To Corrupt An SQLite Database File +</div> +<div class="fancy_toc"> +<a onclick="toggle_toc()"> +<span class="fancy_toc_mark" id="toc_mk">►</span> +Table Of Contents +</a> +<div id="toc_sub"><div class="fancy-toc1"><a href="#_file_overwrite_by_a_rogue_thread_or_process">1. File overwrite by a rogue thread or process</a></div> +<div class="fancy-toc2"><a href="#_continuing_to_use_a_file_descriptor_after_it_has_been_closed">1.1. Continuing to use a file descriptor after it has been closed</a></div> +<div class="fancy-toc2"><a href="#_backup_or_restore_while_a_transaction_is_active">1.2. Backup or restore while a transaction is active</a></div> +<div class="fancy-toc2"><a href="#_deleting_a_hot_journal">1.3. Deleting a hot journal</a></div> +<div class="fancy-toc2"><a href="#_mispairing_database_files_and_hot_journals_">1.4. Mispairing database files and hot journals </a></div> +<div class="fancy-toc1"><a href="#_file_locking_problems">2. File locking problems</a></div> +<div class="fancy-toc2"><a href="#_filesystems_with_broken_or_missing_lock_implementations">2.1. Filesystems with broken or missing lock implementations</a></div> +<div class="fancy-toc2"><a href="#_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_">2.2. Posix advisory locks canceled by a separate thread doing close()</a></div> +<div class="fancy-toc3"><a href="#multiple_copies_of_sqlite_linked_into_the_same_application">2.2.1. Multiple copies of SQLite linked into the same application</a></div> +<div class="fancy-toc2"><a href="#_two_processes_using_different_locking_protocols">2.3. Two processes using different locking protocols</a></div> +<div class="fancy-toc2"><a href="#_unlinking_or_renaming_a_database_file_while_in_use">2.4. Unlinking or renaming a database file while in use</a></div> +<div class="fancy-toc2"><a href="#_multiple_links_to_the_same_file">2.5. Multiple links to the same file</a></div> +<div class="fancy-toc2"><a href="#_carrying_an_open_database_connection_across_a_fork_">2.6. Carrying an open database connection across a fork() </a></div> +<div class="fancy-toc1"><a href="#_failure_to_sync">3. Failure to sync</a></div> +<div class="fancy-toc2"><a href="#_disk_drives_that_do_not_honor_sync_requests">3.1. Disk drives that do not honor sync requests</a></div> +<div class="fancy-toc2"><a href="#_disabling_sync_using_pragmas">3.2. Disabling sync using PRAGMAs</a></div> +<div class="fancy-toc1"><a href="#_disk_drive_and_flash_memory_failures">4. Disk Drive and Flash Memory Failures</a></div> +<div class="fancy-toc2"><a href="#_non_powersafe_flash_memory_controllers">4.1. Non-powersafe flash memory controllers</a></div> +<div class="fancy-toc2"><a href="#_fake_capacity_usb_sticks">4.2. Fake capacity USB sticks</a></div> +<div class="fancy-toc1"><a href="#_memory_corruption">5. Memory corruption</a></div> +<div class="fancy-toc1"><a href="#_other_operating_system_problems">6. Other operating system problems</a></div> +<div class="fancy-toc2"><a href="#_linux_threads">6.1. Linux Threads</a></div> +<div class="fancy-toc2"><a href="#_failures_of_mmap_on_qnx">6.2. Failures of mmap() on QNX</a></div> +<div class="fancy-toc2"><a href="#_filesystem_corruption">6.3. Filesystem Corruption</a></div> +<div class="fancy-toc1"><a href="#sqlite_configuration_errors">7. SQLite Configuration Errors</a></div> +<div class="fancy-toc1"><a href="#_bugs_in_sqlite">8. Bugs in SQLite</a></div> +<div class="fancy-toc2"><a href="#_false_corruption_reports_due_to_database_shrinkage">8.1. False corruption reports due to database shrinkage</a></div> +<div class="fancy-toc2"><a href="#_corruption_following_switches_between_rollback_and_wal_modes">8.2. Corruption following switches between rollback and WAL modes</a></div> +<div class="fancy-toc2"><a href="#_i_o_error_while_obtaining_a_lock_leads_to_corruption">8.3. I/O error while obtaining a lock leads to corruption</a></div> +<div class="fancy-toc2"><a href="#_database_pages_leak_from_the_free_page_list">8.4. Database pages leak from the free page list</a></div> +<div class="fancy-toc2"><a href="#_corruption_following_alternating_writes_from_3_6_and_3_7">8.5. Corruption following alternating writes from 3.6 and 3.7</a></div> +<div class="fancy-toc2"><a href="#_race_condition_in_recovery_on_windows_systems">8.6. Race condition in recovery on Windows systems</a></div> +<div class="fancy-toc2"><a href="#_boundary_value_error_in_the_secondary_journals_used_by_nested_transactions">8.7. Boundary value error in the secondary journals used by nested transactions</a></div> +</div> +</div> +<script> +function toggle_toc(){ +var sub = document.getElementById("toc_sub") +var mk = document.getElementById("toc_mk") +if( sub.style.display!="block" ){ +sub.style.display = "block"; +mk.innerHTML = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</script> +</div> + + + + +<h2 style="margin-left:1.0em" notoc="1" id="overview"> Overview</h2> + +<p>An SQLite database is highly resistant to corruption. +If an application crash, or an operating-system crash, or even +a power failure occurs in the middle of a transaction, the partially +written transaction should be automatically rolled back the next time +the database file is accessed. The recovery process is fully +automatic and does not require any action on the part of the user +or the application. +</p> + +<p>Though SQLite is resistant to database corruption, it is not immune. +This document describes the various ways that an SQLite database might +go corrupt.</p> + +<h1 id="_file_overwrite_by_a_rogue_thread_or_process"><span>1. </span> File overwrite by a rogue thread or process</h1> + +<p>SQLite database files are ordinary disk files. +That means that any process can open the file and +overwrite it with garbage. There is nothing that the SQLite +library can do to defend against this.</p> + +<a name="stalefd"></a> + +<h2 id="_continuing_to_use_a_file_descriptor_after_it_has_been_closed"><span>1.1. </span> Continuing to use a file descriptor after it has been closed</h2> + +<p>We have seen multiple cases where a file descriptor was open on a file, +then that file descriptor was closed and reopened on an SQLite database. +Later, some other thread continued to write into the +old file descriptor, not realizing that the original file had been closed +already. But because the file descriptor had been reopened by SQLite, +the information that was intended to go into the original file ended up +overwriting parts of the SQLite database, leading to corruption of the +database.</p> + +<p>One example of this occurred circa 2013-08-30 on the canonical repository +for the <a href="http://www.fossil-scm.org/">Fossil DVCS</a>. In that event, +file descriptor 2 (standard error) was being erroneously closed (by +<a href="http://www.stunnel.org/">stunnel</a>, we suspect) prior to +<a href="c3ref/open.html">sqlite3_open_v2()</a> so that the file descriptor used for the +repository database file was 2. Later, an application +bug caused an assert() statement to emit +an error message by invoking write(2,...). But since file descriptor 2 was +now connected to a database file, the error message +overwrote part of the database. To guard against this kind of problem, +SQLite <a href="releaselog/3_8_1.html">version 3.8.1</a> (2013-10-17) +and later refuse to use low-numbered file descriptors +for database files. +(See <a href="compile.html#minimum_file_descriptor">SQLITE_MINIMUM_FILE_DESCRIPTOR</a> for additional information.)</p> + +<p>Another example of corruption caused by using a closed file +descriptor was +<a href="https://code.facebook.com/posts/313033472212144/debugging-file-corruption-on-ios/">reported by facebook engineers</a> in a blog post on 2014-08-12.</p> + +<p>Another example of this error was reported against +<a href="https://fossil-scm.org/">Fossil</a> on 2019-07-11. A file descriptor would +be opened for debugging output, but then closed and reopened by SQLite. +But the debugging logic continued to write into the original file +descriptor. See the +<a href="https://fossil-scm.org/forum/forumpost/c51b9a1169">forum discussion</a> +for the bug report and a link to the fix. + +</p><h2 id="_backup_or_restore_while_a_transaction_is_active"><span>1.2. </span> Backup or restore while a transaction is active</h2> + +<p>Systems that run automatic backups in the background might try to +make a backup copy of an SQLite database file while it is in the middle +of a transaction. The backup copy then might contain some old and some +new content, and thus be corrupt.</p> + +<p>The best approach to make reliable backup copies of an SQLite database +is to make use of the <a href="backup.html">backup API</a> that is part of the SQLite library. +Failing that, it is safe to make a copy of an SQLite database file as long +as there are no transactions in progress by any process. If the previous +transaction failed, then it is important that any rollback journal +(the <tt>*-journal</tt> file) or write-ahead log (the <tt>*-wal</tt> file) +be copied together with the database file itself.</p> + +<a name="delhotjrnl"></a> + +<h2 id="_deleting_a_hot_journal"><span>1.3. </span> Deleting a hot journal</h2> + +<p>SQLite normally stores all content in a single disk file. However, +while performing a transaction, information necessary to recover the +database following a crash or power failure is stored in auxiliary +journal files. Such journal files are described as <a href="fileformat2.html#hotjrnl">"hot"</a>. +The journal files have the same name as the +original database file with the addition +of <tt>-journal</tt> or <tt>-wal</tt> suffix.</p> + +<p>SQLite must see the journal files in order to recover from a crash +or power failure. If the <a href="fileformat2.html#hotjrnl">hot journal files</a> are moved, deleted, or renamed +after a crash or power failure, then automatic recovery will not work +and the database may go corrupt.</p> + +<p>Another manifestation of this problem is +<a href="shortnames.html#db83corrupt">database corruption caused by inconsistent use of 8+3 filenames</a>.</p> + +<a name="roguejrnl"></a> + +<h2 id="_mispairing_database_files_and_hot_journals_"><span>1.4. </span> Mispairing database files and hot journals </h2> + +<p> The previous example is a specific case of a more general problem: +The state of an SQLite database is controlled by both the +database file and the journal file. In a quiescent state, the journal +file does not exist and only the database file matters. +But if the journal file does exist, it must be +kept together with the database to avoid corruption. The following +actions are all likely to lead to corruption: +</p><ul> +<li> Swapping journal files between two different databases. +</li><li> Overwritting a journal file with a different journal file. +</li><li> Moving a journal file from one database to another. +</li><li> Copying a database file without also copying its journal. +</li><li> Overwriting a database file with another without also + deleting any hot journal associated with the original database. +</li></ul> + +<h1 id="_file_locking_problems"><span>2. </span> File locking problems</h1> + +<p>SQLite uses file locks on the database file, and on the +<a href="wal.html">write-ahead log</a> or <a href="wal.html">WAL</a> file, to coordinate access between concurrent +processes. Without coordination, two threads or processes might try +to make incompatible changes to a database file at the same time, +resulting in database corruption.</p> + +<h2 id="_filesystems_with_broken_or_missing_lock_implementations"><span>2.1. </span> Filesystems with broken or missing lock implementations</h2> + +<p>SQLite depends on the underlying filesystem to do locking as the +documentation says it will. But some filesystems contain bugs in their +locking logic such that the locks do not always behave as advertised. +This is especially true of network filesystems and NFS in particular. +If SQLite is used on a filesystem where the locking primitives contain +bugs, and if two or more threads or processes try to access the same +database at the same time, then database corruption might result.</p> + +<a name="posix_close_bug"></a> + +<h2 id="_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_"><span>2.2. </span> Posix advisory locks canceled by a separate thread doing close()</h2> + +<p>The default locking mechanism used by SQLite on unix platforms is +POSIX advisory locking. Unfortunately, POSIX advisory locking has design +quirks that make it prone to misuse and failure. In particular, any +thread in the same process with a file descriptor that is holding a POSIX +advisory lock can override that lock using a different file descriptor. +One particularly pernicious problem is that the <tt>close()</tt> system +call will cancel all POSIX advisory locks on the same file for all +threads and all file descriptors in the process.</p> + +<p>So, for example, suppose a multi-thread process has +two or more threads with separate SQLite database connections to the +same database file. Then a third thread comes along and wants to read +something out of that same database file on its own, without using the +SQLite library. +The third thread does an <tt>open()</tt>, a <tt>read()</tt> and then +a <tt>close()</tt>. +One would think this would be harmless. +But the <tt>close()</tt> system call caused the +locks held on the database by all the other threads to be dropped. Those +other threads have no way of knowing that their locks have just been +trashed (POSIX does not provide any mechanism to determine this) and so +they keep on running under the assumption that their locks are still valid. +This can lead to two or more threads or processes trying to write to the +database at the same time, resulting in database corruption.</p> + +<p>Note that it is perfectly safe for two or more threads to access the +same SQLite database file using the SQLite library. The unix drivers for +SQLite know about the POSIX advisory locking quirks and work around them. +This problem only arises when a thread tries to bypass the SQLite library +and read the database file directly.</p> + +<h3 id="multiple_copies_of_sqlite_linked_into_the_same_application"><span>2.2.1. </span>Multiple copies of SQLite linked into the same application</h3> + +<p>As pointed out in the previous paragraph, SQLite takes steps to work +around the quirks of POSIX advisory locking. Part of that work-around involves +keeping a global list (mutex protected) of open SQLite database files. +But, if multiple copies of SQLite are linked into the same application, +then there will be multiple instances of this global list. +Database connections opened using one copy of the SQLite library +will be unaware of database connections opened using the other copy, +and will be unable to work around the POSIX advisory locking quirks. +A <tt>close()</tt> operation on one connection might unknowingly +clear the locks on a different database connection, leading to database +corruption.</p> + +<p>The scenario above sounds far-fetched. +But the SQLite developers are aware of at +least one commercial product that was released +with exactly this bug. The vendor came to the SQLite developers seeking +help in tracking down some infrequent database corruption issues they were +seeing on Linux and Mac. The problem was eventually traced to the +fact that the application was linking against two separate copies of SQLite. +The solution was to change the application build procedures to link against +just one copy of SQLite instead of two.</p> + +<h2 id="_two_processes_using_different_locking_protocols"><span>2.3. </span> Two processes using different locking protocols</h2> + +<p>The default locking mechanism used by SQLite on unix platforms is +POSIX advisory locking, but there are other options. By selecting an +alternative <a href="c3ref/vfs.html">sqlite3_vfs</a> using the <a href="c3ref/open.html">sqlite3_open_v2()</a> interface, an +application can make use of other locking protocols that might be more +appropriate to certain filesystems. For example, dot-file locking might +be select for use in an application that has to run on an NFS filesystem +that does not support POSIX advisory locking.</p> + +<p>It is important that all connections to the same database file use +the same locking protocol. +If one application is using POSIX advisory locks and another application +is using dot-file locking, then the two applications will not see each +other's locks and will not be able to coordinate database access, possibly +leading to database corruption.</p> + +<a name="unlink"></a> + +<h2 id="_unlinking_or_renaming_a_database_file_while_in_use"><span>2.4. </span> Unlinking or renaming a database file while in use</h2> + +<p>If two processes have open connections to the same database file and +one process closes its connection, unlinks the file, then creates a new +database file in its place with the same name and reopens the new file, +then the two processes will be talking to different database files with +the same name. (Note that this is only possible on Posix and Posix-like +systems that permit a file to be unlinked while it is still open for +reading and writing. Windows does not allow this to occur.) +Since rollback journals and WAL files are based on the name of the database +file, the two different database files will share the same rollback +journal or WAL file. A rollback or recovery for one of the databases +might use content from the other database, resulting in corruption. +A similar problem occurs if a database file is renamed while it is +opened and a new file is created with the old name.</p> + +<p>In other words, unlinking or renaming an open database file +results in behavior that is undefined and probably undesirable.</p> + +<p>Beginning with SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20), +the unix OS interface will +send SQLITE_WARNING messages to the <a href="errlog.html">error log</a> if a database file is unlinked +while it is still in use.</p> + +<a name="alias"></a> + +<h2 id="_multiple_links_to_the_same_file"><span>2.5. </span> Multiple links to the same file</h2> + +<p>If a single database file has multiple links (either hard or soft links) +then that is just another way of saying that the file has multiple names. +If two or more processes open the database using different names, then +they will use different rollback journals and WAL files. That means that +if one process crashes, the other process will be unable to recover the +transaction in progress because it will be looking in the wrong place +for the appropriate journal.</p> + +<p>In other words, opening and using a database file that has two or +more names results in behavior that is undefined and probably undesirable.</p> + +<p>Beginning with SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20), +the unix OS interface will +send SQLITE_WARNING messages to the <a href="errlog.html">error log</a> if a database file has +multiple hard links.</p> + +<p>Beginning with SQLite <a href="releaselog/3_10_0.html">version 3.10.0</a> (2016-01-06), +the unix OS interface will +attempt to resolve symbolic links and open the database file by its +canonical name. Prior to version 3.10.0, opening a database file +through a symbolic link was similar to opening a database file +that had multiple hard links and resulted in undefined behavior.</p> + +<a name="fork"></a> + +<h2 id="_carrying_an_open_database_connection_across_a_fork_"><span>2.6. </span> Carrying an open database connection across a fork() </h2> + +<p> Do not open an SQLite database connection, then fork(), then try +to use that database connection in the child process. All kinds of +locking problems will result and you can easily end up with a corrupt +database. SQLite is not designed to support that kind of behavior. +Any database connection that is used in a child process must be opened +in the child process, not inherited from the parent. + +</p><p> Do not even call <a href="c3ref/close.html">sqlite3_close()</a> on a database connection from a +child process if the connection was opened in the parent. It is safe +to close the underlying file descriptor, but the <a href="c3ref/close.html">sqlite3_close()</a> +interface might invoke cleanup activities that will delete content out +from under the parent, leading to errors and perhaps even database +corruption. + +</p><h1 id="_failure_to_sync"><span>3. </span> Failure to sync</h1> + +<p>In order to guarantee that database files are always consistent, SQLite +will occasionally ask the operating system to flush all pending writes to +persistent storage then wait for that flush to complete. This is +accomplished using the <tt>fsync()</tt> system call under unix and +<tt>FlushFileBuffers()</tt> under Windows. We call this flush of +pending writes a "sync".</p> + +<p>Actually, if one is only concerned with atomic and consistent writes and +is willing to forego durable writes, the sync operation does not need +to wait until the content is completely stored on persistent media. Instead, +the sync operation can be thought of as an I/O barrier. As long as all +writes that occur before the sync are completed before any write that happens +after the sync, no database corruption will occur. If sync is operating as +an I/O barrier and not as a true sync, then a power failure or system crash +might cause one or more previously committed transactions to roll back +(in violation of the "durable" property of "ACID") but the database will at +least continue to be consistent, and that is what most people care about.</p> + +<h2 id="_disk_drives_that_do_not_honor_sync_requests"><span>3.1. </span> Disk drives that do not honor sync requests</h2> + +<p>Unfortunately, most consumer-grade mass storage devices lie about +syncing. Disk drives will report that content is safely on persistent +media as soon as it reaches the track buffer and before actually being +written to oxide. This makes the disk drives seem to operate faster +(which is vitally important to the manufacturer so that they can show +good benchmark numbers in trade magazines). And in fairness, the lie +normally causes no harm, as long as there is no power loss or hard reset +prior to the track buffer actually being written to oxide. But if a +power loss or hard reset does occur, and if that results in content that +was written after a sync reaching oxide while content written before +the sync is still in a track buffer, then database corruption can occur.</p> + +<p>USB flash memory sticks seem to be especially pernicious liars +regarding sync requests. One can easily see this by committing a large +transaction to an SQLite database on a USB memory stick. The COMMIT +command will return relatively quickly, indicating that the memory stick +has told the operating system and the operating system has told SQLite that +all content is safely in persistent storage, and yet the LED on the end +of the memory stick will continue flashing for several more seconds. +Pulling out the memory stick while the LED is still flashing will frequently +result in database corruption.</p> + +<p>Note that SQLite must believe whatever the operating system and hardware +tell it about the status of sync requests. There is no way for SQLite to +detect that either is lying and that writes might be occurring out-of-order. +However, SQLite in <a href="wal.html">WAL mode</a> is far more forgiving of +out-of-order writes than in the default rollback journal modes. In WAL +mode, the only time that a failed sync operation can cause database corruption +is during a <a href="wal.html#ckpt">checkpoint</a> operation. A sync failure during a COMMIT might +result in loss of durability but not in a corrupt database file. Hence, +one line of defense against database corruption due to failed sync operations +is to use SQLite in WAL mode and to checkpoint as infrequently as possible.</p> + +<h2 id="_disabling_sync_using_pragmas"><span>3.2. </span> Disabling sync using PRAGMAs</h2> + +<p>The sync operations that SQLite performs to help ensure integrity +can be disabled at run-time using the <a href="pragma.html#pragma_synchronous">synchronous pragma</a>. By setting +PRAGMA synchronous=OFF, all sync operations are omitted. This makes +SQLite seem to run faster, but it also allows the operating system to freely +reorder writes, which could result in database corruption if a power failure +or hard reset occurs prior to all content reaching persistent storage.</p> + +<p>For maximum reliability and for robustness against database corruption, +SQLite should always be run with its default synchronous setting of FULL.</p> + +<a name="hardwarefault"></a> + +<h1 id="_disk_drive_and_flash_memory_failures"><span>4. </span> Disk Drive and Flash Memory Failures</h1> + +<p>An SQLite database can become corrupt if the file content changes +due to a disk drive or flash memory failure. It is very rare, but disks +will occasionally flip a bit in the middle of a sector.</p> + +<h2 id="_non_powersafe_flash_memory_controllers"><span>4.1. </span> Non-powersafe flash memory controllers</h2> + +<p>We are told that in some flash memory controllers the wear-leveling logic +can cause random filesystem damage if power is interrupted during a write. +This can manifest, for example, as random changes in the middle of a file +that was not even open at the time of the power loss. So, for example, +a device would be writing content into an MP3 file in flash memory when a +power loss occurs, and that could result in an SQLite database being +corrupted even though the database was not even in use at the time of the +power loss.</p> + +<a name="fakeusb"></a> + +<h2 id="_fake_capacity_usb_sticks"><span>4.2. </span> Fake capacity USB sticks</h2> + +<p>There are many fraudulent USB sticks in circulation that report to have +a high capacity (ex: 8GB) but are really only capable of storing a much +smaller amount (ex: 1GB). Attempts to write on these devices will +often result in unrelated files being overwritten. Any use of a fraudulent +flash memory device can easily lead to database corruption, therefore. +Internet searches such as "fake capacity usb" will turn up lots of +disturbing information about this problem. + +</p><h1 id="_memory_corruption"><span>5. </span> Memory corruption</h1> + +<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 <a href="mmap.html">memory-mapped I/O</a>. +When all or part of the database file is mapped into the application's +address space, then a stray pointer that 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> + +<h1 id="_other_operating_system_problems"><span>6. </span> Other operating system problems</h1> + +<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> + +<h2 id="_linux_threads"><span>6.1. </span> Linux Threads</h2> + +<p>Some older versions of Linux used the LinuxThreads library for thread +support. LinuxThreads is similar to Pthreads, but is subtly different +with respect to handling of POSIX advisory locks. SQLite versions +2.2.3 through 3.6.23 recognized that LinuxThreads were being used at +runtime and took appropriate action to work around the non-standard +behavior of LinuxThreads. But most modern Linux implementations make +use of the newer, and correct, NPTL implementation of Pthreads. Beginning +with SQLite <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21), the use of NPTL is assumed. +No checks are +made. Hence, recent versions of SQLite will subtly malfunction and may +corrupt database files if used in multi-threaded application that run +on older linux systems that make use of LinuxThreads.</p> + +<h2 id="_failures_of_mmap_on_qnx"><span>6.2. </span> Failures of mmap() on QNX</h2> + +<p>There exists some subtle problem with mmap() on QNX such that making +a second mmap() call against a single file descriptor can cause +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 <a href="wal.html">WAL mode</a>, 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 <a href="mmap.html">memory-mapped I/O</a> never +be used. Furthermore, to use <a href="wal.html">WAL mode</a>, it is recommended that applications +employ the <a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> in order to +use <a href="wal.html#noshm">WAL without shared memory</a>. + + +<a name="fscorruption"></a> + +</p><h2 id="_filesystem_corruption"><span>6.3. </span> Filesystem Corruption</h2> + +<p>Since SQLite databases are ordinary disk files, any malfunction in the +filesystem can corrupt the database. Filesystems in modern operating systems +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> + +<a name="cfgerr"></a> + +<h1 id="sqlite_configuration_errors"><span>7. </span>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><p>The following are examples of disabling the built-in protection +mechanisms of SQLite: + +</p><ul> +<li><p>Setting <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a> can cause the database to +go corrupt if there is an operating-system crash or power failure, +though this setting is safe from damage due to application crashes. + +</p></li><li><p>Changing the <a href="pragma.html#pragma_schema_version">PRAGMA schema_version</a> while other database +connections are open. + +</p></li><li><p>Using <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=OFF</a> or <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=MEMORY</a> +and taking an application crash in the middle of a write transaction. + +</p></li><li><p>Setting <a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a> and then changing the +database schema using DML statements can render the database completely +unreadable, if not done carefully. +</p></li></ul> + +<h1 id="_bugs_in_sqlite"><span>8. </span> Bugs in SQLite</h1> + +<p>SQLite is <a href="testing.html">very carefully tested</a> 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 corruption occurs +during any of these events. SQLite is also field-proven with approximately +two billion active deployments with no serious problems.</p> + +<p>Nevertheless, no software is 100% perfect. There have been a few +historical bugs in SQLite (now fixed) that could cause database corruption. +And there may be yet a few more that remain undiscovered. Because of the +extensive testing and widespread use of SQLite, bugs that result in +database corruption tend to be very obscure. The likelihood +of an application encountering an SQLite bug is small. To illustrate this, +an account is given below +of all database-corruption bugs found in SQLite during the +four-year period from 2009-04-01 to 2013-04-15. +This account should give the reader an intuitive sense of the +kinds of bugs in SQLite that manage to slip through testing procedures +and make it into a release.</p> + + +<h2 id="_false_corruption_reports_due_to_database_shrinkage"><span>8.1. </span> False corruption reports due to database shrinkage</h2> + +<p>If a database is written by SQLite version 3.7.0 or later and then +written again by SQLite version 3.6.23 or earlier in such a way as to +make the size of the database file decrease, then the next time that +SQLite version 3.7.0 access the database file, it might report that the +database file is corrupt. The database file is not really corrupt, however. +Version 3.7.0 was simply being overly zealous in its corruption detection.</p> + +<p>The problem was fixed on 2011-02-20. The fix first appears in +SQLite <a href="releaselog/3_7_6.html">version 3.7.6</a> (2011-04-12).</p> + +<h2 id="_corruption_following_switches_between_rollback_and_wal_modes"><span>8.2. </span> Corruption following switches between rollback and WAL modes</h2> + +<p>Repeatedly switching an SQLite database in and out of <a href="wal.html">WAL mode</a> +and running the <a href="lang_vacuum.html">VACUUM</a> command in between switches, in one process or +thread, can cause another process or thread that has the database file +open to miss the fact that the database has changed. That second process +or thread might then try to modify the database using a stale cache and +cause database corruption.</p> + +<p>This problem was discovered during internal testing and has never been +observed in the wild. The problem was fixed on 2011-01-27 and in version +3.7.5.</p> + +<h2 id="_i_o_error_while_obtaining_a_lock_leads_to_corruption"><span>8.3. </span> I/O error while obtaining a lock leads to corruption</h2> + +<p>If the operating system returns an I/O error while attempting to obtain +a certain lock on shared memory in <a href="wal.html">WAL mode</a> then SQLite might fail +to reset its cache, +which could lead to database corruption if subsequent writes are attempted.</p> + +<p>Note that this problem only occurs if the attempt to acquire the lock +resulted in an I/O error. If the lock is simply not granted (because some +other thread or process is already holding a conflicting lock) then no +corruption will ever occur. We are not aware of any operating systems that +will fail with an I/O error while attempting to get a file lock on shared +memory. So this is a theoretical problem rather than a real problem. +Needless to say, this problem has never been observed in the wild. The +problem was discovered while doing stress testing of SQLite in a test +harness that simulates I/O errors.</p> + +<p>This problem was fixed on 2010-09-20 for SQLite version 3.7.3.</p> + +<h2 id="_database_pages_leak_from_the_free_page_list"><span>8.4. </span> Database pages leak from the free page list</h2> + +<p>When content is deleted from an SQLite database, pages that are no +longer used are added to a free list and are reused to hold content +added by subsequent inserts. A bug in SQLite that was present in +version 3.6.16 through 3.7.2 might cause pages to go missing out of +the free list when <a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> was used. This would not cause +data loss. But it would result in the database file being larger than +necessary. And it would cause the <a href="pragma.html#pragma_integrity_check">integrity_check pragma</a> to report +pages missing from the free list.</p> + +<p>This problem was fixed on 2010-08-23 for SQLite version 3.7.2.</p> + +<h2 id="_corruption_following_alternating_writes_from_3_6_and_3_7"><span>8.5. </span> Corruption following alternating writes from 3.6 and 3.7</h2> + +<p>SQLite version 3.7.0 introduced a number of new enhancements to +the SQLite database file format (such as but not limited to <a href="wal.html">WAL</a>). +The 3.7.0 release was a shake-out release for these new features. +We expected to find problems and were not disappointed.</p> + +<p>If a database were originally created using SQLite version 3.7.0, +then written by SQLite version 3.6.23.1 such that the size of the database +file increased, then written again by SQLite version 3.7.0, the database +file could go corrupt.</p> + +<p>This problem was fixed on 2010-08-04 for SQLite version 3.7.1.</p> + +<h2 id="_race_condition_in_recovery_on_windows_systems"><span>8.6. </span> Race condition in recovery on Windows systems</h2> + +<p>SQLite version 3.7.16.2 fixes a subtle race condition in the locking +logic on Windows systems. When a database file is in need +of recovery because the previous process writing to it crashed in the +middle of a transaction and two or more processes try to open the +that database at the same time, then the race condition might cause +one of those processes to get a false indication that the recovery +has already completed, allowing that process to continue using the +database file without running recovery first. If that process writes +to the file, then the file might go corrupt. This race condition +had apparently existed in all prior versions of SQLite for Windows going +back to 2004. But the race was very tight. Practically speaking, you +need a fast multi-core machine in which you launch two processes to run +recovery at the same moment on two separate cores. This defect was +on Windows systems only and did not affect the posix OS interface.</p> + +<a name="svptbug"></a> + +<h2 id="_boundary_value_error_in_the_secondary_journals_used_by_nested_transactions"><span>8.7. </span> Boundary value error in the secondary journals used by nested transactions</h2> + +<p>When a nested transaction is started using <a href="lang_savepoint.html">SAVEPOINT</a>, SQLite uses +a secondary rollback journal to track the changes for the nested +transaction, in case the inner transaction needs to be rolled back. Secondary +journals are not involved in protecting the database from corruption due +to program crashes or power outages. The secondary journals only come into +play when rolling back an inner transaction of a nested transaction. + +</p><p>These secondary journals can be held either in memory or as temporary +files on disk. The default behavior is to store them on disk. But that +can be changed using the <a href="compile.html#temp_store">-DSQLITE_TEMP_STORE</a> compile-time option, +or at run-time using the <a href="pragma.html#pragma_temp_store">PRAGMA temp_store</a> statement. The bug +only arises when secondary journals are held in memory. + +</p><p>In SQLite <a href="releaselog/3_35_0.html">version 3.35.0</a> (2021-03-12), a new optimization was +added so that when SQLite is holding secondary journals in memory, +less memory will be used. Unfortunately, an boundary check in +the new logic was coded incorrectly. +What should have been a "<" operator was coded as "<=". This +error might cause the secondary journal to enter an inconsistent state +if it is ever rolled back. If additional changes are made and the +outer transaction eventually commits, the database might be left in +an inconsistent state. + +</p><p>This problem was discovered by an +<a href="https://sqlite.org/forum/forumpost/b03d86f9516cb3a2">independent researcher</a> +who was attempting to find bugs in SQLite using a fuzzer. The fuzzer found a +failure in an <a href="assert.html">assert() statement</a> that is used +to help verify the internal state of the secondary journal. The bug was a +sufficiently obscure corner-case that it might have gone unnoticed for many +years, had it not been for the intensive use of assert() statements in SQLite, +the persistence and tenacity of the security researchers, and their +customized state-of-the-art fuzzer. + +</p><p>This problem was <a href="https://www.sqlite.org/src/info/73c2b50211d3ae26">fixed</a> +in <a href="releaselog/3_37_2.html">version 3.37.2</a> (2022-01-06). +</p> + |