How To Corrupt An SQLite Database File
Small. Fast. Reliable.<br>Choose any three.
Home<br>Menu<br>About<br>Documentation<br>Download<br>License<br>Support<br>Purchase
Search
About<br>Documentation<br>Download<br>Support<br>Purchase
Search Documentation<br>Search Changelog
How To Corrupt An SQLite Database File
Table Of Contents<br>1. File overwrite by a rogue thread or process
1.1. Continuing to use a file descriptor after it has been closed
1.2. Backup or restore while a transaction is active
1.3. Deleting a hot journal
1.4. Mispairing database files and hot journals
2. File locking problems
2.1. Filesystems with broken or missing lock implementations
2.2. Posix advisory locks canceled by a separate thread doing close()
2.3. Multiple copies of SQLite linked into the same application
2.4. Two processes using different locking protocols
2.5. Unlinking or renaming a database file while in use
2.6. Multiple links to the same file
2.7. Carrying an open database connection across a fork()
3. Failure to sync
3.1. Disk drives that do not honor sync requests
3.2. Disabling sync using PRAGMAs
4. Disk Drive and Flash Memory Failures
4.1. Non-powersafe flash memory controllers
4.2. Fake capacity USB sticks
5. Memory corruption
6. Other operating system problems
6.1. Linux Threads
6.2. Failures of mmap() on QNX
6.3. Filesystem Corruption
7. SQLite Configuration Errors
8. Bugs in SQLite
8.1. Race condition when writing to a WAL-mode database.
8.2. Stale expression indexes
8.3. False corruption reports due to database shrinkage
8.4. Corruption following switches between rollback and WAL modes
8.5. I/O error while obtaining a lock leads to corruption
8.6. Database pages leak from the free page list
8.7. Corruption following alternating writes from 3.6 and 3.7
8.8. Race condition in recovery on Windows systems
8.9. Boundary value error in the secondary journals used by nested transactions
Overview
An SQLite database is highly resistant to corruption.<br>If an application crash, or an operating-system crash, or even<br>a power failure occurs in the middle of a transaction, the partially<br>written transaction should be automatically rolled back the next time<br>the database file is accessed. The recovery process is fully<br>automatic and does not require any action on the part of the user<br>or the application.
Though SQLite is resistant to database corruption, it is not immune.<br>This document describes the various ways that an SQLite database might<br>go corrupt.
1. File overwrite by a rogue thread or process
SQLite database files are ordinary disk files.<br>That means that any process can open the file and<br>overwrite it with garbage. There is nothing that the SQLite<br>library can do to defend against this.
1.1. Continuing to use a file descriptor after it has been closed
We have seen multiple cases where a file descriptor was open on a file,<br>then that file descriptor was closed and reopened on an SQLite database.<br>Later, some other thread continued to write into the<br>old file descriptor, not realizing that the original file had been closed<br>already. But because the file descriptor had been reopened by SQLite,<br>the information that was intended to go into the original file ended up<br>overwriting parts of the SQLite database, leading to corruption of the<br>database.
One example of this occurred circa 2013-08-30 on the canonical repository<br>for the Fossil DVCS. In that event,<br>file descriptor 2 (standard error) was being erroneously closed (by<br>stunnel, we suspect) prior to<br>sqlite3_open_v2() so that the file descriptor used for the<br>repository database file was 2. Later, an application<br>bug caused an assert() statement to emit<br>an error message by invoking write(2,...). But since file descriptor 2 was<br>now connected to a database file, the error message<br>overwrote part of the database. To guard against this kind of problem,<br>SQLite version 3.8.1 (2013-10-17)<br>and later refuse to use low-numbered file descriptors<br>for database files.<br>(See SQLITE_MINIMUM_FILE_DESCRIPTOR for additional information.)
Another example of corruption caused by using a closed file<br>descriptor was<br>reported by facebook engineers in a blog post on 2014-08-12.
Another example of this error was reported against<br>Fossil on 2019-07-11. A file descriptor would<br>be opened for debugging output, but then closed and reopened by SQLite.<br>But the debugging logic continued to write into the original file<br>descriptor. See the<br>forum discussion<br>for the bug report and a link to the fix.
1.2. Backup or restore while a transaction is active
Systems that run automatic backups in the background might try to<br>make a backup copy of an SQLite database file while it is in the middle<br>of a transaction. The backup copy then might contain some old and some<br>new content, and thus be corrupt.
There are multiple safe approaches to making backup copies of<br>SQLite databases - safe in the sense that they are generate a correct,<br>uncorrupted backup. In no particular order:
The sqlite3_rsync utility program (available beginning...