How to find out which table is affected by page corruption
(1) By Frank van wensveen (frankvw) on 2021-06-16 10:23:26 [link] [source]
I have a sqlite3 database which has become corrupted, most likely due to repeated power outages. Various selects return a "SQLite error SQLITE_CORRUPT (database disk image is malformed)" error.
So I ran an integrity check:
frankvw@dellfvw:~/tmp$ ./sqlite3 MyVideos116.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
*** in database main ***
Page 3284: btreeInitPage() returns error code 11
Page 3283: btreeInitPage() returns error code 11
Page 3282: btreeInitPage() returns error code 11
Error: database disk image is malformed
Question: how do I find out which tables are affected?
I tried to run the .recover command on the database, but ran into problems with the following:
CREATE TABLE "lost_and_found"(rootpgno INTEGER, pgno INTEGER, nfield INTEGER, id INTEGER, c0, c1, c2, [...] c2046, c2047);
which results in:
Error: near line 128760: too many columns on lost_and_found
Error: near line 128761: no such table: lost_and_found
Error: near line 128762: no such table: lost_and_found
and also has other problems:
Error: near line 34: UNIQUE constraint failed: version.rowid
Error: near line 35: UNIQUE constraint failed: bookmark.idBookmark
Error: near line 36: UNIQUE constraint failed: art.art_id
(repeated many times)
Any suggestion would be appreciated!
(2) By Richard Hipp (drh) on 2021-06-16 10:41:55 in reply to 1 [link] [source]
There is no simple utility program or function that you can run to figure out which tables are affected by database corruption. You have to do a deep analysis. You try running a command like:
showdb $YOURDATABASE pgidx
To get an idea of which each page in the file is used for. But badly corrupted pages might not show up in the output, as that utility is not able to trace them back to their use.
Using the "showdb" utility, you can dump the content of page 3282. I'm guessing you will find that it has been zeroed out by your hardware.
To get the "showdb" utility program, download the source code and run "make showdb".
(3) By Frank van wensveen (frankvw) on 2021-06-16 12:41:14 in reply to 2 [source]
Hi, Richard, and thanks for responding!
Sorry to be a pain, but I'm not managing to make showdb using the source in the 3.36.0 tarball:
frankvw@dellfvw:~/tmp/Unzip/sqlite$ make showdb
make: *** No rule to make target 'showdb'. Stop.
Any ideas? Tnx!
(4) By Larry Brasfield (larrybr) on 2021-06-16 13:17:50 in reply to 3 [link] [source]
The showdb utility is a target in the Makefile at the top of a source checkout. Perhaps your ~/tmp/Unzip/sqlite is not a directory containing a SQLite source checkout.
(5) By Richard Hipp (drh) on 2021-06-16 14:51:58 in reply to 3 [link] [source]
You need the canonical source tarball, not the "amalgamation" tarball. Get the canonical source tarball under the "Alternative Source Code Formats" section of the download page, or clone the Fossil repository.