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