Last night, my friends and I feedback SQLite database corruption There is no way to recover. The general situation is this, when the database in use accidentally with a new file overwrite the database, resulting in the SQLite database corruption, open to enter a password, and can not be the SQL statement dump down. So, this article organizes all the corruption of SQLite database, and how to repair corrupted SQLite database files.
SQLite is a very stable database, it is not prone to corruption, even if the application crashes, or the operating system crashes, or even the execution of a transaction with a power outage, can be automatically repaired the next time the database. However, it is still not possible to avoid the occurrence of corruption.
scenarios that cause SQLite database corruptionSQLite database corruption can be attributed to 4 categories: File coverage issues, file lock issues, data synchronization issues, memory issues
file Overwrite issuesSQLite database file is overwritten is possible, after all, is an ordinary disk file, which means that all processes can be opened and overwritten, so it is not possible to completely avoid the situation of file coverage.
1. Multi-threaded Write database problems. SQLite database is to support multi-process concurrent read and write, but if you close and reopen the database at this time, it is likely that some threads are still writing data to the database, some of the data is overwritten.
2. Backing up or recovering data when executing a transactionA transaction is a procedural operation that takes time, and a data backup is an atomic operation that, if backed up during the execution of a transaction, can result in database corruption when the contents of the copy contain some new content and some old content. Recovery is the same.
3. Delete log filesSQLite databases are usually stored in all content to a file, but when executing a transaction, in order to implement a program crash, the log can be rolled back when power is lost, accompanied with some additional log files. If the log is deleted, it causes the recovery to occur unexpectedly.
File Lock IssuesIn order to realize the SQLite database concurrent reading and writing, SQLite uses file locks to ensure data security.
1. System file Lock ProblemSQLite relies on the implementation of file locks on the underlying file system, but some file systems have lock logic errors that make the locks unreliable, which is common in Network file systems and NFS scenarios.
2. POSIX co-lock (Advisory lock)Under Linux or UNIX, the SQLite default lock is a co-lock. When a process usesCooperative Lock,If one of the threads executes close (), it may cause the lock to be canceled. If there are already two threads connected to the same database at the same time, then a thread is not in the form of the SQLite API, which reads the database as a system file (open (), read (), and then Close ()), which causes the database lock of the process to be canceled. The simultaneous manipulation of the database by two threads can cause confusion in data coverage.
3. Different connection Protocolsof differentthe connection protocol lock may also be different, causing the lock to not play an error causing the error.
4. Delete or rename database files when the database is in useThis is often the case with a POSIX system such as Linux, which does not occur under Windows, and the problem is magnified by the fact that a transaction execution occurs.
Data Synchronization IssuesTo ensure data consistency,SQLite sometimes asks the operating system to flash all data that waits to be persisted to the disk, and then waits for the operation to complete.
1. Synchronization requests for disk drives may not be reliableDisk drives with existing general consumption levelsmostwill lie about the data synchronization results to expect higher write speeds. When the data has just reached the disk buffer and has not actually written to the oxide media, the disk drive is already securely written to the reported content. However, a power outage and a hardware reset can cause data synchronization to fail. This situation occurs primarily in flash media.
2. Using pragmas can affect synchronizationby setting pragma synchronous=off, all sync operations of SQLite are ignored. This makes SQLite run faster, but if there is a power failure or a hardware reset, all the data that was previously saved. For maximum data reliability and robustness, SQLite can be set to synchronous = Full
Memory IssuesSQLite as aC Runtime Library,and the application that uses it runs in the same memory address space. This means that any wild pointers, buffer overflows, heap corruptions, and so on, can damage theSQLite data structure, and eventually cause database file corruption. In addition, memory problems can become more severe when using memory-mapped I/O models (such as MMAP). When some or all of the database files are mapped to the address space of the application, although file IO operations are reduced, the wild pointers may be accessed and modified to any part of theMapping Spacedata.
More about the causes of SQLite database corruption can be seen here.
repairing a corrupted SQLite databaseUnder Linux: $ sqlite3 mydata.db ". Dump" | Sqlite3 New.dbwin: d:\>sqlite3 mydata.db. Dump > Mydata.sqld:\>sqlite3 new.db < Mydata.sqld:\>sqlite3 aa.db "pragma integrity_check"
Here you can download Sqlite3.exe of course, these APIs just fix the corrupted database in a certain program and cannot solve all the problems.
SQLite Usage RecommendationsHere are 4 tips: 1. Reduce multi-process or multi-threaded operations and write as single-threaded as possible. 2. Reduce transaction operations, reduce transaction complexity, and decrease checkpoint 3. Reduce the size of the database by 4. Avoid using pragma synchronous=off
Reference: http://blog.csdn.net/mycwq/article/details/45541409
Discuss SQLite database corruption and repair
