Database disk image is malformed

Support
22

Hello, folks,

I know it’s a few months later but I just had this issue myself and I think I found a solution:

@jonmikeiv, Maybe the person with a lot of knowledge on the DB can chime in on if this method is inappropriate . But, here is what I did.

First, stop services, as a precaution.

Now, make a backup copy of the {fooo}.sqlite file.

Next, load the bad DB in your favourite SQLite editor. I use DBeaver, but the exact tool is un important.

Then I did:

pragma integrity_check;

/* RESULT: (takes forever)
	integrity_check                             |
	--------------------------------------------|
	row 34538 missing from index FilePath       |
	row 56151 missing from index FilePath       |
	row 56162 missing from index FilePath       |
	row 172197 missing from index BlockHashSize |
	row 220677 missing from index BlockHashSize |
	wrong # of entries in index BlockHashSize   |
 */

As we can see, the errors solely affected indexes. So, I investigated the DDL of the tables and recovered that the indexes were created like this:

CREATE UNIQUE INDEX "FilePath" ON "File" ("Path", "BlocksetID", "MetadataID");
CREATE UNIQUE INDEX "BlockHashSize" ON "Block" ("Hash", "Size");

We now have enough information to remove the corrupt indexes and recreate them.

drop index "FilePath";
drop index "BlockHashSize";

CREATE UNIQUE INDEX "FilePath" ON "File" ("Path", "BlocksetID", "MetadataID");
CREATE UNIQUE INDEX "BlockHashSize" ON "Block" ("Hash", "Size");

It was fast for me, but index creation could be quite slow.

pragma integrity_check;

/* SUCCESS!
	integrity_check |
	----------------|
	ok              |*/

Then, all I did was copy the db file back over the original and restart services.

I now am able to complete the backup without error:

vivaldi_2018-03-11_21-12-53|513x205

When the error was occuring, the backup halted.

For me, the problem started at about the time that an upgrade didn’t succeed.

So, while I’m not super familiar with SQLite, it seems that at least in cases where this error affects indexes, manual intervention can help.

Though, the obvious question is ‘why did this happen?’ (I have preserved the bad database in case devs !have questions.)

4 Likes