On 28 December 2017 at 02:55, Simon Slavin <[email protected]> wrote:
> On 27 Dec 2017, at 6:10pm, Nikhil Deshpande <[email protected]> wrote: > > >> Can you include a "pragma integrity_check" at startup ? > >> Can you include a "pragma integrity_check" executed at regular > intervals ? > > The writer process does "pragma quick_check" on every startup at init, > > bails out on failure and spawns a separate thread to do same > > "pragma quick_check" every 5 minutes (opens it's own separate DB handle > > and closes it). Would changing quick_check to integrity_check be > > helpful? (Would integrity_check catch such corruption earlier than > > quick_check? Would it hold longer exclusive locks on the DB file > > that could prevent reads?) > > Changing "quick_check" to "integrity_check" may help in this case may be > useful in investigating your problem. "integrity_check" is far more > thorough and checks every aspect of data integrity from both directions. > However, the type of corruption you’re experiencing may be one which is > spotted just as well by "quick_check". There’s no way to know without > checking it. > I haven't checked the code but the docs say: https://sqlite.org/pragma.html#pragma_quick_check > The pragma is like integrity_check <https://sqlite.org/pragma.html#pragma_integrity_check> except that it does not verify UNIQUE constraints and does > not verify that index content matches table content. By skipping UNIQUE and index consistency checks, > quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same. Based on my personal experience/testing, quick_check still reads the entire DB. The only type of corruption I've ever seen integrity_check report which quick_check didn't was an index lacking an entry for a particular ROWID (in an otherwise well-formed DB). Coupled with Nikhil's tests I'm certain quick_check will catch this type of corruption. So the only scenario left to worry about is an index corruption (ie. one only detected by integrity_check) happening first, which somehow leads to a DB page corruption AND masks the original index corruption. Doesn't seem likely, but switching to integrity_check would rule out any chance. It does take longer than quick_check, but how much longer will depend on how big your indices are. On a 2GB DB I've measured 200 seconds for quick_check vs 300 seconds for integrity_check, but that was over NFS. I don't think either check takes an EXCLUSIVE lock on the DB? But a competing writer might, which ends up locking out readers until the check finishes (and the write completes). I agree with Simon you're not doing anything obviously wrong... This is listed in the how to corrupt page, but I'll ask anyway because it's a subtle one: do any of your processes open the database file, for any reason, without going through sqlite's API? -Rowan _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
