> On 5 Mar 2015, at 7:28am, Alexandr N?mec <a.nemec at atlas.cz> wrote: > > Hi Simon, > >> Does SQLite automatically detect and uncorrupt these problems the next time >> it opens the database, >> or has something been done to the hardware to break in-order writing ? > > No. The database can be opened successfully, but a simple "select" ends up > with "database disk image is malformed" in the command line shell. I can, > however, ".dump" the database. Using the SQLite API, the "sqlite3_prepare_v2" > function ends up with error code 11 (SQLITE_CORRUPT).
Okay. A few things, all of them agreeing with your description. The _open() call doesn't actually access the database files. It just makes a note of where they are for later use. So to have SQLite automatically detect and recover from unfinished changes it's necessary to issue at least one SQL command that reads something from the database file. For example, "SELECT rowid FROM atable LIMIT 1". The '.dump' command reads the table without using searching or sorting, so it would not spot a corrupt index. So from all the above it's likely that the data in your tables is okay, and only the indexes in that file are corrupt. So dropping and recreating your indexes should fix the file perfectly unless the violation of the UNIQUE requirement is in the primary index for a table. > I don't understand exactly, what you mean by "breaking in-order writing". > The database has been running on a Windows 2012 R2 server and was placed > (with a lot of other data) on a RAID-5 disk array made to one logical disk > for the server. AFAICT it was a pretty standard Windows 2012 configured > server. That does sound like a common setup and it is subject to corruption. 'in-order writing' is the assurance from the computer to the programmer that if they issue a number of write commands to disk, they will be executed in the order they were given rather than something like 1, 2, 5, 4, 3. Without in-order writing, it is impossible to maintain a database in a trustworthy manner since whatever locking or corruption-checking you do can be easily defeated. For more on this see <http://www.sqlite.org/src/tktview/ff5be73dee?plaintext> <https://lkml.org/lkml/2009/4/7/523> <http://www.proximaldata.com/blog/caching/the-technical-implications-of-write-back-cache/> Unfortunately, most disk subsystems do not enforce in-order writing because it is far faster to write changes to disk in an order given by the fastest path which covers all parts of the disk which need to be written. For a normal computer used mainly for Web/Word/Email/Games this doesn't matter. For a computer used for databases it does matter. Many Server-class hard disks or hard disk drivers come with settings which enforce correct behaviour (at the costs of slowing down writing). Some RAID software has settings which do the same. You might find these settings near, or part of, the ones to do with caching. Unfortunately I don't know enough about Windows Server to advise you. Simon.
