On 2016/06/29 6:36 AM, Joe Pasquariello wrote:
On 6/28/2016 4:52 PM, Simon Slavin wrote:
On 28 Jun 2016, at 11:22pm, Joe Pasquariello <j...@fenway.com> wrote:
SELECT udatetime,typeof(udatetime),udatetime-1415000934 FROM eventlog
where device like '%M14' and udatetime=1415000934
udatetime typeof(udatetime) udatetime-1415000934
1415000934 integer 0
PRAGMA integrity_check
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
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.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
