SQLite Forum

Troubleshooting corrupt indexes
Login

Troubleshooting corrupt indexes

(1) By Jim Borden (borrrden) on 2020-04-17 00:05:06 [link]

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.

(2) By Warren Young (wyoung) on 2020-04-17 00:48:28 in reply to 1

> I'm sure this custom function could conceivably cause this sort of error if it weren't properly deterministic

The function has to be more than simply deterministic. It has to obey all of the following restrictions lined out [in the docs][1]:

1.    If A==B then B==A.
2.    If A==B and B==C then A==C.
3.    If A<B THEN B>A.
4.    If A<B and B<C then A<C. 

If your collation function doesn't obey them all, corruption is possible.

[1]: https://sqlite.org/c3ref/create_collation.html