Hi,
I found sqlite (3.8.2 as well as 3.7.17) cannot stand reliably with some
simple insert/update operations on a single table with primary
key. I found this problem on ARM platform with varities of filesystems
including UBIFS, Yafss on NAND, and Ext4 on SDCARD.
Attached is the test program writting in C.
What I did is to create a simple table with threee integer columns, the
first two columns are defined as primary key. The I continously update
the table with three different strategies:
strategy 0: always insert and detect SQL_CONSTRAINT error, if
detected, do a update instead;
strategy 1: do a "select count (*) where ..." firstly, if the records
with the same key does not exists (count = 0) then do insertion,
otherwise do update.
strategy 2: using sqlite specific SQL "insert or replace".
In all above strategies, I orgnized sql operations in an exclusive
transation. I committed a transation after 500 insert/update operations
had been executed before start another transaction. If any abnormal error
detected in insert/update operations, I rollback the running
transation, print an error and continue.
If I run my test enough long, e.g., 10000+ transactions, it always almost
show different problems, but all of these problems seem is
showing that the index structure of the database got corrupted:
a) The test program run successfully, but after it ended and I do a
integrity_check from sqlite3 shell, there are a lot of "row #nnn missed
from ***_autoindex_***" errors.
b) Program received sqlite error code 11 from invocation of sqlite3_step().
Most these kind of error happened after executed an "update" statement, but
it also observed that the same error can be received even after executed
a "select" statement.
c) In strategy 0, after executed a "select" statement, it returns a count
value of zero, means the records does not exist. But immediatly after the
next "insert" was issued, it reported an sqlite err 19 (CONSTRAINT).
I have not yet speed too much time in testing strategy 2 (insert or
replace),
it has not yet shown an error.
Anyway, all above errors looks so strange. And, these operations I talking
about are so basic and my real application (another bigger one) really
depends on these.
Pleaes be kindly to check my test program. I hope these issues were caused
by bugs in my ode, then I can fixed. But if my code is okay, it's really
a big probleme. If, otherwise, someone can confirm that this is a sqlite
bug,
can I have any work-around?
Thanks in advance.
-woody
--
Life is the only flaw in an otherwise perfect nonexistence
-- Schopenhauer
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
