Bug #106009 After INSERT ... SELECT, value of AUTO_INCREMENT becomes multiple of 8
Submitted: 30 Dec 2021 11:42 Modified: 31 Dec 2021 13:09
Reporter: Domen Kermc Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[30 Dec 2021 11:42] Domen Kermc
Description:
When inserting rows using INSERT ... SELECT statement, target table's AUTO_INCREMENT after the query is executed becomes some multiple of 8 (or multiple of 8 off-by-one, in subsequent insert).

Examples:
* After INSERT inserts 2 rows into an empty table, AUTO_INCREMENT will be 4.
* After INSERT inserts 10 rows into an empty table, AUTO_INCREMENT will be 16.
* After INSERT inserts 100 rows into an empty table, AUTO_INCREMENT will be 128.

Tested on InnoDB tables. INSERT ... VALUES works as expected.

How to repeat:
Create tables:
CREATE TABLE `test_source` (`id` INT PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE `test_target` (`id` INT PRIMARY KEY AUTO_INCREMENT);

Scenario: source has 2 values:
TRUNCATE TABLE `test_source`;
TRUNCATE TABLE `test_target`;
INSERT INTO `test_source` (`id`) VALUES (NULL), (NULL); -- 2 values
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 1, 2; AUTO_INCREMENT = 4
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 4, 5; AUTO_INCREMENT = 7

Scenario: source has 10 values:
TRUNCATE TABLE `test_source`;
TRUNCATE TABLE `test_target`;
INSERT INTO `test_source` (`id`) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); -- 10 values
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 1..10; AUTO_INCREMENT = 16
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 16..25; AUTO_INCREMENT = 31
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 31..40; AUTO_INCREMENT = 46

Scenario: source has 100 values:
TRUNCATE TABLE `test_source`;
TRUNCATE TABLE `test_target`;
INSERT INTO `test_source` (`id`) VALUES (NULL), [...], (NULL); -- 100 values
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 1..100; AUTO_INCREMENT = 128
INSERT INTO `test_target` (`id`) (SELECT NULL FROM `test_source`); -- IDs: 128..227; AUTO_INCREMENT = 255
[31 Dec 2021 5:08] MySQL Verification Team
Hi,
This is not a bug.

Check documentation:
https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increm...

https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increm...

happy holidays
[31 Dec 2021 13:09] Domen Kermc
Described behaviour was tested on two servers: replication source and server that is not taking part in a replication.

Variables auto_increment_increment and auto_increment_offset both had value 1 on both servers.

Also, as mentioned in bug description, behaviour is correct when inserting separate rows using INSERT ... VALUES (as examples in documentation https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increm... do). Only INSERT ... SELECT is problematic.

I've delved further. I've tested versions 8.0.27, 8.0.24, 8.0.12, 5.7.35, 5.7.20, 5.7.10 and 5.6.20. They all exhibit this behaviour, but only on InnoDB engine. On MyISAM, INSERT ... SELECT works the same as INSERT ... VALUES.

So this may be a bug. If it is, it's been around for a long time. If it's not, I would be grateful if you could point me to the documentation describing (and possibly changing) this behaviour.
[31 Dec 2021 14:57] MySQL Verification Team
Please check: https://bugs.mysql.com/bug.php?id=105871