Bug #88321 Regression: InnoDB AUTO_INCREMENT produces same value twice
Submitted: 1 Nov 2017 19:19 Modified: 28 Aug 2018 3:57
Reporter: Richard Balue Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.35, 5.6.38, 5.7.20, 8.0.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment_increment, auto_increment_offset

[1 Nov 2017 19:19] Richard Balue
Description:
This is a regression of bug #76872 - please see that thread for full details.

When more than one INSERT is executed concurrently on a non-empty InnoDB table with an AUTO_INCREMENT PK immediately after MySQL start up (before any other INSERTs on the same table) one will often fail with the message like "Duplicate entry '123' for key 'PRIMARY'".
This will only happen if innodb_autoinc_lock_mode is 1 or 2 and auto_increment_increment > 1.

How to repeat:
1. Create a table as follows:
CREATE TABLE `test` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `testcol` varchar(256) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. Insert one row as follows:
INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');

3. Restart MySQL server (which has innodb_autoinc_lock_mode=1 and auto_increment_increment=2 configured).

4. Create two clients that connect to the MySQL server in parallel and await each other after this is completed.

5. Simultaneously execute one INSERT from each one of the clients.
INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');

Result: One succeeds and one fails with the error message "Duplicate entry '123' for key 'PRIMARY'"
[1 Nov 2017 22:58] Miguel Solorzano
I couldn't repeat with current source server, so I assume is the version number in the changelog wrong:

miguel@tikal:~/dbs $ ./56c
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39 Source distributiona 2017-OCT-28

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use u
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.6 >
mysql 5.6 > INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
Query OK, 1 row affected (0.04 sec)

mysql 5.6 > INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
Query OK, 1 row affected (0.08 sec)

mysql 5.6 > INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
Query OK, 1 row affected (0.05 sec)

mysql 5.6 >
mysql 5.6 > select * from test;
+---------+---------------+
| test_id | testcol       |
+---------+---------------+
|       1 | aldsldjfhasjk |
|       3 | aldsldjfhasjk |
|       5 | aldsldjfhasjk |
|       7 | aldsldjfhasjk |
|       9 | aldsldjfhasjk |
|      11 | aldsldjfhasjk |
|      13 | aldsldjfhasjk |
+---------+---------------+
7 rows in set (0.00 sec)
[1 Nov 2017 23:01] Miguel Solorzano
Please try version 5.6.38.

https://dev.mysql.com/downloads/mysql/5.6.html#downloads
[3 Nov 2017 16:14] Richard Balue
Thanks.  I will setup 5.6.38 and get back to you.
[6 Nov 2017 12:03] Miguel Solorzano
Reopen when you are done with new test. Thanks.
[6 Nov 2017 23:14] Richard Balue
I tried my tests on 5.6.38 from the latest port build and saw the same issue.  Let me know what you need to replicate?
[7 Nov 2017 13:01] Umesh Shastry
## ran some 10 times provided test case against 5.6.38(with default conf) on OL7

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: ./run_test.sh  5000
100..200..300..400..500..
test1 ran 507 times with 0 Duplicate Errors
100..200..300..400..500..600..
test3 ran 647 times with 0 Duplicate Errors
100..200..300..400..500..600..700..
test4 ran 751 times with 0 Duplicate Errors
100..200..300..400..500..
test5 ran 505 times with 0 Duplicate Errors
100..200..300..400..500..
test6 ran 575 times with 0 Duplicate Errors
100..200..300..400..500..600..
test7 ran 689 times with 0 Duplicate Errors
[7 Nov 2017 13:30] Umesh Shastry
Thank you, please ignore my previous note. 
With the provided test case I'm seeing reported issue in 5.6.38 and 5.7.20.

Thanks,
Umesh
[7 Nov 2017 13:32] Umesh Shastry
test results

Attachment: 88321.results (application/octet-stream, text), 5.70 KiB.

[7 Nov 2017 22:32] Richard Balue
Added affected version to 5.6.38
[6 Aug 2018 15:07] Jacky Jacky
Does we have found the root cause? And in my production environment, I have encounter the same issue without restarting MySQL server (version 5.6.35)
[6 Aug 2018 18:43] Richard Balue
We are a couple months shy of 1 year since I first reported this bug.  Can we get an update?
[28 Aug 2018 3:57] Richard Balue
Updating bug to include MySQL 8.0.12
[31 Aug 2018 10:06] Umesh Shastry
Bug #92253 marked as duplicate of this one