Bug #95650 Allocates more auto-increment values than needed
Submitted: 5 Jun 2019 9:45 Modified: 5 Jun 2019 12:33
Reporter: Guangbao Ni Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6, 5.7, 8.0 OS:Any
Assigned to: 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.