Bug #78356 AUTO_INCREMENT generates duplicate values after server restart
Submitted: 7 Sep 2015 16:02 Modified: 8 Sep 2015 5:33
Reporter: Georgi Iovchev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment

[7 Sep 2015 16:02] Georgi Iovchev
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.
[7 Sep 2015 20:55] Peter Laursen
Probaly same (or similar to) http://bugs.mysql.com/bug.php?id=76872

-- Peter
-- not an Oracle/MySQL person.
[8 Sep 2015 5:33] Umesh Shastry
Hello Georgi Iovchev,

Thank you for the report.
This is duplicate of Bug #76872	

Thanks,
Umesh