Debian Codegraph @

www/howtocorrupt.html

raw
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 "&lt;" operator was coded as "&lt;=". 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