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]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Locked
