Description:
on rare occasions, (25 out of 85,000 transactions for example) creating
a new row in a table with a unique integer key reports failure due to duplicate
key, when in fact that should be impossible. The cliche used to create
the row uses two tables, one with an auto increment column, whose value
is used as the unique key in the second table.
INSERT INTO table1 creator='goo'; // auto-increment
INSERT INTO table2 uid=LAST_INSERT_ID();
where the second query is reported as failed. The application is single
threaded, C, uses libmysql in a pretty straightforward way. The second
insert does appear to have succeeded, which could mean that somehow the
INSERT was indeed attempted twice, or alternatively the error return
is itself the error.
Here is an actual entry from my error log:
[local time Fri Apr 16 11:47:51 2004, gmt: Fri Apr 16 15:47:51 2004] (p=2460)query failed, error = -1 (Duplicate entry '1000084708' for key 1) query
= INSERT INTO picture uid,number,origin,path,width,height,batch,photo,status,thumb_generated) VALUES ('1000084708','43383','Upload','d:/apache/htdocs/temp/cgi-lib-1082130067-198.163.213.19-44476-2028-1.JPG','640','480','Grade 7 Girls Volleyball','gr7gvb2',:
This is happening with similar frequency on 4 independant systems all with
identical hardware and software.
How to repeat:
Difficult, as this is rare. Binary logs are available, but nothing alarming
is seen in the vivinity of the failed transaction. There is no evidence that
there were actually two queries (so the failure would be legitimate) or that
there is any other query close in time is implicated in the problem.
Description: on rare occasions, (25 out of 85,000 transactions for example) creating a new row in a table with a unique integer key reports failure due to duplicate key, when in fact that should be impossible. The cliche used to create the row uses two tables, one with an auto increment column, whose value is used as the unique key in the second table. INSERT INTO table1 creator='goo'; // auto-increment INSERT INTO table2 uid=LAST_INSERT_ID(); where the second query is reported as failed. The application is single threaded, C, uses libmysql in a pretty straightforward way. The second insert does appear to have succeeded, which could mean that somehow the INSERT was indeed attempted twice, or alternatively the error return is itself the error. Here is an actual entry from my error log: [local time Fri Apr 16 11:47:51 2004, gmt: Fri Apr 16 15:47:51 2004] (p=2460)query failed, error = -1 (Duplicate entry '1000084708' for key 1) query = INSERT INTO picture uid,number,origin,path,width,height,batch,photo,status,thumb_generated) VALUES ('1000084708','43383','Upload','d:/apache/htdocs/temp/cgi-lib-1082130067-198.163.213.19-44476-2028-1.JPG','640','480','Grade 7 Girls Volleyball','gr7gvb2',: This is happening with similar frequency on 4 independant systems all with identical hardware and software. How to repeat: Difficult, as this is rare. Binary logs are available, but nothing alarming is seen in the vivinity of the failed transaction. There is no evidence that there were actually two queries (so the failure would be legitimate) or that there is any other query close in time is implicated in the problem.