That sounds like it might a corrupt index since the data doesn't show up in a dump.
Does your select work if you drop the index? And have you done a "pragma integrity_check" ? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of herb...@gdls.com [herb...@gdls.com] Sent: Tuesday, June 05, 2012 1:38 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Database Corruption - Table data being overwritten Hello, I have run into a problem using SQLite in an embedded environment. Currently using version 3.7.5. (I know, I want to update, just have been locked in at this version for the time being) The power is routinely cut from the system, so I have JOURNAL_MODE = PERSIST because there were issues with the -journal file not being completely cleaned up with a power loss when the mode was left defaulted to DELETE. Everything works fine for a while, but then something will happen that corrupts the database. Rows from one table seem to be overwriting valid Rows in another table. No UPDATEs or DELETEs are ever performed on this database, so no data should ever be over-written. Even more bizarre, a .dump of "table1" will show: INSERT INTO "table1" VALUES(28274, 6, 10308); But this query returns nothing: SELECT * FROM table1 WHERE table1_id=28274; The invalid table1 data (28274, 6, 10308) seems to be a direct copy of valid data from table2. The .dump of table2 has: INSERT INTO "table2" VALUES(28274, 6, 10308, ' 277'); "28274" doesn't even make sense as a valid table1_id (which is never manipulated and set to auto-increment). "SELECT * FROM sqlite_sequence WHERE name='table1'" gives: table1|11985 So, "28274" is completely invalid. What could cause such corruption? A journal file problem? Is there a better option than "PERSIST" for systems that like to have the power pulled? Thanks for any help! Patrick Herbst. _______________________________________________ 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
