Sorry, I cannot run a shell sqlite3 on my embedded system. I will explain it using your example.
It happen that sometime autoincrement begin to fail and new records overwrite the one at rowid =1 1|1|2|3|one|two|three replaced by 1|4|2|3|one|two|three 2|2|2|3|one|two|three 3|3|2|3|one|two|three if I add another record >insert into journal values(5,2,3,'one','two','three'); it overwrite last one as follow 1|4|2|3|one|two|three replaced by 1|5|2|3|one|two|three 2|2|2|3|one|two|three 3|3|2|3|one|two|three and by "select rowid, * from journal where rowid = 1" returns three times the same values with rowid = 1 1|5|2|3|one|two|three 1|5|2|3|one|two|three 1|5|2|3|one|two|three It appear that SQLite "keep in mind" the number of operations but the index for extracting info does not work fine. Thanks for any comment Alessandro From: "Black, Michael (IS)" <michael.bla...@ngc.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Date: 23/08/2011 17.07 Subject: Re: [sqlite] Autoincrement failure Your insert looks OK...can you run sqlite3 on your embedded system? Try this and see what you get...I ran this on an MMC I had and it worked OK. SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum ...> INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum, ...> TicketNum, ItemNum)); sqlite> insert into journal values(1,2,3,'one','two','three'); sqlite> select * from journal; 1|2|3|one|two|three sqlite> insert into journal values(2,2,3,'one','two','three'); sqlite> select rowid,* from journal; 1|1|2|3|one|two|three 2|2|2|3|one|two|three sqlite> insert into journal values(3,2,3,'one','two','three'); sqlite> select rowid,* from journal; 1|1|2|3|one|two|three 2|2|2|3|one|two|three 3|3|2|3|one|two|three Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of a.azzol...@custom.it [a.azzol...@custom.it] Sent: Tuesday, August 23, 2011 8:55 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Autoincrement failure table definition: "CREATE TABLE Journal (ClosureNum INTEGER, TicketNum INTEGER, ItemNum INTEGER, Date DATE, Time TIME, Item BLOB, PRIMARY KEY(ClosureNum, TicketNum, ItemNum))" example insert (sorry for cut&paste from my code...): sprintf(zSql, "INSERT INTO Journal VALUES(%i, %i, %i, '%s', '%s', '%s')", nv.dgfe_db_pntr->triad.closure_num+1, nv.dgfe_db_pntr->triad.ticket_num, nv.dgfe_db_pntr->triad.item_num, nv.dgfe_db_pntr->date, nv.dgfe_db_pntr->time, description); Rgds, Alessandro From: "Black, Michael (IS)" <michael.bla...@ngc.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Date: 23/08/2011 15.50 Subject: Re: [sqlite] Autoincrement failure Could you please show us your table definition and an example insert statement that your generating? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of a.azzol...@custom.it [a.azzol...@custom.it] Sent: Tuesday, August 23, 2011 8:35 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Autoincrement failure I'm using SQLite on embedded system (ARM processor) and DB file is on MMC memory. Normally INSERT operations of new records works fine. Perhaps, autoincrement failure is due to electrical pbm and is present only on few devices but I'm looking for a way to protect DB file against this pbm. This failure is a DB corruption invisible after simple INSERT (it returns 'no_error') and it's very dangerous (data seems correctly saved) Compiler is "armcc". My table has three primary key, autoincrement active and other description column like (rowid = 1) 1 1 1 mydescr1 mydescr2 mydescr3 (rowid = 2) 1 1 2 mydescr1 mydescr2 mydescr3 (rowid = 3) 1 1 3 mydescr1 mydescr2 mydescr3 (rowid = 4) 1 1 4 mydescr1 mydescr2 mydescr3 (rowid = 5) 1 2 1 mydescr1 mydescr2 mydescr3 (rowid = 6) 1 2 2 mydescr1 mydescr2 mydescr3 (rowid = 7) 1 2 3 mydescr1 mydescr2 mydescr3 (rowid = 8) 1 2 4 mydescr1 mydescr2 mydescr3 (rowid = 9) 1 3 1 mydescr1 mydescr2 mydescr3 (rowid =10) 1 3 2 mydescr1 mydescr2 mydescr3 (rowid =11) 1 3 3 mydescr1 mydescr2 mydescr3 (rowid =12) 2 1 1 mydescr1 mydescr2 mydescr3 ... etc Corruption is visible when first row is overwrited with three index different from 1,1,1 After that, any new record will be inserted automatically at rowid=1 How can I avoid this corruption? Or How can protect my DB against this kind of corruption? Thanks for any help Alessandro From: "Black, Michael (IS)" <michael.bla...@ngc.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Date: 23/08/2011 14.10 Subject: Re: [sqlite] Autoincrement failure Give the recent notice of a bug in gcc-4.1 what compiler are you using and how are you compiling? And can you reproduce this with a small example table? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of a.azzol...@custom.it [a.azzol...@custom.it] Sent: Tuesday, August 23, 2011 7:01 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Autoincrement failure Reindex procedure returns 'no_error' but rowid=1 is still present 37 times with same values How can I avoid to fall in this bug? It's very dangerous because any new INSERT operation terminate successfully but previous data has beeen overwrited with new one (on rowid =1) Many thanks, Alessandro From: Richard Hipp <d...@sqlite.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Date: 22/08/2011 17.40 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 11:25 AM, <a.azzol...@custom.it> wrote: > schema 3 > > PRAGMA integrity_check > returns > > *** in database main *** > rowid 0 missing from index JournalDateIndex > rowid 0 missing from index sqlite_autoindex_Journal_1 > wrong # of entries in index JournalDateIndex > wrong # of entries in index sqlite_autoindex_Journal_1 > Try running "REINDEX" and see if that clears up the problem. > > > > > > From: > Richard Hipp <d...@sqlite.org> > To: > General Discussion of SQLite Database <sqlite-users@sqlite.org> > Date: > 22/08/2011 17.21 > Subject: > Re: [sqlite] Autoincrement failure > > > > On Mon, Aug 22, 2011 at 11:17 AM, <a.azzol...@custom.it> wrote: > > > SELECT rowid FROM (mytable) WHERE (mystuff) > > > > returns > > > > 37 identical rows(!) where Rowid = 1 > > > > What is your schema? > > If you run "PRAGMA integrity_check"? > > > > > > > > > > Alessandro > > > > > > > > > > From: > > a.azzol...@custom.it > > To: > > <sqlite-users@sqlite.org> > > Date: > > 22/08/2011 16.26 > > Subject: > > Re: [sqlite] Autoincrement failure > > > > > > > > sqlite> .dump sqlite_sequence > > PRAGMA foreign_keys=OFF; > > BEGIN TRANSACTION; > > COMMIT; > > sqlite> > > > > > > Any idea? > > > > Thanks > > Alessandro > > > > > > > > From: > > Richard Hipp <d...@sqlite.org> > > To: > > General Discussion of SQLite Database <sqlite-users@sqlite.org> > > Date: > > 22/08/2011 16.11 > > Subject: > > Re: [sqlite] Autoincrement failure > > > > > > > > On Mon, Aug 22, 2011 at 9:56 AM, <a.azzol...@custom.it> wrote: > > > > > Hallo, > > > > > > Have you ever seen a SQLite3 DB file with autoincrement algoritm > broken? > > > Every new record seems to be added with rowid=1 overwriting existing > > > info... > > > > > > Any idea about the causes of this issue > > > and about extracting lost data (if present)? > > > > > > > Please run from the sqlite3.exe shell: > > > > .dump sqlite_sequence > > > > Tell us what you see. > > > > > > > > > > Many thanks > > > Alessandro > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > _______________________________________________ > > 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 > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ 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 _______________________________________________ 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 _______________________________________________ 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 _______________________________________________ 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
