Bug #119431 Concurrent inserts generate duplicate AUTO_INCREMENT values after changing auto_increment settings

• Submitted: 20 Nov 7:59
• Reporter: kai zhang
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.30, 8.0.39
• OS: Any
• CPU Architecture: Any
• Tags: auto_increment;auto_increment_offset;duplicate;

[20 Nov 7:59] kai zhang

Description:
After setting auto_increment on a primary key column and modifying the auto_increment_increment and auto_increment_offset variables, concurrent inserts within explicit transactions encounter duplicate primary key errors. The duplicate primary key value equals the auto_increment_offset value, and the row with this duplicate key already exists in the table.
It happens when innodb_autoinc_lock_mode equals 1 or 2.

How to repeat:
Repeat it using MTR.

```

--connection default
DROP DATABASE IF EXISTS gdn_test_db1;
CREATE DATABASE gdn_test_db1;
USE gdn_test_db1;

select @@version;

show variables like '%innodb_autoinc_lock_mode%';

CREATE TABLE IF NOT EXISTS test(
  id INT(11) NOT NULL AUTO_INCREMENT,
  timestamp_field TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# NOTE: if we don't set auto_increment_increment and auto_increment_offset, inserts below will not be duplicated error.
SET GLOBAL auto_increment_increment=20;
SET GLOBAL auto_increment_offset=100;
SHOW GLOBAL VARIABLES LIKE '%auto_incre%';

--connect(con1,localhost,root,,gdn_test_db1)
--connect(con2,localhost,root,,gdn_test_db1)
--connect(con3,localhost,root,,gdn_test_db1)
--connect(con4,localhost,root,,gdn_test_db1)
--connect(con5,localhost,root,,gdn_test_db1)
--connect(con6,localhost,root,,gdn_test_db1)
--connect(con7,localhost,root,,gdn_test_db1)
--connect(con8,localhost,root,,gdn_test_db1)
--connect(con9,localhost,root,,gdn_test_db1)

--connection con1
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con2
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con3
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con4
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con5
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con6
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con7
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con8
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con9
--send START TRANSACTION; INSERT INTO test(timestamp_field) VALUES (CURRENT_TIMESTAMP); SELECT @@global.server_uuid; COMMIT

--connection con1
--reap

--connection con2
--reap

--connection con3
--reap

--connection con4
--reap

--connection con5
--reap

--connection con6
--reap

--connection con7
--reap

--connection con8
--reap

--connection con9
--reap

--connection default
SELECT id FROM test ;

--disconnect con1
--disconnect con2
--disconnect con3
--disconnect con4
--disconnect con5
--disconnect con6
--disconnect con7
--disconnect con8
--disconnect con9

--connection default
SET GLOBAL auto_increment_increment=1;
SET GLOBAL auto_increment_offset=1;
DROP DATABASE IF EXISTS gdn_test_db1;

```

My result: 
```

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] main.bug                                  [ fail ]
        Test ended at 2025-11-20 15:41:57

CURRENT_TEST: main.bug
mysqltest: At line 69: Query 'reap' failed.
ERROR 1062 (23000): Duplicate entry '100' for key 'test.PRIMARY'

```