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.