On 6/29/2016 1:04 AM, R Smith wrote:
These are the duplicate records. This index is associated with a
UNIQUE constraint on the original table. Is there a way to clean it up?
That means your DB is corrupted by some method. It's not bad yet, just
missing Index entries.
Make a copy of the database file, then try to delete those rows and
recreate all indices with:
DELETE FROM eventlog WHERE row_id IN
(18029,18030,18031,18032,18033,18034,18035);
REINDEX;
PRAGMA integrity_check;
If you do not delete the rows, it can't re-index because the Unique
Index constraint will fail since the rows are not unique.
If this worked, test all tables and queries. Be sure to have that
backup at hand - once integrity is broken, the DB state is not secure
until integrity check passes.
PS: Nice catch Simon - I did not even consider a broken index. I've
never had an SQLite file go corrupt on me, even though using many
SQLite DBs in all our systems, even as application file formats, so
I'm always a bit oblivious to that possibility.
starting from the original, corrupted DB...
integrity_check
row 18029 missing from index sqlite_autoindex_EventLog_1
row 18030 missing from index sqlite_autoindex_EventLog_1
row 18031 missing from index sqlite_autoindex_EventLog_1
row 18032 missing from index sqlite_autoindex_EventLog_1
row 18033 missing from index sqlite_autoindex_EventLog_1
row 18034 missing from index sqlite_autoindex_EventLog_1
row 18035 missing from index sqlite_autoindex_EventLog_1
wrong # of entries in index sqlite_autoindex_EventLog_1
delete from eventlog where rowid >=18029 and rowid <= 18035
(checked integrity again here, and got same result as above)
reindex
integrity_check
ok
vacuum
integrity_check
ok
Query for duplicates returns no records.
Thanks very much, Ryan and Simon. My application monitors wind turbines,
and there are somewhat frequent power issues at the sites. At one
particular site, I had frequent corruption on server crashes, but none
since changing SYNCHRONOUS from OFF to NORMAL. This particular site
still had SYNCHRONOUS OFF, but will now have NORMAL.
Joe
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
