Bug #120598 Autoincrement problem

• Submitted: 2 Jun 7:06
• Modified: 2 Jun 7:07
• Reporter: Alex Zimnitski
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.9
• OS: Linux
• CPU Architecture: x86

[2 Jun 7:06] Alex Zimnitski

Description:
SELECT @@global.auto_increment_increment, @@global.auto_increment_offset, SELECT @@global.auto_increment_increment, @@global.auto_increment_offset, @@global.innodb_autoinc_lock_mode;
2	3	2

Autoincrement gaps when table is new

How to repeat:
DROP TEMPORARY TABLE IF EXISTS test_autoincrement;
CREATE TEMPORARY TABLE IF NOT EXISTS test_autoincrement (
	id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	val varchar(10)
) ENGINE = INNODB;

INSERT INTO  test_autoincrement (val)
values('A'),('B');
SELECT * FROM test_autoincrement;
1	A
3	B

INSERT INTO  test_autoincrement (val)
values('A'),('B');
SELECT * FROM test_autoincrement;
1	A
3	B
9	A
11	B

INSERT INTO  test_autoincrement (val)
values('A'),('B');
SELECT * FROM test_autoincrement;
1	A
3	B
9	A
11	B
13	A
15	B

Suggested fix:
unknown

[2 Jun 7:07] Alex Zimnitski

Autoincrement problem