Hello, I've used sqlite for a number of years now. I ran in to something over the week-end like nothing I've experienced before. An application linked against 3.6.18 corrupted one of my databases. The application did not crash or misbehave, the only reason that I discovered the problem is that it runs "PRAGMA integrity_check;" periodically. I got the following errors: Attempts to reproduce this scenario from a healthy database have failed.
sqlite> PRAGMA integrity_check; rowid 13 missing from index sqlite_autoindex_MetricDefn_1 rowid 20 missing from index sqlite_autoindex_MetricDefn_1 rowid 21 missing from index sqlite_autoindex_MetricDefn_1 rowid 22 missing from index sqlite_autoindex_MetricDefn_1 rowid 23 missing from index sqlite_autoindex_MetricDefn_1 rowid 24 missing from index sqlite_autoindex_MetricDefn_1 rowid 25 missing from index sqlite_autoindex_MetricDefn_1 rowid 26 missing from index sqlite_autoindex_MetricDefn_1 rowid 28 missing from index sqlite_autoindex_MetricDefn_1 rowid 29 missing from index sqlite_autoindex_MetricDefn_1 rowid 30 missing from index sqlite_autoindex_MetricDefn_1 rowid 31 missing from index sqlite_autoindex_MetricDefn_1 rowid 32 missing from index sqlite_autoindex_MetricDefn_1 rowid 33 missing from index sqlite_autoindex_MetricDefn_1 rowid 34 missing from index sqlite_autoindex_MetricDefn_1 wrong # of entries in index sqlite_autoindex_MetricDefn_1 I dumped the database using the command line .dump utility and tried to create a the database fresh with the data. This table had trouble because a UNIQUE constraint on the table was violated. The table definition looks roughly like this: CREATE TABLE IF NOT EXISTS MetricDefn ( Id INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE, Owner INTEGER NOT NULL, Name TEXT NOT NULL, Type TEXT NOT NULL, ... data fields with no constraints or indexes ... UNIQUE(Owner, Name, Type) ON CONFLICT ABORT ); I only perform simple INSERT INTO ... (...) VALUES (...) and DELETE FROM ... WHERE Id = ... operations on this table. I actually don't think that my DELETE code is ever called anywhere yet. The INSERT operation on this table is the only operation modifying it. In the dump file, I found that rows 13 and 20-25 (all mentioned in the errors above) conflicted with records later in the file with higher row id numbers. On further inspection, all of the rows mentioned above have newer versions with higher ids. It just happens that in some of the rows, the Type field changed and so the UNIQUE constraint was not violated. Typically, if some of the data in a record changes, I perform an INSERT with the same Id and the record gets replaced. In this case, it appears that this didn't happen correctly and new rows got inserted with new rowids Could my use of the ON CONFLICT REPLACE algorithm have triggered a bug in sqlite? Could there be anything else going on here? Thank you, Carl Baldwin _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
