Discuss SQLite database corruption and repair, and discuss sqlite Database

Discuss SQLite database corruption and repair, and discuss sqlite Database
Last night, my friend and I reported whether the SQLite database can be recovered due to damage. The general situation is that when the database is used, it accidentally overwrites the database with new files, resulting in damage to the SQLite database. When it is opened, a password is required, the SQL statement cannot be dumped. Therefore, this article will describe all the damages to the SQLite database and how to fix the damaged SQLite database files.
SQLite is a very stable database and is not prone to damage. Even if the application crashes, the operating system crashes, or even a power failure occurs during transaction execution, it can be automatically repaired when the database is used for the next time. However, we still cannot avoid damages.
The damage to the SQLite database can be roughly classified into four categories: file coverage, file lock, data synchronization, and memory.
File Overwrite is possible for SQLite database files to be overwritten. After all, it is a common disk file, which means that all processes can be opened and overwritten, so it is impossible to completely avoid file overwrite.1. multi-thread database writing.SQLite Database supports multi-process concurrent read/write. However, if you close and re-open the database at this time, some threads may still write data to the database, and some data may be overwritten.
2. Back up or restore data during transaction executionA transaction is a procedural operation that takes some time, and data backup is an atomic operation. If a transaction is backed up during execution, the copied content may contain some new content and some old content, and the database is damaged. The same is true for restoration.
3. Delete log filesSQLite databases usually store all the content to a file. However, when a transaction is executed, logs can be rolled back to realize program crash, which is accompanied by some additional log files. If the log is deleted, an exception occurs during recovery.
To achieve concurrent read/write of the SQLite database, SQLite uses the file lock to ensure data security.1. System File lock problemsSQLite depends on the implementation of File locks by the underlying file system. However, some file systems have lock logic errors, making the locks unreliable, which is common in network file systems and NFS.
2. POSIX collaborative lock (advisory lock)In linux or unix, SQLite locks are collaborative locks by default. When a process uses a collaborative lock, if one of the threads executes close (), the lock may be canceled. If two threads are already connected to the same database at the same time, another thread reads the database (open (), read (), then close () will cause the database lock of the process to be canceled, and operations on the database by two threads at the same time will lead to data overwriting and confusion.
3. Different connection protocolsDifferent connection protocol locks may also be different, which leads to errors caused by the failure of the lock.
4. delete or rename database files when the database is in useThis is often the case in POSIX systems such as linux. This is not the case in windows, and at the same time, transaction execution will enlarge this problem.
To ensure data consistency, SQLite sometimes requests the operating system to fl all the data waiting for persistence into the disk and then wait until the operation is completed.1. the disk drive synchronization request may be unreliableMost existing disk drives at the general consumption level will falsely report the data synchronization results to achieve a higher write speed. When the data has just arrived at the disk buffer and has not been actually written into the oxide media, the disk drive reports that the content has been securely written. However, data synchronization may fail due to power failure or hardware reset. This situation mainly occurs in Flash Media.
2. Using PRAGMAs will affect synchronization.By setting PRAGMA synchronous = OFF, all SQLite synchronization operations are ignored. This allows SQLite to run faster, but if a power failure or hardware reset occurs, all the data previously stored will be performed. For maximum data reliability and robustness, you can set synchronous = FULL for SQLite.
Memory problems as a C Runtime Library, SQLite and applications using it run in the same memory address space. This means that any wild pointer, buffer overflow, and heap corruption may damage the SQLite data structure and eventually cause database file damage. In addition, memory problems become more serious when I/O models (such as mmap) are mapped to memory. When some or all of the database files are mapped to the address space of the application, although the file I/O operations are reduced, the wild pointer may access and modify the data of any part of the ing space.
For more information about the causes of SQLite database corruption, see here.

Fix corrupted SQLite database in 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"

You can download sqlite3.exe here. Of course, these Apis only repair corrupted databases in certain programs and cannot solve all the problems.
Here are four suggestions for using SQLite: 1. Reduce multi-process or multi-thread operations and write data in a single thread as much as possible. 2. Reduce transaction operations, transaction complexity, and check points. 3. Reduce database size. 4. Avoid using PRAGMA synchronous = OFF.
Reference: http://blog.csdn.net/mycwq/article/details/45541409