SQLite Forum

Troubleshooting corrupt indexes
Posting this on behalf of a colleague who "doggedly denies the existence of the new forum":

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…

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