Search code examples
mysqlmariadbinnodbmyisam

MariaDB - INNODB skipping the number sequence while creating incremental records - why?

I do not know if this is expected behavior with INNODB, but I really think it's totally weird.

If I use the same SQL statement using MYISAM, the behavior occurs as expected.

MYISAM 

CREATE TABLE main_database.numero (
    id INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY(id)
) ENGINE = MYISAM DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

INSERT INTO main_database.numero VALUES(NULL); -- First, run once time ...
INSERT INTO main_database.numero SELECT NULL FROM main_database.numero; -- After, more 12 times = 4096 records

Result (expected behavior):

enter image description here

Now if I use exactly the same statement, however, informing that the engine is INNODB.

INNODB

CREATE TABLE main_database.numero (
    id INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


INSERT INTO main_database.numero VALUES(NULL); -- First, run once time ...
INSERT INTO main_database.numero SELECT NULL FROM main_database.numero; -- After, more 12 times = 4096 records

Result (weird result - Skipping the number sequence):

enter image description here

In fact, both engines are creating the expected 4096 records, but I got worried with behavior of INNO, because I'm migrating my databases from MYISAM to INNODB and I do not know how much that can impact my applications.

Solution

  • The auto_increment mechanism is required to generate unique values, that are greater than any value it has generated previously. It does not guarantee to generate consecutive values.

    There's some discussion about it here: https://bugs.mysql.com/bug.php?id=57643

    There is little importance in generating consecutive values faithfully, because any value could be "lost" for other reasons:

    • Your INSERT fails, for example because of violating a constraint like UNIQUE KEY or FOREIGN KEY.
    • You roll back the transaction for your INSERT.
    • You succeed and commit, but later the row is DELETEd by you or another session.

    Auto-inc values are not returned to any kind of queue, because other concurrent sessions might have generated further id values in the meantime. It's not worth InnoDB maintaining a pool of unallocated id values, because that pool could become huge and wasteful.

    Also, it might be appropriate to "lose" an ID value, or else someone would think the row they meant to DELETE somehow came back.