Description:
Whit @@auto_increment_increment not default value when multiple connections are trying to insert row into innodb table with auto increment column, it is possible to receive error - ERROR : Duplicate entry '..' for key 'PRIMARY'
This happens only once (per table) after the server has been restarted. Next inserts are fine. Also it happens only to Innodb tables, myisam is fine.
I have tested this behavior with versions 5.6.26. with my.cnf containing only datadir=... and auto_increment_increment=10
Also I have tried older versions to see in which version this behavior is introduced and I found that in 5.5.22 is ok, but in 5.5.23 (2012-04-12 !!!) up to now the bug exists.
How to repeat:
In my.cnf add at the end of [mysqld] section add
auto_increment_increment=10
## Create and change database (use the exact name - it will be used by mysqlslap utility)
create database mysqlslap ; use mysqlslap ;
## Create simple table
mysql> CREATE TABLE table1 ( col1 INT(11) AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB ; Query OK, 0 rows affected (0.01 sec)
## Insert at least one row
mysql> insert into table1 values(); Query OK, 1 row affected (0.01 sec)
## Note the value of the AUTO_INCREMENT
mysql> select AUTO_INCREMENT from information_schema.TABLES where table_schema='mysqlslap' and TABLE_NAME='table1'; +----------------+
| AUTO_INCREMENT |
+----------------+
| 11 |
+----------------+
1 row in set (0.00 sec)
## Restart mysqld service
## Again check the value of the AUTO_INCREMENT
mysql> select AUTO_INCREMENT from information_schema.TABLES where table_schema='mysqlslap' and TABLE_NAME='table1';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 2 |
+----------------+
1 row in set (0.01 sec)
### It is different!!!
## Run simple mysqlslap insert test with at least 2 concurrent connections.
mysqlslap --concurrency=2 --iterations=1 --query="insert into table1 values()"
mysqlslap: Cannot run query insert into table1 values() ERROR : Duplicate entry '11' for key 'PRIMARY'
## Run it again and it will complete successfully
It happens only after restart.
