> So any idea what might have caused this, a good way to fix it, or detect > it at runtime? The other threads that I have read point to either > multithreading issues, or an ungraceful shutdown.
And they're right, except that ungraceful shutdown will hurt your database only if you set pragma synchronous = off and multithreading issues will hurt if you compiled SQLite with threading turned off and then tried to use SQLite from multiple threads... And there's not much you can do about the problems themselves. Just try to .dump it from sqlite3 command line utility and recreate database from the dump. And don't think about detecting such problems in runtime, think about fixing your application so that these problems don't appear. Pavel On Fri, Oct 2, 2009 at 9:13 AM, Reusche, Andrew <andrew.reus...@scientificgames.com> wrote: > I have an issue with a sqlite3 db. I have a table that yields invalid > data in some queries. > > > > > > Select sales.* > > From sales > > Where sales.record_type = 1 > > Order By sales.datetime Desc > > > > Yields the top 124 or so records as all duplicates, and the record_type > column has a "0" in each record. By the query above, these shouldn't > show. What's worse is that there is really only one instance of this > record in the row: > > > > Select sales.*, sales.sales_id > > From sales > > Where sales.sales_id = 617284 > > Order By sales.datetime Desc > > > > This query yields 1 value. The original query in this email returned > 124 copies of this record. > > > > More troubleshooting: > > > > sqlite> select max(sales_id) from sales; > > 617283 > > > > This value returned for max is actually less than the value returned in > the query with the bogus results. > > > > sqlite> pragma integrity_check; > > rowid 609189 missing from index sales_datetime > > rowid 609190 missing from index sales_datetime > > rowid 609191 missing from index sales_datetime > > rowid 609192 missing from index sales_datetime > > rowid 609193 missing from index sales_datetime > > rowid 609194 missing from index sales_datetime > > rowid 609195 missing from index sales_datetime > > rowid 609196 missing from index sales_datetime > > rowid 609197 missing from index sales_datetime > > rowid 609198 missing from index sales_datetime > > rowid 609199 missing from index sales_datetime > > rowid 609200 missing from index sales_datetime > > rowid 609201 missing from index sales_datetime > > rowid 609202 missing from index sales_datetime > > rowid 609203 missing from index sales_datetime > > rowid 609204 missing from index sales_datetime > > rowid 609198 missing from index sales_datetime > > rowid 609199 missing from index sales_datetime > > rowid 609200 missing from index sales_datetime > > rowid 609201 missing from index sales_datetime > > rowid 609202 missing from index sales_datetime > > rowid 609203 missing from index sales_datetime > > rowid 609204 missing from index sales_datetime > > rowid 625656 missing from index sales_datetime > > rowid 625657 missing from index sales_datetime > > rowid 625658 missing from index sales_datetime > > rowid 625661 missing from index sales_datetime > > rowid 625662 missing from index sales_datetime > > rowid 625663 missing from index sales_datetime > > rowid 625664 missing from index sales_datetime > > rowid 625665 missing from index sales_datetime > > rowid 625666 missing from index sales_datetime > > wrong # of entries in index sales_datetime > > > > then I run reindex. Then another integrity_check: > > > > > > sqlite> pragma integrity_check; > > wrong # of entries in index sales_datetime > > > > > > > > So any idea what might have caused this, a good way to fix it, or detect > it at runtime? The other threads that I have read point to either > multithreading issues, or an ungraceful shutdown. > > > > > > Thanks in advance > > > > > > Andrew > > > > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
