3 months ago
- SQLite databases are highly resistant to corruption, with automatic rollback on crashes, but they are not immune.
- Corruption can occur due to file descriptor misuse, such as overwriting database files with unrelated data.
- Background backup processes copying during transactions can create corrupt backups; safe methods include sqlite3_rsync, VACUUM INTO, and the backup API.
- Moving, deleting, or renaming hot journal files (like *-journal or *-wal) after a crash prevents recovery and may corrupt the database.
- File locking issues, especially on network filesystems or with POSIX advisory locking quirks, can lead to corruption if multiple processes write concurrently.
- Using multiple links (hard or symbolic) to a database file can cause undefined behavior and potential corruption.
- Forking a process with an open SQLite connection and using it in the child can result in locking problems and corruption.
- Storage devices lying about sync operations (e.g., USB sticks) may cause out-of-order writes, leading to corruption, mitigated by using WAL mode.
- Disabling SQLite protections like PRAGMA synchronous=OFF increases corruption risk during crashes or power failures.
- Bugs in SQLite, though rare, have historically caused corruption, such as the WAL-reset bug and issues with expression indexes.