[MDEV-8827] Duplicate key with auto increment Created: 2015-09-22  Updated: 2019-10-30  Resolved: 2015-12-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 5.5.47, 10.0.23, 10.1.10

Type: Bug Priority: Critical
Reporter: Chris Calender (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 3
Labels: upstream
Environment:

CentOS 6

Issue Links:
Relates
relates to MDEV-20927 Duplicate key with auto increment Closed

 Description   

I am filing this bug with MariaDB, just for the sake of completeness, as it was already filed with MySQL/InnoDB here (by someone else):

http://bugs.mysql.com/bug.php?id=76872

"InnoDB AUTO_INCREMENT produces same value twice"

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

There is much more in the actual bug report, but I didn't want to copy/paste the entire report.

 Comments   
Comment by Elena Stepanova [ 2015-09-22 ]

Non-deterministic test case by Laurynas Biveinis from the upstream report (run with --repeat=N):

--source include/have_innodb.inc
 
 
 
# Should be != 0
 
SELECT @@innodb_autoinc_lock_mode;
 
 
 
CREATE TABLE `test` (
 
  `test_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
  `testcol` varchar(256)
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
 
 
 
SET SESSION auto_increment_increment = 2;
 
send INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
 
--connect(con1,localhost,root,,)
 
--connection con1
 
SET SESSION auto_increment_increment = 2;
 
send INSERT INTO `test` (`testcol`) VALUES ('aldsldjfhasjk');
 
# --error 0,1062
 
reap;
 
--disconnect con1
 
--connection default
 
# --error 0,1062
 
reap;
 
 
 
SELECT * FROM test;
 
 
 
DROP TABLE test;

Comment by Sergei Golubchik [ 2015-11-30 ]

http://lists.askmonty.org/pipermail/commits/2015-November/008693.html

Comment by Jan Lindström (Inactive) [ 2015-12-01 ]

I forgot that this fix should be applied to 5.5 (you need to remove galera change, galera change only to 5.5-galera).

Comment by Marko Mäkelä [ 2019-07-23 ]

I ported the MySQL 5.6.35/5.7.17 fix to MariaDB 5.5.65 and reverted our fix because our fix conflicted with a revert that I applied from upstream.

Comment by Marko Mäkelä [ 2019-07-23 ]

It is possible that due to the fix that I had to revert due to reverting the MySQL 5.6 change we are reintroducing MySQL Bug #88321 into MariaDB. It is also possible that that bug was fixed by reverting the MySQL 5.6 change. Oracle does not always update the public bug reports.

elenst, maybe you could try the test case?

Generated at Thu Feb 08 07:30:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.