MySQL: Why does my INSERT statement skip 56 numbers when auto-incrementing the id?

Question

While demonstrating the INSERT statement to the students of my SQL course, we've come up on some odd behavior in MySQL 8.0. Please help us learn what is happenning. (No need for workarounds as we're aware of a few and this is for learning, not for production. Thank you)

We are creating a new database and copying some rows from the well-known Sakila sample DB, like so:

CREATE DATABASE simpsons;

USE simpsons;

CREATE TABLE `character` (
    character_id smallint unsigned NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20),
    shoe_size INT,
    PRIMARY KEY (character_id));

INSERT INTO `character` 
        (first_name, last_name)
    SELECT 
        first_name, last_name 
    FROM 
        sakila.actor;

When we do this and SELECT * FROM ``character`` we see that all 200 records from sakila.actor have been copied correctly over to the new character table.

The last row gets the value 200 for its character_id auto-incremented PK. The output window shows no errors in any of the above commands.

Then, when we immediately add one more record manually:

INSERT INTO `character`
    (first_name, last_name, shoe_size)
VALUES
    ('Bart', 'Simpson', 35);

Quite oddly, we find that this record gets the value 256 as its character_id and not 201.

This is despite the fact that running SHOW VARIABLES LIKE 'auto_inc%'; shows that both auto_increment_increment and auto_increment_offset are set to 1.

We would like to learn why does MySQL skip 56 numbers?

---

Please note, this question is different from MySQL InnoDB auto_increment value increases by 2 instead of 1. Virus? and MySQL autoincrement column jumps by 10- why? because auto_incerement_increment is 1, there are no DELETE operations in our (easily reproducible) scenario and we each are the only users of our prospective DBs. Plus none of the answers to that question are conclusive as to what actually happened. Finally, please see @Postman's wonderful answer which references a root cause not mentioned in any of the answers to the above questions. Thank you

Answer 1

This behavior has something to do with "bulk inserts" and the innodb_autoinc_lock_mode setting.

As far as I understand it (the documentation isn't quite clear about this), when you use a INSERT INTO ... SELECT statement, MySQL cannot know how many rows are actually being inserted before running the query, but the IDs for the new AUTO_INCREMENT values have to be reserved when using innodb_autoinc_lock_mode=1 (consecutive) or 2 (interleaved). From my observation it reserves a set of AUTO_INCREMENT numbers where the count is a power of 2 (cannot confirm this, only a guess). See the following example:

CREATE TABLE sourceTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE targetTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    original VARCHAR(30)
);

INSERT INTO sourceTable(name) VALUES ('one');
INSERT INTO sourceTable(name) VALUES ('two');
INSERT INTO sourceTable(name) VALUES ('three');
INSERT INTO sourceTable(name) VALUES ('four');
INSERT INTO sourceTable(name) VALUES ('five');

INSERT INTO targetTable(original) SELECT name FROM sourceTable;

INSERT INTO targetTable(original) VALUES ('manual');

SELECT * FROM targetTable;

This will generate the following output:

+----+----------+
| id | original |
+----+----------+
|  1 | one      |
|  2 | two      |
|  3 | three    |
|  4 | four     |
|  5 | five     |
|  8 | manual   |
+----+----------+

When inserting the 5 rows from the source table, it reserves the next 8 possible AUTO_INCREMENT values because that is the closest power of 2 number greater than 5. However, it will use only 5 of them since you insert only 5 rows.

In your case, you are inserting 200 rows, so the closest power of 2 number greater than 200 would be 256. So you have a "gap" of 56 missing AUTO_INCREMENT values and the next entry gets the ID 256.