Mysql/Percona 5.1.54 Duplicate Entry on autoinc PK field

MySQL & MariaDB Other MySQL® Questions
1

We’ve been getting the following error recently fairly often (once or twice a day).

Error:
Duplicate entry ‘5367702’ for key ‘PRIMARY’

Query:
INSERT INTO myTableName (field1, field2, field3) VALUES (5972, 321, 3127532);

Mysql Version:
5.1.54-rel12.5-log Percona Server on CentOS5.5

Table:
CREATE TABLE myTableName (
id int(11) NOT NULL AUTO_INCREMENT,
field1 int(11) DEFAULT NULL,
field2 int(11) DEFAULT NULL,
field3 int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

I was hoping someone could shed some light into this for us. The PK field is not defined in the insert query, so it should use the next available auto increment value.

Thanks!
Stephen

2

Looks strange,

what innodb_auto_increment_mode does it happens in ?

Are you able to create some test case which will trigger the problem
(even if it takes it hours to run to trigger one)

3

We have not attempted to create any test cases… but that is a good idea. I will try to setup a test instance and duplicate it.

I can give a little more background information. Our innodb_auto_increment_mode is the default, 1/traditional.

This seems limited to occurring on tables that we do batch inserts onto.

The table this is most often occurring on we have ~25 parallel processes running each doing batch inserts of 1000 rows per statement, which generally insert between 10,000 to 100,000 rows in total. The table is used more or less as a queue, so there will be brief periods of inserts like these, then have little activity.

We have a few other tables that we regularly do batch inserts on and we see the same problem there as well.

Thanks!
Stephen