I am working on a project where I have have several processes writing records to an InnoDB table. There are two separate queries doing the inserts. In maybe one out of a couple 100 inserts, I get an integrity constraint violation error, complaining about a duplicate entry for key 1.
The first is a Java class, and the query is simple. The query passed to PreparedStatement is: INSERT INTO buildsteps (buildid, workflowstepid, inputlocation, creationtime) VALUES (?, ?, ?, NOW()); There are roughly 20 different threads doing these inserts. The second is a MySql trigger on a different table, that writes to buildsteps. This query is a little more complicated: AFTER INSERT ON `builds` FOR EACH ROW INSERT INTO buildsteps (buildid, workflowstepid, inputlocation, creationtime) SELECT builds.id, workflowsteps.id, builds.initiallocation, builds.creationtime FROM builds, workflowsteps, workflowsteprelationships WHERE builds.id = NEW.id AND workflowsteps.workflowid = NEW.workflowid AND workflowsteps.id = workflowsteprelationships.nextworkflowstepid AND workflowsteprelationships.previousworkflowstepid IS NULL If I generate builds records 10 at a time while the Java threads are running, as I said, about one in every 100 or so inserts seems to generate this error. None of the specified fields (buildid, workflowstepid, inputlocation, creationtime) are unique keys in buildsteps. The primary key of buildsteps is an auto increment field. The values that the integrity constraint error complains about are primary key values in buildsteps, and there is only one key on that table. I get the error both when I invoke the trigger (I get an error on the mysql command line) and when the Java query runs (I get a MySQLIntegrityConstraintViolationException thrown). The error seems to be an interaction between the two queries, that is, it only seems to occur when both processes are writing to the table, but I am not positive about that. I do not have any transaction code in place in the Java class, because I understand it is unnecessary for single queries. Do you have any ideas about what might be going on? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
