SQLite Forum

pragma integrity_check; Error: disk I/O error
Login

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 [link] [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 [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.