On 16 Mar 2015, at 6:35pm, Dave Dyer <ddyer-sqlite at real-me.net> wrote:
> This "fully repaired" database turned out to contain a duplicated set of > records which did not cause an indexing problem, but which should not have > occurred, and was consistent with a duplicated transaction. If this had > been caused by a program error - ie; I really inserted the records twice, > the database would not have been really damaged, and the shortcut repair I > tried first would have succeeded. You are correct in what you have noticed but the explanation is simpler and well-known. Because of the format in which SQLite keeps its indexes, you cannot always repair a corrupted index by deleting the rows which you think are corrupt. Sometimes you need to delete other rows too (for example those immediately before and after a corrupt row) and you need detailed analysis of what's wrong with the index to know exactly what you need to delete. If you do leave corrupt information in the index is can lead to additional corruption when you add more rows to those indexes. The only safe thing to do is to drop the index and remake it. Or do to something which does that (e.g. VACUUM). Simon.
