SQLite Forum

Troubleshooting corrupt indexes
Login

Troubleshooting corrupt indexes

(1) By Jens Alfke (snej) on 2020-04-13 19:23:23 [source]

We've found a few database instances with index corruption, and I'm unsure how to troubleshoot it.

The symptom is SELECT queries failing with SQLITE_CORRUPT. I've looked at the database files, and pragma integrity_check spits out five "row missing from index" errors on one index, and "wrong # of entries" on that same index and three others. Running REINDEX seems to repair everything, at least integrity_check reports no more problems.

The indexes aren't fancy. The one with the row-missing errors is simply

	CREATE UNIQUE INDEX seq ON table (sequence)
where the sequence column is simply defined as sequence INTEGER.

The other indexes are all of the form

	CREATE INDEX … ON table (customfn(body, 'key1'), customfn(body, 'key2'), …)
where customfn is a custom function that extracts key-value data from the structured blob in body, somewhat like the regular json_extract function.

I'm sure this custom function could conceivably cause this sort of error if it weren't properly deterministic, but it's been in use for a few years and pretty well tested, and I've never seen a bug with index corruption like this.

And I'm baffled how something as simple as the seq index could go awry…

—Jens

PS: We're using SQLite 3.28, statically linked into the app, on Android.

(2) By Richard Hipp (drh) on 2020-04-13 21:02:59 in reply to 1 [link] [source]

Are you able to share a corrupt database file with the developers?

(3) By Jens Alfke (snej) on 2020-04-13 21:59:48 in reply to 2 [link] [source]

I'll need to ask — the customer's shared the file with us under an NDA, and I'm not sure if that's transitive.

In the interim, one question: I'm aware of all the ways that the file itself can become corrupted. My mental model is that index corruption is different, since it's at a higher-level: the b-tree is intact, it just has incorrect data. Is that a fair assumption? Or is it feasible for file-level damage to manifest only as a bad index?

--Jens

(4) By Richard Hipp (drh) on 2020-04-13 22:03:27 in reply to 3 [link] [source]

We have encountered cases where random filesystem corruption seemed to only impact indexes. Perhaps that was just dumb luck. But there is precedent for that kind of thing.

(5) By Richard Hipp (drh) on 2020-04-13 22:04:16 in reply to 1 [link] [source]

Are you using any application-defined collating sequences?

(6) By Dan Kennedy (dan) on 2020-04-14 13:55:06 in reply to 1 [link] [source]

PS: We're using SQLite 3.28, statically linked into the app, on Android.

Is it possible that the same app is also using the stock Android SQLite to access the same db from a different thread? We've seen that cause corruption in the past.

(7) By Jens Alfke (snej) on 2020-04-14 17:14:55 in reply to 5 [link] [source]

Yes, but not in any of the affected indexes.

(8) By Jens Alfke (snej) on 2020-04-14 17:34:25 in reply to 1 [link] [source]

I've looked at the app's logs and found something useful.

2020-03-26 09:08:32.754 3561-3621/ SQLite error (code 11): database corruption at line 79626 of [884b4b7e50]

2020-03-26 09:08:32.755 3561-3621/ SQLite error (code 11): statement aborts at 7: [SELECT fl_result(_doc.key) FROM kv_default AS _doc WHERE (fl_value(_doc.body, 'class') = 'ticket') AND (_doc.flags & 1 = 0)] database disk image is malformed

Line 79626 of sqlite3.c is in fact if( res!=0 ) return SQLITE_CORRUPT_BKPT;, in the function handleDeferredMoveto.

Does that shed any light on what might be wrong?

(9) By Jens Alfke (snej) on 2020-04-14 17:48:37 in reply to 6 [link] [source]

According to the developer, they are not opening this database themselves, so it's only our library accessing it.

(10) By Richard Hipp (drh) on 2020-04-14 17:51:09 in reply to 8 [link] [source]

Not really. That fault can happen when a row is missing from an index. It doesn't cause the row to go missing. It just responds to a row that is already missing. And we already know that is a problem.

(11) By dosena on 2021-01-13 11:13:22 in reply to 5 [link] [source]

Are there known bugs where indexes with custom collating sequences get corrupted?

(I appear to be running into this; more details to follow)

(12) By Warren Young (wyoung) on 2021-01-13 11:49:46 in reply to 11 [link] [source]

If your custom collation isn’t strictly following the 4 relations documented here, it can corrupt the index, but the bug would be in your code in that case.