pragma integrity_check; Error: disk I/O error
(1) By markd (markdub) on 2021-08-12 14:58:27 [link] [source]
Hi, while checking a places.sqlite Mozilla's bookmarks DB, I got pragma integrity_check; Error: disk I/O error that's on Windows 10 though [sqlite3 dll+tools SQLite 3.36.0 2021-06-18], whereas on (Debian, SQLite 3.16.2 2017-01-06) Linux the check yields just 'ok'. then following hunchs from othe bugs citing I/O error, tried: sqlite> vacuum; sqlite> pragma integrity_check; *** in database main *** Page 35: unable to get the page. error code=266 Page 34: unable to get the page. error code=266 Page 1275 is never used Page 1276 is never used (many times more) ... Error: disk I/O error whereas on Linux same sequence yields just 'ok'. Then again sqlite> vacuum; Error: disk I/O error retrying full check sqlite> pragma integrity_check; Error: disk I/O error retrying by single table sqlite> .tables moz_anno_attributes moz_historyvisits moz_keywords moz_annos moz_hosts moz_meta moz_bookmarks moz_inputhistory moz_origins moz_bookmarks_deleted moz_items_annos moz_places sqlite> pragma integrity_check(moz_bookmarks); ok sqlite> pragma integrity_check(moz_places); ok sqlite> pragma integrity_check(moz_annos); ok sqlite> pragma integrity_check(moz_anno_attributes); ok sqlite> pragma integrity_check(moz_bookmarks_deleted); ok sqlite> pragma integrity_check(moz_historyvisits); ok sqlite> pragma integrity_check(moz_hosts); ok sqlite> pragma integrity_check(moz_items_annos); ok sqlite> pragma integrity_check(moz_origins); ok but all again fails sqlite> pragma integrity_check; Error: disk I/O error the fs is NTFS, if that matters. Any cues? thx
(2) By Stephan Beal (stephan) on 2021-08-12 15:03:37 in reply to 1 [link] [source]
that's on Windows 10 though [sqlite3 dll+tools SQLite 3.36.0 2021-06-18], whereas on (Debian, SQLite 3.16.2 2017-01-06) Linux the check yields just 'ok'.
Are you perchance accessing that db via a virtual filesystem mounted in the other OS? If so, i recommend eliminating that middleman and trying again. It seems highly unlikely that, on a local/native/non-virtual filesystem, you could get different results for bitwise-identical copies of a database file. It is never recommended to use sqlite db's across remote mounts, e.g. SMB or NFS, or virtualization layers which abstract a host OS's storage to a guest OS. That Way Lies Madness.
(3) By markd (markdub) on 2021-08-12 22:12:35 in reply to 2 [link] [source]
nope, plain local filesystem. Funny, quitted then reloaded the DB checked as per original post: last integrity_check got I/O error, now instead (started with -stats): sqlite> pragma integrity_check; ok Memory Used: 2245720 (max 2278664) bytes Number of Outstanding Allocations: 690 (max 708) Number of Pcache Overflow Bytes: 1386928 (max 1419696) bytes Largest Allocation: 658560 bytes Largest Pcache Allocation: 32928 bytes Lookaside Slots Used: 123 (max 123) Successful lookaside attempts: 212 Lookaside failures due to size: 1 Lookaside failures due to OOM: 233 Pager Heap Usage: 2050152 bytes Page cache hits: 977275 Page cache misses: 139191 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 12024 bytes Statement Heap/Lookaside Usage: 77840 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 6075238 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 77840 got 'ok' yet I/O error was expected, since nothing (in the DB) should have changed between quit/reload. then re-tried with original copy of the DB, and got error again: sqlite> pragma integrity_check; Memory Used: 2282200 (max 2291816) bytes Number of Outstanding Allocations: 1119 (max 1129) Number of Pcache Overflow Bytes: 1995760 (max 2004112) bytes Largest Allocation: 85120 bytes Largest Pcache Allocation: 4256 bytes Lookaside Slots Used: 123 (max 123) Successful lookaside attempts: 212 Lookaside failures due to size: 1 Lookaside failures due to OOM: 227 Pager Heap Usage: 2080408 bytes Page cache hits: 460749 Page cache misses: 63695 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 12024 bytes Statement Heap/Lookaside Usage: 77840 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 1881471 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 77840 Error: disk I/O error any cues from above? is there a way to make sqlite3 spit some log? thx
(4) By Stephan Beal (stephan) on 2021-08-12 22:21:38 in reply to 3 [source]
Funny, quitted then reloaded the DB checked as per original post: last integrity_check got I/O error, now instead (started with -stats):
This sounds like a side effect of a virus scanner.
(5) By Simon Slavin (slavin) on 2021-08-13 13:06:22 in reply to 4 [link] [source]
While checking that, also check that Mozilla isn't actually running while you're doing the integrity check. SQLite is meant to take care of that problem, but there's a chance that Mozilla assumes that it has sole access to that database file.
(6) By markd (markdub) on 2021-08-13 16:25:31 in reply to 5 [link] [source]
oh ... k. Nope, DB wasn't in use by any Moz / other process last run caught some additional log: sqlite> pragma integrity_check; *** in database main *** Page 6835: unable to get the page. error code=266 Page 6834: unable to get the page. error code=266 Page 6833: unable to get the page. error code=266 Memory Used: 2282200 (max 2292088) bytes Number of Outstanding Allocations: 1119 (max 1137) Number of Pcache Overflow Bytes: 1995760 (max 2004112) bytes Largest Allocation: 85120 bytes Largest Pcache Allocation: 4256 bytes Lookaside Slots Used: 123 (max 123) Successful lookaside attempts: 572 Lookaside failures due to size: 5 Lookaside failures due to OOM: 1139 Pager Heap Usage: 2080408 bytes Page cache hits: 198230 Page cache misses: 30157 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 12024 bytes Statement Heap/Lookaside Usage: 77840 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 861970 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 77840 Error: disk I/O error then I tried a batch of integrity_check: Error: near line 1: disk I/O error Error: near line 2: disk I/O error ok Error: near line 4: disk I/O error Error: near line 5: disk I/O error *** in database main *** Page 7225: unable to get the page. error code=266 Page 7176: unable to get the page. error code=266 Page 7115: unable to get the page. error code=266 Page 4993: unable to get the page. error code=266 Page 4992: unable to get the page. error code=266 Page 4937: unable to get the page. error code=266 Error: near line 6: disk I/O error Error: near line 7: disk I/O error Error: near line 8: disk I/O error Error: near line 9: disk I/O error Error: near line 10: disk I/O error ok Error: near line 12: disk I/O error Error: near line 13: disk I/O error while monitoring the cpu/mem usage of MS' antimalware which seemed to spike, so the AV interference might be a thing. At this point I fired windows-update & reboot ... now all seem fine: sqlite> .read ic.sql ok ok ok ok ok ok ok ok ok ok ok ok ok each 'ok' is from a integrity_check line in the script. So whether the culprit was some system bug, AV bug or just memory crap piled up is not clear, but the AV interference sound a sensible option. Wonder if the lib/tool can be made more robust on that. Anyway, so far my issue seems gone, thanx all!
(7) By Stephan Beal (stephan) on 2021-08-13 16:40:46 in reply to 6 [link] [source]
So whether the culprit was some system bug, AV bug or just memory crap piled up is not clear, but the AV interference sound a sensible option. Wonder if the lib/tool can be made more robust on that.
AV interference is not an uncommon problem on Windows. Libraries like sqlite have zero insight into what such a tool is doing and how it may be interfering. The "real" (if snarky) answer is to make the OS more robust so that it doesn't need AV running 24/7 ;).
(8) By Scott Robison (casaderobison) on 2021-08-13 17:38:09 in reply to 7 [link] [source]
Just making it less successful / "popular" would do a lot to diminish the need for 24/7 AV. :)
That being said, I don't run AV 24/7 on my own hardware because I trust myself to not go do things that will be risky. On my other family members computers, I do not take the same approach!
(9) By Simon Slavin (slavin) on 2021-08-14 13:02:18 in reply to 6 [link] [source]
Page 35: unable to get the page. error code=266
and/or sometimes
Error: near line 1: disk I/O error
You appear to get different, random, errors each time you try an integrity check. Is there any chance that you have a real hardware failure here ? Your reports would all be explained if your storage device was developing a fault. You would start out getting occasional errors, and get more and more as the fault developed.
Can you run something irrelevant to SQLite that tests the partition's integrity ? 'chkdsk' or something ?
(10) By anonymous on 2021-08-14 16:53:54 in reply to 9 [link] [source]
Try a copy of the db file and rename both files, such that the original file will keep the HD occupied but you start using a different image on storage media.
(11) By Simon Slavin (slavin) on 2021-08-15 02:32:09 in reply to 10 [link] [source]
Yeah, but after taking the copy do an integrity check immediately to make sure it's not already corrupt.
We had a thread here from someone who insisted that their database kept getting corrupted. But it turned out that after the database file failed the integrity check they continued working with it, apparently thinking that SQLite automatically fixed all the problems integrity_check reported.