Hi, We got a report of a 'pragma integrity_check' failure on the user mailing list for our application [1]. There was no data loss involved (just the index was corrupted), but I thought you might want to take a look.
The database was written to by our application (SX 2.0) which uses SQLite 3.9.1, then the application got upgraded to a new version. Before the upgrade it always stops the application, starts an exclusive transaction and runs a 'pragma integrity_check' (with SQLite 3.12.2), which reported: integrity_check: row 312 missing from index idx_op_revision integrity_check: row 1497 missing from index idx_op_revision integrity_check: row 1912 missing from index idx_op_revision integrity_check: row 2552 missing from index idx_op_revision integrity_check: row 6031 missing from index idx_op_revision integrity_check: row 8543 missing from index idx_op_revision The corrupted database is available here: https://development.s3.touristed.com/hm00000009.db (according to the OP there was no WAL file) The OS was: Linux squirtle 3.13.0-86-generic #130-Ubuntu SMP Mon Apr 18 18:27:15 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux. Disk: dedicated server with hardware raid The database was successfully recovered by using .clone: sqlite> .clone /var/tmp/clone.db hashfs... done blocks... done avail... done reservations... done revision_blocks... done revision_ops... done sqlite_autoindex_hashfs_1... done sqlite_autoindex_blocks_1... done sqlite_autoindex_reservations_1... done idx_res... done idx_res_ttl... done sqlite_autoindex_revision_blocks_1... done idx_revmap... done sqlite_autoindex_revision_ops_1... done idx_op_revision... done sqlite> .open /var/tmp/clone.db sqlite> pragma integrity_check; ok The database is accessed with these PRAGMAs: PRAGMA busy_timeout = 20 PRAGMA synchronous = NORMAL PRAGMA case_sensitive_like = true PRAGMA cache_spill = false PRAGMA journal_size_limit = 15360000 According to our user [2] there were no forced shutdowns/powerlosses/reboots that he's aware of, and the log files didn't contain any errors/warnings from SQLite (our application logs messages from the SQLITE_CONFIG_LOG callback, although maybe the warning - if any - was so long ago it got due to logrotate). Since 'pragma integrity_check' is only run on upgrades this means I don't have a reproducible testcase, all I know is that the problem occured sometime between installing SX 2.0 and upgrading to SX 2.1 today. In fact had the application not run 'pragma integrity_check', SQLite's (probably) would've kept using the DB without errors. Also there are 47 more DBs with same schema, on the same disk: those all got an OK from 'pragma integrity_check', it is just one DB that failed. Let me know if you would like further information from our user. [1] http://www.skylable.com/community/mailing-list/lurker/message/20160512.184058.9bca5b76.en.html [2] http://www.skylable.com/community/mailing-list/lurker/message/20160512.202329.9aafdf9d.en.html Best regards, -- Edwin T?r?k | Co-founder and Lead Developer Skylable open-source object storage: reliable, fast, secure http://www.skylable.com
