Inline... On Mon, Feb 28, 2011 at 04:37:13PM -0700, Carl Baldwin wrote: > 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?
I find it unlikely that the ON CONFLICT REPLACE algorithm has triggered a bug, otherwise it is likely you'd be able to reproduce the problem. The ON CONFLICT ABORT on the UNIQUE constraint is the default and therefore redundent, so will be a well tested code path with low probability of non-reproducable failure. Do the problem Id rows have similar values for Owner, Name, Type? Is it likely they were stored in the same index page, for example? Or perhaps correlated by time? Of course, bugs do happen, and SQLite has had corruption problems in the past with corner cases. But without a reproduceable test case, you may be out of luck. Some information on the underlying platform might help, such as OS, hardware. Have you seen other random looking errors on this machine? Machine crashes? Check also that your library is correctly compiled for threading if your application is threaded, and that your hard disks don't have any underlying errors (check for SMART errors) and PSU issues. Check out: http://www.sqlite.org/lockingv3.html#how_to_corrupt Christian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
