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)" <[email protected]> To: General Discussion of SQLite Database <[email protected]> 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: [email protected] [[email protected]] on behalf of [email protected] [[email protected]] 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 <[email protected]> To: General Discussion of SQLite Database <[email protected]> Date: 22/08/2011 17.40 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 11:25 AM, <[email protected]> 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 <[email protected]> > To: > General Discussion of SQLite Database <[email protected]> > Date: > 22/08/2011 17.21 > Subject: > Re: [sqlite] Autoincrement failure > > > > On Mon, Aug 22, 2011 at 11:17 AM, <[email protected]> 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: > > [email protected] > > To: > > <[email protected]> > > 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 <[email protected]> > > To: > > General Discussion of SQLite Database <[email protected]> > > Date: > > 22/08/2011 16.11 > > Subject: > > Re: [sqlite] Autoincrement failure > > > > > > > > On Mon, Aug 22, 2011 at 9:56 AM, <[email protected]> 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 > > > [email protected] > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > D. Richard Hipp > > [email protected] > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > [email protected] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
