MySQL Bugs: #88321: Regression: InnoDB AUTO_INCREMENT produces same value twice

Bug #88321	Regression: InnoDB AUTO_INCREMENT produces same value twice
Submitted:	1 Nov 2017 19:19	Modified:	29 Jul 2020 15:58
Reporter:	Richard Balue	Email Updates:
Status:	Closed	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

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'"

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)

Please try version 5.6.38.

https://dev.mysql.com/downloads/mysql/5.6.html#downloads

Thanks.  I will setup 5.6.38 and get back to you.

Reopen when you are done with new test. Thanks.

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?

## 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

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

test results

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

Added affected version to 5.6.38

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)

We are a couple months shy of 1 year since I first reported this bug.  Can we get an update?

Updating bug to include MySQL 8.0.12

Bug #92253 marked as duplicate of this one

Hi Umesh,

We are going on 3+ years, can you provide a status on this?

Thanks,
Richard

This seems to have been fixed in version 5.7.27 (release notes):

InnoDB: Client sessions using different auto_increment_increment values while performing concurrent insert operations could cause a duplicate key error. (Bug #15851528, Bug #67526)References: Reverted patches: Bug #14049391, Bug #65225.

Verified it's been fixed