MySQL 5.7: Issues with AUTO_INCREMENT Skipping Values During Bulk Inserts
I'm integrating two systems and I'm dealing with I've looked through the documentation and I'm still confused about I'm encountering a frustrating issue with MySQL 5.7 where the AUTO_INCREMENT values for my primary key are skipping unexpectedly when performing bulk inserts..... I am using a stored procedure to insert multiple rows into a table, and I noticed that the last inserted ID is often significantly less than expected. For example, when I insert 10 rows, the ID jumps from 100 to 112. I suspect this might be related to transactions or the way MySQL handles row locks during inserts. Here’s how my stored procedure looks: ```sql CREATE PROCEDURE BulkInsert(IN numberOfRows INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < numberOfRows DO INSERT INTO my_table (data_column) VALUES (CONCAT('Row ', i)); SET i = i + 1; END WHILE; END; ``` To execute it, I run: ```sql CALL BulkInsert(10); ``` I also have a trigger on this table that logs the inserted rows into an audit table. However, I’ve checked the audit table, and it seems that it only logs the inserts after the transaction is committed. I even tried disabling the trigger to see if it had any effect, but the skipping still occurred. We've set `innodb_autoinc_lock_mode` to 1, which, according to the documentation, should minimize gaps. However, even with this setting, we continue to see skips when multiple sessions execute this procedure simultaneously. Could this be an issue with concurrent inserts, or is there something I may be missing in the configuration? Is there a better approach to handle bulk inserts that can ensure consecutive AUTO_INCREMENT values? Any pointers would be greatly appreciated! My development environment is macOS. The stack includes Sql and several other technologies. I'd love to hear your thoughts on this. This issue appeared after updating to Sql stable. What's the correct way to implement this? I'm working with Sql in a Docker container on Debian.