Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the "How To Corrupt And SQLite Database" document. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
bc040b36db0e4a3bbc6210939c0cb4a7 |
User & Date: | drh 2011-04-12 18:17:39.817 |
Context
2011-04-12
| ||
18:46 | Add a new failure scenario to the howtocorrupt.html document. (check-in: 115b99a5d1 user: drh tags: trunk) | |
18:17 | Add the "How To Corrupt And SQLite Database" document. (check-in: bc040b36db user: drh tags: trunk) | |
13:28 | Add a link from the download page to System.Data.SQLite.org. Also change the name of precompiled Mac binaries from *-mac-* to *-osx-*. (check-in: 5235a1ff91 user: drh tags: trunk) | |
Changes
Added pages/howtocorrupt.in.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | <title>How To Corrupt An SQLite Database File</title> <tcl>hd_keywords {how to corrupt}</tcl> <h1 align=center>How To Corrupt An SQLite Database File</h1> <p>An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or a 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> <h2>1.0 File overwrite by a rogue thread process</h2> <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> <h3>1.1 Continuing to use a file descriptor after it has been closed</h3> <p>We have seen cases where a file descriptor was open on a log file, then that file descriptor was closed and reopened on an SQLite database. Later, some other thread continued to write log information into the old file descriptor, not realizing that the log file had been closed already. But because the file descriptor had been reopened by SQLite, the information that was intended to go into the log file ended up overwriting parts of the SQLite database, leading to corruption of the database.</p> <h3>1.2 Backup or restore while a transaction is active</h3> <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 [backup API] 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> <h3>1.3 Deleting a hot journal</h3> <p>SQLite normally stores all content in a single disk file. However, while performing a tranaction, information necessary to roll back that transaction following a crash or power failure is stored in auxiliary journal files. These 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 journal files are moved, deleted, or renamed after a crash or power failure, then automatic recovery will not work and the database may go corrupt.</p> <h2>2.0 File locking problems</h2> <p>SQLite uses file locks on the database file, and on the [write-ahead log] or [WAL] 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> <h3>2.1 Filesystems with broken or missing lock implementations</h3> <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> <h3>2.2 Posix advisory locks canceled by a separate thread doing close()</h3> <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>2.3 Two processes using different locking protocols</h3> <p>The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking, but there are other options. By selecting an alternative [sqlite3_vfs] using the [sqlite3_open_v2()] 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 others locks and will not be able to coordinate database access, possibly leading to database corruption.</p> <h2>3.0 Failure to sync</h2> <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 really 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> <h3>3.1 Disk drives that do not honor sync requests</h3> <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 [WAL | WAL mode] 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 [checkpoint] 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> <h3>3.2 Disabling sync using PRAGMAs</h3> <p>The sync operations that SQLite performs to help ensure integrity can be disabled at run-time using the [synchronous pragma]. 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> <h3>4.0 Disk drive failures</h3> <p>An SQLite database can become corrupt if the content changes on disk due to a disk drive failure. It is very rare, but disks will occasionally flip a bit in the middle of a sector.</p> <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 a MP3 file in flash memory when a power loss occurs, and that could result in an SQLite database being corrupted even though the database as not even in use at the time of the power loss.</p> <h2>5.0 Memory corruption</h2> <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 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> <h2>6.0 Other operating system problems</h2> <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> <h3>6.1 Linux Threads</h3> <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 where 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 version 3.7.0, 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> <h3>6.2 Failures of mmap() on QNX</h3> <p>There exists some subtle problem with mmap() on QNX such that making a second mmap() call against the 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 [WAL | WAL mode], 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> <h2>7.0 Bugs in SQLite</h2> <p>SQLite is [testing | very carefully tested] to help ensure that it is as bug-free as possible. Among the many tests that are carried out for every SQLite version are tests that simulate power failures, I/O errors, and out-of-memory (OOM) errors and verify that no database corrupt occurs 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 two-year period from 2009-04-01 to 2011-04-01. 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> <h3>7.1 False corruption reports due to database shrinkage</h3> <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 begin overly zealous in its corruption detection.</p> <p>The problem was fixed on 2011-02-20. The fix first appears in SQLite version 3.7.6.</p> <h3>7.2 Corruption follow switches between rollback and WAL modes</h3> <p>Repeatedly switch an SQLite database in and out of [WAL | WAL mode] and running the [VACUUM] 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> <h3>7.3 I/O while obtaining a lock leads to corruption</h3> <p>If the operating system returns an I/O error while attempting to obtain a certain lock on shared memory in [WAL | WAL mode] 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 really 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> <h3>7.4 Database pages leak from the free page list</h3> <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 but 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 [incremental_vacuum] 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 [integrity_check pragma] 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> <h3>7.5 Corruption following alternating writes from 3.6 and 3.7.</h3> <p>SQLite version 3.7.0 introduced a number of new enhancements to the SQLite database file format (such as but not limited to [WAL]). 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> |