Troubleshooting corrupt indexes

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Troubleshooting corrupt indexes

Jens Alfke-2
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 [doggedly denying the existence of the new forum]

PS: We're using SQLite 3.28, statically linked into the app, on Android.
sqlite-users mailing list
[hidden email]