views:

34

answers:

1

Thanks for reading and hopefully answering,

Due to legacy issues and company policy, I am working with SQLite 2.8.17 and have a few varchars as primary keys.

But when I run a *"pragma integrity_check"*, it reports "rowid missing from index" and "wrong # of entries in index" on the tables with the varchar primary key but the returned result is an OK.

Extended Constant DB access and usage (about a day of load testing) seems to result in a DB failure (integrity check returns FAIL) and the only explanation (via .explain) is the same errors as earlier ("rowid missing from index" and "wrong # of entries in index").

Can anyone help? Is there something wrong that I am doing?

Thanks.

A: 

If you vacuum the database, that should at the very least re-construct it without this integrity check failure. Then, try a day of access and usage, as you say, and see if the error is still present.

To vacuum a database, either enter the database in command line and type: VACUUM;, or use a command line:

sqlite2.exe mydb.db "vacuum;"

(although I seem to remember that SQLite2 needed a table name after VACUUM, so you might need to experiment a little. It will affect all tables nonetheless.)

MPelletier
No, SQLite doesn't need a table name. Providing no parameters vacuums the entire DB.There is no change after vacuuming, The errors persist. At first the **"rowid missing from index"** and **"wrong # of entries in index"** just get stated by the check and the integrity check still passes, after a while, the integrity check will fail and report a DB-sick state. A Vacuum or another integrity-check after that hangs my system.I don't know if the "rowid missing from index" and "wrong # of entries in index" errors from having a varchar PK is causing it, but it's the only lead so far.
JJeff