Description:
We have created a multithread application that uses the MySQL Embedded Library (we develop under Delphi 6 and 7). The main thread executes a series of INSERT queries, while a background thread executes some SELECT, UPDATE and DELETE queries on the same table. For each query we start a new connection (mysql_connect) and we close it after the execution of the query itself.
The structure of the table is this:
CREATE TABLE IndexJob
(indexJobID INTEGER NOT NULL AUTO_INCREMENT,
URI TEXT,
isSource TINYINT,
toIndex TINYINT,
inProgress TINYINT,
PRIMARY KEY (indexJobID),
INDEX URIIndex (URI(255)),
INDEX inProgressIndex (inProgress))
Always after some queries we get a MySQL error "duplicate entry 'X' for key 3" when an INSERT query is executed.
We have understood that the problem is related to inProgressIndex.
The INSERT queries are performed by the main thread, while another thread performs UPDATE queries changing the value of the inProgress field on the same table (but on different entries). If we exclude the secondary thread, the error doesn't occur, so we think that there is some synchronization problem.
If we don't define any index in the table, this error doesn't occur.
In each case, indipendently by the table definition, another error happens: after having executed our threads for a while, some exceptions are thrown of kind "got error X from table handler" where the value of X is sometimes 22, otherwise 127 or 134.
We have replicated the problem with a case test that we can send to you.
Also this program is made with Delphi and it's composed by three threads: one inserting records into the table, one performing UPDATE queries, and one other that deletes records.
After running for a while the test program, we have the same errors and the final result is that the table is corrupted.
We've performed our tests on 4 computers with Intel processors (3 IBM laptops and 1 Compaq desktop). We've seen that the probability of having the error grows with the speed of the computer (on an old IBM Thinkpad 600X the error happens very rarely).
How to repeat:
How described above, we can send to you a test program that, under some conditions, replicates the problem.
Suggested fix:
We think the problem is related to synchronization primitives and thread-safety of MySql.
