On 6 Jun 2012, at 1:03pm, herb...@gdls.com wrote: > 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;
There should be (at least) two copies of that number in the data file. One is in the copy of all rows. The other is in the index SQLite makes up so it can check for keys. I would guess that one copy is there and one isn't. > 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. Having now learned that your database is corrupt, please cure the corruption before continuing. Pointless doing more tests when you already know it's corrupt. Perhaps use .dump and .read or something. > What could cause such corruption? A journal file problem? Well, SQLite /should/ recover from sudden loss of power properly but it won't do so if fsync() doesn't work on your platform, and it may not. Read section 3.1 in <http://www.sqlite.org/howtocorrupt.html> and note that Flash memory implementations are especially bad at syncing. Or continue and read the rest of that page. > Is there a > better option than "PERSIST" for systems that like to have the power > pulled? Would it be possible in your implementation to close the database after any significant changes ? Perhaps close and reopen immediately after long runs of inserting. Or close and reopen after each 1000 operations, or each minute, or something ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
