Bug #95650 Allocates more auto-increment values than needed

• Submitted: 5 Jun 2019 9:45
• Modified: 5 Jun 2019 12:33
• Reporter: Guangbao Ni
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.6, 5.7, 8.0
• OS: Any
• CPU Architecture: Any
• Tags: auto_increment

[5 Jun 2019 9:45] Guangbao Ni

Description:
innodb_autoinc_lock_mode set 1,  a “mixed-mode inserts” is allocated more auto-increment values than needed, which will cause a gap with the next INSERT. 

Seem the result is expected based on the document:  https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html, yet I still think we can get improvement.

How to repeat:
create table t1(c1 int auto_increment primary key, c2 char(10)) engine=innodb;
insert into t1 values(-200),(1),(null),(null); <----2,3 are inserted

insert into t1 values(null);  // 6 is inserted, yet 4 is expected

Suggested fix:
N/A

[5 Jun 2019 12:33] MySQL Verification Team

Hi Mr. Ni,

Thank you for your bug report.

However, this is not a bug. That is how InnoDB SE works with multi-row inserts.

This is fully described in our Reference Manual, section on InnoDB Storage Engine.