SQLite Forum


2 forum posts by user borrrden

23:54 Post: Use of unitialized variable in 3.32.1 (artifact: 45a4a60854 user: borrrden)

In the analyzeOneTable function the following occurs:

for(j=0; j<pPk->nKeyCol; j++){
    k = sqlite3TableColumnToIndex(pIdx, pPk->aiColumn[j]);
    assert( k>=0 && k<pIdx->nColumn );
    sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, regKey+j);
    VdbeComment((v, "%s.column(%d)", pIdx->zName, i));

Note the use of i in the last line's comment. At least on GCC this gives a warning about the variable being possibly uninitialized and I think it was the intent to use j here.

00:05 Post: Troubleshooting corrupt indexes (artifact: 0ae020f9c9 user: borrrden)

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.