| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>How To Corrupt An SQLite Database File</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
How To Corrupt An SQLite Database File |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<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> |
| 126 |
<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> |
| 127 |
<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> |
| 128 |
<div class="fancy-toc2"><a href="#_deleting_a_hot_journal">1.3. Deleting a hot journal</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#_mispairing_database_files_and_hot_journals_">1.4. Mispairing database files and hot journals </a></div> |
| 130 |
<div class="fancy-toc1"><a href="#_file_locking_problems">2. File locking problems</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#_filesystems_with_broken_or_missing_lock_implementations">2.1. Filesystems with broken or missing lock implementations</a></div> |
| 132 |
<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> |
| 133 |
<div class="fancy-toc2"><a href="#multiple_copies_of_sqlite_linked_into_the_same_application">2.3. Multiple copies of SQLite linked into the same application</a></div> |
| 134 |
<div class="fancy-toc2"><a href="#_two_processes_using_different_locking_protocols">2.4. Two processes using different locking protocols</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#_unlinking_or_renaming_a_database_file_while_in_use">2.5. Unlinking or renaming a database file while in use</a></div> |
| 136 |
<div class="fancy-toc2"><a href="#_multiple_links_to_the_same_file">2.6. Multiple links to the same file</a></div> |
| 137 |
<div class="fancy-toc2"><a href="#_carrying_an_open_database_connection_across_a_fork_">2.7. Carrying an open database connection across a fork() </a></div> |
| 138 |
<div class="fancy-toc1"><a href="#_failure_to_sync">3. Failure to sync</a></div> |
| 139 |
<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> |
| 140 |
<div class="fancy-toc2"><a href="#_disabling_sync_using_pragmas">3.2. Disabling sync using PRAGMAs</a></div> |
| 141 |
<div class="fancy-toc1"><a href="#_disk_drive_and_flash_memory_failures">4. Disk Drive and Flash Memory Failures</a></div> |
| 142 |
<div class="fancy-toc2"><a href="#_non_powersafe_flash_memory_controllers">4.1. Non-powersafe flash memory controllers</a></div> |
| 143 |
<div class="fancy-toc2"><a href="#_fake_capacity_usb_sticks">4.2. Fake capacity USB sticks</a></div> |
| 144 |
<div class="fancy-toc1"><a href="#_memory_corruption">5. Memory corruption</a></div> |
| 145 |
<div class="fancy-toc1"><a href="#_other_operating_system_problems">6. Other operating system problems</a></div> |
| 146 |
<div class="fancy-toc2"><a href="#_linux_threads">6.1. Linux Threads</a></div> |
| 147 |
<div class="fancy-toc2"><a href="#_failures_of_mmap_on_qnx">6.2. Failures of mmap() on QNX</a></div> |
| 148 |
<div class="fancy-toc2"><a href="#_filesystem_corruption">6.3. Filesystem Corruption</a></div> |
| 149 |
<div class="fancy-toc1"><a href="#sqlite_configuration_errors">7. SQLite Configuration Errors</a></div> |
| 150 |
<div class="fancy-toc1"><a href="#_bugs_in_sqlite">8. Bugs in SQLite</a></div> |
| 151 |
<div class="fancy-toc2"><a href="#_race_condition_when_writing_to_a_wal_mode_database_">8.1. Race condition when writing to a WAL-mode database. </a></div> |
| 152 |
<div class="fancy-toc2"><a href="#_stale_expression_indexes_">8.2. Stale expression indexes </a></div> |
| 153 |
<div class="fancy-toc2"><a href="#_false_corruption_reports_due_to_database_shrinkage">8.3. False corruption reports due to database shrinkage</a></div> |
| 154 |
<div class="fancy-toc2"><a href="#_corruption_following_switches_between_rollback_and_wal_modes">8.4. Corruption following switches between rollback and WAL modes</a></div> |
| 155 |
<div class="fancy-toc2"><a href="#_i_o_error_while_obtaining_a_lock_leads_to_corruption">8.5. I/O error while obtaining a lock leads to corruption</a></div> |
| 156 |
<div class="fancy-toc2"><a href="#_database_pages_leak_from_the_free_page_list">8.6. Database pages leak from the free page list</a></div> |
| 157 |
<div class="fancy-toc2"><a href="#_corruption_following_alternating_writes_from_3_6_and_3_7">8.7. Corruption following alternating writes from 3.6 and 3.7</a></div> |
| 158 |
<div class="fancy-toc2"><a href="#_race_condition_in_recovery_on_windows_systems">8.8. Race condition in recovery on Windows systems</a></div> |
| 159 |
<div class="fancy-toc2"><a href="#_boundary_value_error_in_the_secondary_journals_used_by_nested_transactions">8.9. Boundary value error in the secondary journals used by nested transactions</a></div> |
| 160 |
</div> |
| 161 |
</details> |
| 162 |
</div> |
| 163 |
|
| 164 |
|
| 165 |
|
| 166 |
|
| 167 |
<h2 style="margin-left:1.0em" notoc="1" id="overview"> Overview</h2> |
| 168 |
|
| 169 |
<p>An SQLite database is highly resistant to corruption. |
| 170 |
If an application crash, or an operating-system crash, or even |
| 171 |
a power failure occurs in the middle of a transaction, the partially |
| 172 |
written transaction should be automatically rolled back the next time |
| 173 |
the database file is accessed. The recovery process is fully |
| 174 |
automatic and does not require any action on the part of the user |
| 175 |
or the application. |
| 176 |
</p> |
| 177 |
|
| 178 |
<p>Though SQLite is resistant to database corruption, it is not immune. |
| 179 |
This document describes the various ways that an SQLite database might |
| 180 |
go corrupt.</p> |
| 181 |
|
| 182 |
<h1 id="_file_overwrite_by_a_rogue_thread_or_process"><span>1. </span> File overwrite by a rogue thread or process</h1> |
| 183 |
|
| 184 |
<p>SQLite database files are ordinary disk files. |
| 185 |
That means that any process can open the file and |
| 186 |
overwrite it with garbage. There is nothing that the SQLite |
| 187 |
library can do to defend against this.</p> |
| 188 |
|
| 189 |
<a name="stalefd"></a> |
| 190 |
|
| 191 |
<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> |
| 192 |
|
| 193 |
<p>We have seen multiple cases where a file descriptor was open on a file, |
| 194 |
then that file descriptor was closed and reopened on an SQLite database. |
| 195 |
Later, some other thread continued to write into the |
| 196 |
old file descriptor, not realizing that the original file had been closed |
| 197 |
already. But because the file descriptor had been reopened by SQLite, |
| 198 |
the information that was intended to go into the original file ended up |
| 199 |
overwriting parts of the SQLite database, leading to corruption of the |
| 200 |
database.</p> |
| 201 |
|
| 202 |
<p>One example of this occurred circa 2013-08-30 on the canonical repository |
| 203 |
for the <a href="http://www.fossil-scm.org/">Fossil DVCS</a>. In that event, |
| 204 |
file descriptor 2 (standard error) was being erroneously closed (by |
| 205 |
<a href="http://www.stunnel.org/">stunnel</a>, we suspect) prior to |
| 206 |
<a href="c3ref/open.html">sqlite3_open_v2()</a> so that the file descriptor used for the |
| 207 |
repository database file was 2. Later, an application |
| 208 |
bug caused an assert() statement to emit |
| 209 |
an error message by invoking write(2,...). But since file descriptor 2 was |
| 210 |
now connected to a database file, the error message |
| 211 |
overwrote part of the database. To guard against this kind of problem, |
| 212 |
SQLite <a href="releaselog/3_8_1.html">version 3.8.1</a> (2013-10-17) |
| 213 |
and later refuse to use low-numbered file descriptors |
| 214 |
for database files. |
| 215 |
(See <a href="compile.html#minimum_file_descriptor">SQLITE_MINIMUM_FILE_DESCRIPTOR</a> for additional information.)</p> |
| 216 |
|
| 217 |
<p>Another example of corruption caused by using a closed file |
| 218 |
descriptor was |
| 219 |
<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> |
| 220 |
|
| 221 |
<p>Another example of this error was reported against |
| 222 |
<a href="https://fossil-scm.org/">Fossil</a> on 2019-07-11. A file descriptor would |
| 223 |
be opened for debugging output, but then closed and reopened by SQLite. |
| 224 |
But the debugging logic continued to write into the original file |
| 225 |
descriptor. See the |
| 226 |
<a href="https://fossil-scm.org/forum/forumpost/c51b9a1169">forum discussion</a> |
| 227 |
for the bug report and a link to the fix. |
| 228 |
|
| 229 |
</p><h2 id="_backup_or_restore_while_a_transaction_is_active"><span>1.2. </span> Backup or restore while a transaction is active</h2> |
| 230 |
|
| 231 |
<p>Systems that run automatic backups in the background might try to |
| 232 |
make a backup copy of an SQLite database file while it is in the middle |
| 233 |
of a transaction. The backup copy then might contain some old and some |
| 234 |
new content, and thus be corrupt.</p> |
| 235 |
|
| 236 |
<p>There are multiple safe approaches to making backup copies of |
| 237 |
SQLite databases - safe in the sense that they are generate a correct, |
| 238 |
uncorrupted backup. In no particular order: |
| 239 |
|
| 240 |
</p><ol> |
| 241 |
<li><p> |
| 242 |
The <a href="rsync.html">sqlite3_rsync</a> utility program (available beginning with |
| 243 |
SQLite 3.47.0 (2024-10-21) and later) will make a copy |
| 244 |
of a live SQLite over SSH using a bandwidth-efficient protocol. |
| 245 |
|
| 246 |
</p></li><li><p> |
| 247 |
The <a href="lang_vacuum.html#vacuuminto">VACUUM INTO <i>filename</i></a> command copies out |
| 248 |
the current state of an SQLite database into a separate file. |
| 249 |
|
| 250 |
</p></li><li><p> |
| 251 |
The <a href="backup.html">backup API</a> is a C-language interface that can make a |
| 252 |
consistent copy of an SQLite database. |
| 253 |
</p></li></ol> |
| 254 |
|
| 255 |
<p> |
| 256 |
Any of the above approaches will work even on a live database. |
| 257 |
It is also safe to make a copy of an SQLite database file |
| 258 |
as long as there are no transactions in progress while the copy |
| 259 |
is taking place. |
| 260 |
If the previous write transaction failed, then it is important that |
| 261 |
any rollback journal (the <tt>*-journal</tt> file) or write-ahead log |
| 262 |
(the <tt>*-wal</tt> file) be copied together with the database file |
| 263 |
itself.</p> |
| 264 |
|
| 265 |
<a name="delhotjrnl"></a> |
| 266 |
|
| 267 |
<h2 id="_deleting_a_hot_journal"><span>1.3. </span> Deleting a hot journal</h2> |
| 268 |
|
| 269 |
<p>SQLite normally stores all content in a single disk file. However, |
| 270 |
while performing a transaction, information necessary to recover the |
| 271 |
database following a crash or power failure is stored in auxiliary |
| 272 |
journal files. Such journal files are described as <a href="fileformat2.html#hotjrnl">"hot"</a>. |
| 273 |
The journal files have the same name as the |
| 274 |
original database file with the addition |
| 275 |
of <tt>-journal</tt> or <tt>-wal</tt> suffix.</p> |
| 276 |
|
| 277 |
<p>SQLite must see the journal files in order to recover from a crash |
| 278 |
or power failure. If the <a href="fileformat2.html#hotjrnl">hot journal files</a> are moved, deleted, or renamed |
| 279 |
after a crash or power failure, then automatic recovery will not work |
| 280 |
and the database may go corrupt.</p> |
| 281 |
|
| 282 |
<p>Another manifestation of this problem is |
| 283 |
<a href="shortnames.html#db83corrupt">database corruption caused by inconsistent use of 8+3 filenames</a>.</p> |
| 284 |
|
| 285 |
<a name="roguejrnl"></a> |
| 286 |
|
| 287 |
<h2 id="_mispairing_database_files_and_hot_journals_"><span>1.4. </span> Mispairing database files and hot journals </h2> |
| 288 |
|
| 289 |
<p> The previous example is a specific case of a more general problem: |
| 290 |
The state of an SQLite database is controlled by both the |
| 291 |
database file and the journal file. In a quiescent state, the journal |
| 292 |
file does not exist and only the database file matters. |
| 293 |
But if the journal file does exist, it must be |
| 294 |
kept together with the database to avoid corruption. The following |
| 295 |
actions are all likely to lead to corruption: |
| 296 |
</p><ul> |
| 297 |
<li> Swapping journal files between two different databases. |
| 298 |
</li><li> Overwriting a journal file with a different journal file. |
| 299 |
</li><li> Moving a journal file from one database to another. |
| 300 |
</li><li> Copying a database file without also copying its journal. |
| 301 |
</li><li> Overwriting a database file with another without also |
| 302 |
deleting any hot journal associated with the original database. |
| 303 |
</li></ul> |
| 304 |
|
| 305 |
<h1 id="_file_locking_problems"><span>2. </span> File locking problems</h1> |
| 306 |
|
| 307 |
<p>SQLite uses file locks on the database file, and on the |
| 308 |
<a href="wal.html">write-ahead log</a> or <a href="wal.html">WAL</a> file, to coordinate access between concurrent |
| 309 |
processes. Without coordination, two threads or processes might try |
| 310 |
to make incompatible changes to a database file at the same time, |
| 311 |
resulting in database corruption.</p> |
| 312 |
|
| 313 |
<h2 id="_filesystems_with_broken_or_missing_lock_implementations"><span>2.1. </span> Filesystems with broken or missing lock implementations</h2> |
| 314 |
|
| 315 |
<p>SQLite depends on the underlying filesystem to do locking as the |
| 316 |
documentation says it will. But some filesystems contain bugs in their |
| 317 |
locking logic such that the locks do not always behave as advertised. |
| 318 |
This is especially true of network filesystems and NFS in particular. |
| 319 |
If SQLite is used on a filesystem where the locking primitives contain |
| 320 |
bugs, and if two or more threads or processes try to access the same |
| 321 |
database at the same time, then database corruption might result.</p> |
| 322 |
|
| 323 |
<a name="posix_close_bug"></a> |
| 324 |
|
| 325 |
<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> |
| 326 |
|
| 327 |
<p>The default locking mechanism used by SQLite on unix platforms is |
| 328 |
POSIX advisory locking. Unfortunately, POSIX advisory locking has design |
| 329 |
quirks that make it prone to misuse and failure. In particular, any |
| 330 |
thread in the same process with a file descriptor that is holding a POSIX |
| 331 |
advisory lock can override that lock using a different file descriptor. |
| 332 |
One particularly pernicious problem is that the <tt>close()</tt> system |
| 333 |
call will cancel all POSIX advisory locks on the same file for all |
| 334 |
threads and all file descriptors in the process.</p> |
| 335 |
|
| 336 |
<p>So, for example, suppose a multi-thread process has |
| 337 |
two or more threads with separate SQLite database connections to the |
| 338 |
same database file. Then a third thread comes along and wants to read |
| 339 |
something out of that same database file on its own, without using the |
| 340 |
SQLite library. Maybe the third thread wants to make a backup copy |
| 341 |
of the database. Or maybe the third thread is just trying to identify |
| 342 |
the file type and hences tries to read the first 16 bytes to determine |
| 343 |
if it really is an SQLite database. Regardless of the reason, |
| 344 |
the third thread does an <tt>open()</tt>, a <tt>read()</tt> and then |
| 345 |
a <tt>close()</tt>. |
| 346 |
One would think this would be harmless. |
| 347 |
But the <tt>close()</tt> system call caused the |
| 348 |
locks held on the database by all the other threads to be dropped. Those |
| 349 |
other threads have no way of knowing that their locks have just been |
| 350 |
trashed (POSIX does not provide any mechanism to determine this) and so |
| 351 |
they keep on running under the assumption that their locks are still valid. |
| 352 |
This can lead to two or more threads or processes trying to write to the |
| 353 |
database at the same time, resulting in database corruption.</p> |
| 354 |
|
| 355 |
<p>Note that it is perfectly safe for two or more threads to access the |
| 356 |
same SQLite database file using the SQLite library. The unix drivers for |
| 357 |
SQLite know about the POSIX advisory locking quirks and work around them. |
| 358 |
This problem only arises when a thread tries to bypass the SQLite library |
| 359 |
and read the database file directly.</p> |
| 360 |
|
| 361 |
<p>Beginning with SQLite version 3.51.0 (2025-11-04), SQLite implements |
| 362 |
additional defenses to try to avoid problems caused by locks that are |
| 363 |
broken by <tt>close()</tt>. These new defenses help when the database |
| 364 |
is in <a href="wal.html">WAL mode</a> and is being accessed from multiple processes. But |
| 365 |
they are not a cure-all. To avoid corruptions, developers should be |
| 366 |
careful to never use <tt>close()</tt> on an SQLite database file |
| 367 |
while one or more database connections are open, even in other threads. |
| 368 |
|
| 369 |
</p><h2 id="multiple_copies_of_sqlite_linked_into_the_same_application"><span>2.3. </span>Multiple copies of SQLite linked into the same application</h2> |
| 370 |
|
| 371 |
<p>As pointed out in the previous section, SQLite takes steps to work |
| 372 |
around the quirks of POSIX advisory locking. Part of that work-around involves |
| 373 |
keeping a global list (mutex protected) of open SQLite database files. |
| 374 |
But, if multiple copies of SQLite are linked into the same application, |
| 375 |
then there will be multiple instances of this global list. |
| 376 |
Database connections opened using one copy of the SQLite library |
| 377 |
will be unaware of database connections opened using the other copy, |
| 378 |
and will be unable to work around the POSIX advisory locking quirks. |
| 379 |
A <tt>close()</tt> operation on one connection might unknowingly |
| 380 |
clear the locks on a different database connection, leading to database |
| 381 |
corruption.</p> |
| 382 |
|
| 383 |
<p>The scenario above sounds far-fetched. |
| 384 |
But the SQLite developers are aware of at |
| 385 |
least one commercial product that was released |
| 386 |
with exactly this bug. The vendor came to the SQLite developers seeking |
| 387 |
help in tracking down some infrequent database corruption issues they were |
| 388 |
seeing on Linux and Mac. The problem was eventually traced to the |
| 389 |
fact that the application was linking against two separate copies of SQLite. |
| 390 |
The solution was to change the application build procedures to link against |
| 391 |
just one copy of SQLite instead of two.</p> |
| 392 |
|
| 393 |
<h2 id="_two_processes_using_different_locking_protocols"><span>2.4. </span> Two processes using different locking protocols</h2> |
| 394 |
|
| 395 |
<p>The default locking mechanism used by SQLite on unix platforms is |
| 396 |
POSIX advisory locking, but there are other options. By selecting an |
| 397 |
alternative <a href="c3ref/vfs.html">sqlite3_vfs</a> using the <a href="c3ref/open.html">sqlite3_open_v2()</a> interface, an |
| 398 |
application can make use of other locking protocols that might be more |
| 399 |
appropriate to certain filesystems. For example, dot-file locking might |
| 400 |
be selected for use in an application that has to run on an NFS filesystem |
| 401 |
that does not support POSIX advisory locking.</p> |
| 402 |
|
| 403 |
<p>It is important that all connections to the same database file use |
| 404 |
the same locking protocol. |
| 405 |
If one application is using POSIX advisory locks and another application |
| 406 |
is using dot-file locking, then the two applications will not see each |
| 407 |
other's locks and will not be able to coordinate database access, possibly |
| 408 |
leading to database corruption.</p> |
| 409 |
|
| 410 |
<a name="unlink"></a> |
| 411 |
|
| 412 |
<h2 id="_unlinking_or_renaming_a_database_file_while_in_use"><span>2.5. </span> Unlinking or renaming a database file while in use</h2> |
| 413 |
|
| 414 |
<p>If two processes have open connections to the same database file and |
| 415 |
one process closes its connection, unlinks the file, then creates a new |
| 416 |
database file in its place with the same name and reopens the new file, |
| 417 |
then the two processes will be talking to different database files with |
| 418 |
the same name. (Note that this is only possible on Posix and Posix-like |
| 419 |
systems that permit a file to be unlinked while it is still open for |
| 420 |
reading and writing. Windows does not allow this to occur.) |
| 421 |
Since rollback journals and WAL files are based on the name of the database |
| 422 |
file, the two different database files will share the same rollback |
| 423 |
journal or WAL file. A rollback or recovery for one of the databases |
| 424 |
might use content from the other database, resulting in corruption. |
| 425 |
A similar problem occurs if a database file is renamed while it is |
| 426 |
opened and a new file is created with the old name.</p> |
| 427 |
|
| 428 |
<p>In other words, unlinking or renaming an open database file |
| 429 |
results in behavior that is undefined and probably undesirable.</p> |
| 430 |
|
| 431 |
<p>Beginning with SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20), |
| 432 |
the unix OS interface will |
| 433 |
send SQLITE_WARNING messages to the <a href="errlog.html">error log</a> if a database file is unlinked |
| 434 |
while it is still in use.</p> |
| 435 |
|
| 436 |
<a name="alias"></a> |
| 437 |
|
| 438 |
<h2 id="_multiple_links_to_the_same_file"><span>2.6. </span> Multiple links to the same file</h2> |
| 439 |
|
| 440 |
<p>If a single database file has multiple links (either hard or soft links) |
| 441 |
then that is just another way of saying that the file has multiple names. |
| 442 |
If two or more processes open the database using different names, then |
| 443 |
they will use different rollback journals and WAL files. That means that |
| 444 |
if one process crashes, the other process will be unable to recover the |
| 445 |
transaction in progress because it will be looking in the wrong place |
| 446 |
for the appropriate journal.</p> |
| 447 |
|
| 448 |
<p>In other words, opening and using a database file that has two or |
| 449 |
more names results in behavior that is undefined and probably undesirable.</p> |
| 450 |
|
| 451 |
<p>Beginning with SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20), |
| 452 |
the unix OS interface will |
| 453 |
send SQLITE_WARNING messages to the <a href="errlog.html">error log</a> if a database file has |
| 454 |
multiple hard links.</p> |
| 455 |
|
| 456 |
<p>Beginning with SQLite <a href="releaselog/3_10_0.html">version 3.10.0</a> (2016-01-06), |
| 457 |
the unix OS interface will |
| 458 |
attempt to resolve symbolic links and open the database file by its |
| 459 |
canonical name. Prior to version 3.10.0, opening a database file |
| 460 |
through a symbolic link was similar to opening a database file |
| 461 |
that had multiple hard links and resulted in undefined behavior.</p> |
| 462 |
|
| 463 |
<a name="fork"></a> |
| 464 |
|
| 465 |
<h2 id="_carrying_an_open_database_connection_across_a_fork_"><span>2.7. </span> Carrying an open database connection across a fork() </h2> |
| 466 |
|
| 467 |
<p> Do not open an SQLite database connection, then fork(), then try |
| 468 |
to use that database connection in the child process. All kinds of |
| 469 |
locking problems will result and you can easily end up with a corrupt |
| 470 |
database. SQLite is not designed to support that kind of behavior. |
| 471 |
Any database connection that is used in a child process must be opened |
| 472 |
in the child process, not inherited from the parent. |
| 473 |
|
| 474 |
</p><p> Do not even call <a href="c3ref/close.html">sqlite3_close()</a> on a database connection from a |
| 475 |
child process if the connection was opened in the parent. It is safe |
| 476 |
to close the underlying file descriptor, but the <a href="c3ref/close.html">sqlite3_close()</a> |
| 477 |
interface might invoke cleanup activities that will delete content out |
| 478 |
from under the parent, leading to errors and perhaps even database |
| 479 |
corruption. |
| 480 |
|
| 481 |
</p><h1 id="_failure_to_sync"><span>3. </span> Failure to sync</h1> |
| 482 |
|
| 483 |
<p>In order to guarantee that database files are always consistent, SQLite |
| 484 |
will occasionally ask the operating system to flush all pending writes to |
| 485 |
persistent storage then wait for that flush to complete. This is |
| 486 |
accomplished using the <tt>fsync()</tt> system call under unix and |
| 487 |
<tt>FlushFileBuffers()</tt> under Windows. We call this flush of |
| 488 |
pending writes a "sync".</p> |
| 489 |
|
| 490 |
<p>Actually, if one is only concerned with atomic and consistent writes and |
| 491 |
is willing to forego durable writes, the sync operation does not need |
| 492 |
to wait until the content is completely stored on persistent media. Instead, |
| 493 |
the sync operation can be thought of as an I/O barrier. As long as all |
| 494 |
writes that occur before the sync are completed before any write that happens |
| 495 |
after the sync, no database corruption will occur. If sync is operating as |
| 496 |
an I/O barrier and not as a true sync, then a power failure or system crash |
| 497 |
might cause one or more previously committed transactions to roll back |
| 498 |
(in violation of the "durable" property of "ACID") but the database will at |
| 499 |
least continue to be consistent, and that is what most people care about.</p> |
| 500 |
|
| 501 |
<h2 id="_disk_drives_that_do_not_honor_sync_requests"><span>3.1. </span> Disk drives that do not honor sync requests</h2> |
| 502 |
|
| 503 |
<p>Unfortunately, most consumer-grade mass storage devices lie about |
| 504 |
syncing. Disk drives will report that content is safely on persistent |
| 505 |
media as soon as it reaches the track buffer and before actually being |
| 506 |
written to oxide. This makes the disk drives seem to operate faster |
| 507 |
(which is vitally important to the manufacturer so that they can show |
| 508 |
good benchmark numbers in trade magazines). And in fairness, the lie |
| 509 |
normally causes no harm, as long as there is no power loss or hard reset |
| 510 |
prior to the track buffer actually being written to oxide. But if a |
| 511 |
power loss or hard reset does occur, and if that results in content that |
| 512 |
was written after a sync reaching oxide while content written before |
| 513 |
the sync is still in a track buffer, then database corruption can occur.</p> |
| 514 |
|
| 515 |
<p>USB flash memory sticks seem to be especially pernicious liars |
| 516 |
regarding sync requests. One can easily see this by committing a large |
| 517 |
transaction to an SQLite database on a USB memory stick. The COMMIT |
| 518 |
command will return relatively quickly, indicating that the memory stick |
| 519 |
has told the operating system and the operating system has told SQLite that |
| 520 |
all content is safely in persistent storage, and yet the LED on the end |
| 521 |
of the memory stick will continue flashing for several more seconds. |
| 522 |
Pulling out the memory stick while the LED is still flashing will frequently |
| 523 |
result in database corruption.</p> |
| 524 |
|
| 525 |
<p>Note that SQLite must believe whatever the operating system and hardware |
| 526 |
tell it about the status of sync requests. There is no way for SQLite to |
| 527 |
detect that either is lying and that writes might be occurring out-of-order. |
| 528 |
However, SQLite in <a href="wal.html">WAL mode</a> is far more forgiving of |
| 529 |
out-of-order writes than in the default rollback journal modes. In WAL |
| 530 |
mode, the only time that a failed sync operation can cause database corruption |
| 531 |
is during a <a href="wal.html#ckpt">checkpoint</a> operation. A sync failure during a COMMIT might |
| 532 |
result in loss of durability but not in a corrupt database file. Hence, |
| 533 |
one line of defense against database corruption due to failed sync operations |
| 534 |
is to use SQLite in WAL mode and to checkpoint as infrequently as possible.</p> |
| 535 |
|
| 536 |
<h2 id="_disabling_sync_using_pragmas"><span>3.2. </span> Disabling sync using PRAGMAs</h2> |
| 537 |
|
| 538 |
<p>The sync operations that SQLite performs to help ensure integrity |
| 539 |
can be disabled at run-time using the <a href="pragma.html#pragma_synchronous">synchronous pragma</a>. By setting |
| 540 |
PRAGMA synchronous=OFF, all sync operations are omitted. This makes |
| 541 |
SQLite seem to run faster, but it also allows the operating system to freely |
| 542 |
reorder writes, which could result in database corruption if a power failure |
| 543 |
or hard reset occurs prior to all content reaching persistent storage.</p> |
| 544 |
|
| 545 |
<p>For maximum reliability and for robustness against database corruption, |
| 546 |
SQLite should always be run with its default synchronous setting of FULL.</p> |
| 547 |
|
| 548 |
<a name="hardwarefault"></a> |
| 549 |
|
| 550 |
<h1 id="_disk_drive_and_flash_memory_failures"><span>4. </span> Disk Drive and Flash Memory Failures</h1> |
| 551 |
|
| 552 |
<p>An SQLite database can become corrupt if the file content changes |
| 553 |
due to a disk drive or flash memory failure. It is very rare, but disks |
| 554 |
will occasionally flip a bit in the middle of a sector.</p> |
| 555 |
|
| 556 |
<h2 id="_non_powersafe_flash_memory_controllers"><span>4.1. </span> Non-powersafe flash memory controllers</h2> |
| 557 |
|
| 558 |
<p>We are told that in some flash memory controllers the wear-leveling logic |
| 559 |
can cause random filesystem damage if power is interrupted during a write. |
| 560 |
This can manifest, for example, as random changes in the middle of a file |
| 561 |
that was not even open at the time of the power loss. So, for example, |
| 562 |
a device would be writing content into an MP3 file in flash memory when a |
| 563 |
power loss occurs, and that could result in an SQLite database being |
| 564 |
corrupted even though the database was not even in use at the time of the |
| 565 |
power loss.</p> |
| 566 |
|
| 567 |
<a name="fakeusb"></a> |
| 568 |
|
| 569 |
<h2 id="_fake_capacity_usb_sticks"><span>4.2. </span> Fake capacity USB sticks</h2> |
| 570 |
|
| 571 |
<p>There are many fraudulent USB sticks in circulation that report to have |
| 572 |
a high capacity (ex: 8GB) but are really only capable of storing a much |
| 573 |
smaller amount (ex: 1GB). Attempts to write on these devices will |
| 574 |
often result in unrelated files being overwritten. Any use of a fraudulent |
| 575 |
flash memory device can easily lead to database corruption, therefore. |
| 576 |
Internet searches such as "fake capacity usb" will turn up lots of |
| 577 |
disturbing information about this problem. |
| 578 |
|
| 579 |
</p><h1 id="_memory_corruption"><span>5. </span> Memory corruption</h1> |
| 580 |
|
| 581 |
<p>SQLite is a C-library that runs in the same address space as the |
| 582 |
application that it serves. That means that stray pointers, buffer |
| 583 |
overruns, heap corruption, or other malfunctions in the application can |
| 584 |
corrupt internal SQLite data structure and ultimately result in a |
| 585 |
corrupt database file. Normally these kinds of problems manifest themselves |
| 586 |
as segfaults prior to any database corruption occurring, but there have |
| 587 |
been instances where application code errors have caused SQLite to |
| 588 |
malfunction subtly so as to corrupt the database file rather than |
| 589 |
panicking.</p> |
| 590 |
|
| 591 |
<p>The memory corruption problem becomes more acute when |
| 592 |
using <a href="mmap.html">memory-mapped I/O</a>. |
| 593 |
When all or part of the database file is mapped into the application's |
| 594 |
address space, then a stray pointer that overwrites any part of that |
| 595 |
mapped space will immediately corrupt the database file, without |
| 596 |
requiring the application to do a subsequent write() system call.</p> |
| 597 |
|
| 598 |
<h1 id="_other_operating_system_problems"><span>6. </span> Other operating system problems</h1> |
| 599 |
|
| 600 |
<p>Sometimes operating systems will exhibit non-standard behavior which |
| 601 |
can lead to problems. Sometimes this non-standard behavior is deliberate, |
| 602 |
and sometimes it is a mistake in the implementation. But in any event, |
| 603 |
if the operating performs differently from they way SQLite expects it to |
| 604 |
perform, the possibility of database corruption exists.</p> |
| 605 |
|
| 606 |
<h2 id="_linux_threads"><span>6.1. </span> Linux Threads</h2> |
| 607 |
|
| 608 |
<p>Some older versions of Linux used the LinuxThreads library for thread |
| 609 |
support. LinuxThreads is similar to Pthreads, but is subtly different |
| 610 |
with respect to handling of POSIX advisory locks. SQLite versions |
| 611 |
2.2.3 through 3.6.23 recognized that LinuxThreads were being used at |
| 612 |
runtime and took appropriate action to work around the non-standard |
| 613 |
behavior of LinuxThreads. But most modern Linux implementations make |
| 614 |
use of the newer, and correct, NPTL implementation of Pthreads. Beginning |
| 615 |
with SQLite <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21), the use of NPTL is assumed. |
| 616 |
No checks are |
| 617 |
made. Hence, recent versions of SQLite will subtly malfunction and may |
| 618 |
corrupt database files if used in multi-threaded application that run |
| 619 |
on older linux systems that make use of LinuxThreads.</p> |
| 620 |
|
| 621 |
<h2 id="_failures_of_mmap_on_qnx"><span>6.2. </span> Failures of mmap() on QNX</h2> |
| 622 |
|
| 623 |
<p>There exists some subtle problem with mmap() on QNX such that making |
| 624 |
a second mmap() call against a single file descriptor can cause |
| 625 |
the memory obtained from the first mmap() call to be zeroed. SQLite on |
| 626 |
unix uses mmap() to create a shared memory region for transaction |
| 627 |
coordination in <a href="wal.html">WAL mode</a>, and it will call mmap() multiple times |
| 628 |
for large transactions. The QNX mmap() has been demonstrated to corrupt |
| 629 |
database file under that scenario. QNX engineers are aware of this problem |
| 630 |
and are working on a solution; the problem may have already been fixed by |
| 631 |
the time you read this.</p> |
| 632 |
|
| 633 |
<p>When running on QNX, it is recommended that <a href="mmap.html">memory-mapped I/O</a> never |
| 634 |
be used. Furthermore, to use <a href="wal.html">WAL mode</a>, it is recommended that applications |
| 635 |
employ the <a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> in order to |
| 636 |
use <a href="wal.html#noshm">WAL without shared memory</a>. |
| 637 |
|
| 638 |
|
| 639 |
<a name="fscorruption"></a> |
| 640 |
|
| 641 |
</p><h2 id="_filesystem_corruption"><span>6.3. </span> Filesystem Corruption</h2> |
| 642 |
|
| 643 |
<p>Since SQLite databases are ordinary disk files, any malfunction in the |
| 644 |
filesystem can corrupt the database. Filesystems in modern operating systems |
| 645 |
are very reliable, but errors do still occur. For example, on 2013-10-01 |
| 646 |
the SQLite database that holds the |
| 647 |
<a href="http://wiki.tcl-lang.org/">Wiki for Tcl/Tk</a> went corrupt a few days |
| 648 |
after the host computer was moved to a dodgy build of the (linux) kernel |
| 649 |
that had issues in the filesystem layer. In that event, the filesystem |
| 650 |
eventually became so badly corrupted that the machine was unusable, but |
| 651 |
the earliest symptom of trouble was the corrupted SQLite database.</p> |
| 652 |
|
| 653 |
<a name="cfgerr"></a> |
| 654 |
|
| 655 |
<h1 id="sqlite_configuration_errors"><span>7. </span>SQLite Configuration Errors</h1> |
| 656 |
|
| 657 |
<p>SQLite has many built-in protections against database corruption. |
| 658 |
But many of these protections can be disabled by configuration options. |
| 659 |
If protections are disabled, database corruption may occur. |
| 660 |
|
| 661 |
</p><p>The following are examples of disabling the built-in protection |
| 662 |
mechanisms of SQLite: |
| 663 |
|
| 664 |
</p><ul> |
| 665 |
<li><p>Setting <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a> can cause the database to |
| 666 |
go corrupt if there is an operating-system crash or power failure, |
| 667 |
though this setting is safe from damage due to application crashes. |
| 668 |
|
| 669 |
</p></li><li><p>Changing the <a href="pragma.html#pragma_schema_version">PRAGMA schema_version</a> while other database |
| 670 |
connections are open. |
| 671 |
|
| 672 |
</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> |
| 673 |
and taking an application crash in the middle of a write transaction. |
| 674 |
|
| 675 |
</p></li><li><p>Setting <a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a> and then changing the |
| 676 |
database schema using DML statements can render the database completely |
| 677 |
unreadable, if not done carefully. |
| 678 |
</p></li></ul> |
| 679 |
|
| 680 |
<h1 id="_bugs_in_sqlite"><span>8. </span> Bugs in SQLite</h1> |
| 681 |
|
| 682 |
<p>SQLite is <a href="testing.html">very carefully tested</a> to help ensure that it is |
| 683 |
as bug-free as possible. Among the many tests that are carried out for |
| 684 |
every SQLite version are tests that simulate power failures, I/O errors, |
| 685 |
and out-of-memory (OOM) errors to verify that no database corruption occurs |
| 686 |
during any of these events. SQLite is also field-proven with approximately |
| 687 |
two billion active deployments with no serious problems.</p> |
| 688 |
|
| 689 |
<p>Nevertheless, no software is 100% perfect. There have been a few |
| 690 |
historical bugs in SQLite (now fixed) that could cause database corruption. |
| 691 |
And there may be yet a few more that remain undiscovered. Because of the |
| 692 |
extensive testing and widespread use of SQLite, bugs that result in |
| 693 |
database corruption tend to be very obscure. The likelihood |
| 694 |
of an application encountering an SQLite bug is small. To illustrate this, |
| 695 |
an account is given below |
| 696 |
of all database-corruption bugs found in SQLite during the |
| 697 |
four-year period from 2009-04-01 to 2013-04-15. |
| 698 |
This account should give the reader an intuitive sense of the |
| 699 |
kinds of bugs in SQLite that manage to slip through testing procedures |
| 700 |
and make it into a release.</p> |
| 701 |
|
| 702 |
<h2 id="_race_condition_when_writing_to_a_wal_mode_database_"><span>8.1. </span> Race condition when writing to a WAL-mode database. </h2> |
| 703 |
|
| 704 |
<p>When there are two or more database connections in separate |
| 705 |
threads or processes, both open on the same <a href="wal.html">WAL-mode</a> database, |
| 706 |
and if both connections try to write or run checkpoint at the |
| 707 |
same time, then there is a race condition that might corrupt |
| 708 |
the database file. This is the <a href="wal.html#walresetbug">WAL-reset bug</a>. It existed |
| 709 |
in all versions of SQLite from 3.7.0 through 3.51.2. See |
| 710 |
the <a href="wal.html#walresetbug">WAL-reset bug</a> documentation for details. |
| 711 |
|
| 712 |
</p><h2 id="_stale_expression_indexes_"><span>8.2. </span> Stale expression indexes </h2> |
| 713 |
|
| 714 |
<p>An "expression index" is an index that references an |
| 715 |
expression or a <a href="gencol.html#vgc*">VIRTUAL generated column</a>. Expression indexes |
| 716 |
are suppose to use only <a href="deterministic.html">deterministic functions</a>. However, |
| 717 |
when moving an database across different platforms, or when |
| 718 |
changing SQLite versions, sometimes one of these supposely |
| 719 |
deterministic functions will change its output slightly. |
| 720 |
This can cause the index to appear to be corrupt. See |
| 721 |
the <a href="staleexpridx.html">stale expression index</a> documentation for further |
| 722 |
details. |
| 723 |
|
| 724 |
</p><h2 id="_false_corruption_reports_due_to_database_shrinkage"><span>8.3. </span> False corruption reports due to database shrinkage</h2> |
| 725 |
|
| 726 |
<p>If a database is written by SQLite version 3.7.0 or later and then |
| 727 |
written again by SQLite version 3.6.23 or earlier in such a way as to |
| 728 |
make the size of the database file decrease, then the next time that |
| 729 |
SQLite version 3.7.0 accesses the database file, it might report that the |
| 730 |
database file is corrupt. The database file is not really corrupt, however. |
| 731 |
Version 3.7.0 was simply being overly zealous in its corruption detection.</p> |
| 732 |
|
| 733 |
<p>The problem was fixed on 2011-02-20. The fix first appears in |
| 734 |
SQLite <a href="releaselog/3_7_6.html">version 3.7.6</a> (2011-04-12).</p> |
| 735 |
|
| 736 |
<h2 id="_corruption_following_switches_between_rollback_and_wal_modes"><span>8.4. </span> Corruption following switches between rollback and WAL modes</h2> |
| 737 |
|
| 738 |
<p>Repeatedly switching an SQLite database in and out of <a href="wal.html">WAL mode</a> |
| 739 |
and running the <a href="lang_vacuum.html">VACUUM</a> command in between switches, in one process or |
| 740 |
thread, can cause another process or thread that has the database file |
| 741 |
open to miss the fact that the database has changed. That second process |
| 742 |
or thread might then try to modify the database using a stale cache and |
| 743 |
cause database corruption.</p> |
| 744 |
|
| 745 |
<p>This problem was discovered during internal testing and has never been |
| 746 |
observed in the wild. The problem was fixed on 2011-01-27 and in version |
| 747 |
3.7.5.</p> |
| 748 |
|
| 749 |
<h2 id="_i_o_error_while_obtaining_a_lock_leads_to_corruption"><span>8.5. </span> I/O error while obtaining a lock leads to corruption</h2> |
| 750 |
|
| 751 |
<p>If the operating system returns an I/O error while attempting to obtain |
| 752 |
a certain lock on shared memory in <a href="wal.html">WAL mode</a> then SQLite might fail |
| 753 |
to reset its cache, |
| 754 |
which could lead to database corruption if subsequent writes are attempted.</p> |
| 755 |
|
| 756 |
<p>Note that this problem only occurs if the attempt to acquire the lock |
| 757 |
resulted in an I/O error. If the lock is simply not granted (because some |
| 758 |
other thread or process is already holding a conflicting lock) then no |
| 759 |
corruption will ever occur. We are not aware of any operating systems that |
| 760 |
will fail with an I/O error while attempting to get a file lock on shared |
| 761 |
memory. So this is a theoretical problem rather than a real problem. |
| 762 |
Needless to say, this problem has never been observed in the wild. The |
| 763 |
problem was discovered while doing stress testing of SQLite in a test |
| 764 |
harness that simulates I/O errors.</p> |
| 765 |
|
| 766 |
<p>This problem was fixed on 2010-09-20 for SQLite version 3.7.3.</p> |
| 767 |
|
| 768 |
<h2 id="_database_pages_leak_from_the_free_page_list"><span>8.6. </span> Database pages leak from the free page list</h2> |
| 769 |
|
| 770 |
<p>When content is deleted from an SQLite database, pages that are no |
| 771 |
longer used are added to a free list and are reused to hold content |
| 772 |
added by subsequent inserts. A bug in SQLite that was present in |
| 773 |
version 3.6.16 through 3.7.2 might cause pages to go missing out of |
| 774 |
the free list when <a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> was used. This would not cause |
| 775 |
data loss. But it would result in the database file being larger than |
| 776 |
necessary. And it would cause the <a href="pragma.html#pragma_integrity_check">integrity_check pragma</a> to report |
| 777 |
pages missing from the free list.</p> |
| 778 |
|
| 779 |
<p>This problem was fixed on 2010-08-23 for SQLite version 3.7.2.</p> |
| 780 |
|
| 781 |
<h2 id="_corruption_following_alternating_writes_from_3_6_and_3_7"><span>8.7. </span> Corruption following alternating writes from 3.6 and 3.7</h2> |
| 782 |
|
| 783 |
<p>SQLite version 3.7.0 introduced a number of new enhancements to |
| 784 |
the SQLite database file format (such as but not limited to <a href="wal.html">WAL</a>). |
| 785 |
The 3.7.0 release was a shake-out release for these new features. |
| 786 |
We expected to find problems and were not disappointed.</p> |
| 787 |
|
| 788 |
<p>If a database were originally created using SQLite version 3.7.0, |
| 789 |
then written by SQLite version 3.6.23.1 such that the size of the database |
| 790 |
file increased, then written again by SQLite version 3.7.0, the database |
| 791 |
file could go corrupt.</p> |
| 792 |
|
| 793 |
<p>This problem was fixed on 2010-08-04 for SQLite version 3.7.1.</p> |
| 794 |
|
| 795 |
<h2 id="_race_condition_in_recovery_on_windows_systems"><span>8.8. </span> Race condition in recovery on Windows systems</h2> |
| 796 |
|
| 797 |
<p>SQLite version 3.7.16.2 fixes a subtle race condition in the locking |
| 798 |
logic on Windows systems. When a database file is in need |
| 799 |
of recovery because the previous process writing to it crashed in the |
| 800 |
middle of a transaction and two or more processes try to open the |
| 801 |
that database at the same time, then the race condition might cause |
| 802 |
one of those processes to get a false indication that the recovery |
| 803 |
has already completed, allowing that process to continue using the |
| 804 |
database file without running recovery first. If that process writes |
| 805 |
to the file, then the file might go corrupt. This race condition |
| 806 |
had apparently existed in all prior versions of SQLite for Windows going |
| 807 |
back to 2004. But the race was very tight. Practically speaking, you |
| 808 |
need a fast multi-core machine in which you launch two processes to run |
| 809 |
recovery at the same moment on two separate cores. This defect was |
| 810 |
on Windows systems only and did not affect the posix OS interface.</p> |
| 811 |
|
| 812 |
<a name="svptbug"></a> |
| 813 |
|
| 814 |
<h2 id="_boundary_value_error_in_the_secondary_journals_used_by_nested_transactions"><span>8.9. </span> Boundary value error in the secondary journals used by nested transactions</h2> |
| 815 |
|
| 816 |
<p>When a nested transaction is started using <a href="lang_savepoint.html">SAVEPOINT</a>, SQLite uses |
| 817 |
a secondary rollback journal to track the changes for the nested |
| 818 |
transaction, in case the inner transaction needs to be rolled back. Secondary |
| 819 |
journals are not involved in protecting the database from corruption due |
| 820 |
to program crashes or power outages. The secondary journals only come into |
| 821 |
play when rolling back an inner transaction of a nested transaction. |
| 822 |
|
| 823 |
</p><p>These secondary journals can be held either in memory or as temporary |
| 824 |
files on disk. The default behavior is to store them on disk. But that |
| 825 |
can be changed using the <a href="compile.html#temp_store">-DSQLITE_TEMP_STORE</a> compile-time option, |
| 826 |
or at run-time using the <a href="pragma.html#pragma_temp_store">PRAGMA temp_store</a> statement. The bug |
| 827 |
only arises when secondary journals are held in memory. |
| 828 |
|
| 829 |
</p><p>In SQLite <a href="releaselog/3_35_0.html">version 3.35.0</a> (2021-03-12), a new optimization was |
| 830 |
added so that when SQLite is holding secondary journals in memory, |
| 831 |
less memory will be used. Unfortunately, a boundary check in |
| 832 |
the new logic was coded incorrectly. |
| 833 |
What should have been a "<" operator was coded as "<=". This |
| 834 |
error might cause the secondary journal to enter an inconsistent state |
| 835 |
if it is ever rolled back. If additional changes are made and the |
| 836 |
outer transaction eventually commits, the database might be left in |
| 837 |
an inconsistent state. |
| 838 |
|
| 839 |
</p><p>This problem was discovered by an |
| 840 |
<a href="https://sqlite.org/forum/forumpost/b03d86f9516cb3a2">independent researcher</a> |
| 841 |
who was attempting to find bugs in SQLite using a fuzzer. The fuzzer found a |
| 842 |
failure in an <a href="assert.html">assert() statement</a> that is used |
| 843 |
to help verify the internal state of the secondary journal. The bug was a |
| 844 |
sufficiently obscure corner-case that it might have gone unnoticed for many |
| 845 |
years, had it not been for the intensive use of assert() statements in SQLite, |
| 846 |
the persistence and tenacity of the security researchers, and their |
| 847 |
customized state-of-the-art fuzzer. |
| 848 |
|
| 849 |
</p><p>This problem was <a href="https://sqlite.org/src/info/73c2b50211d3ae26">fixed</a> |
| 850 |
in <a href="releaselog/3_37_2.html">version 3.37.2</a> (2022-01-06). |
| 851 |
</p><p align="center"><small><i>This page was last updated on 2026-04-13 10:54:51Z </small></i></p> |
| 852 |
|