combination of log-bin true and innodb_autoinc_lock_mode set to 2 (is default) will skip ID's

MySQL & MariaDB Percona Operator for MySQL
1

I am trying to find how I can avoid skipping ID on entry’s.

A few days ago I didn’t use the log-bin option: on mysql 5.7 it standard set to off. After I changed this setting in my.cnf the binlog worked, but the result was that very new project I created had a ID which was 3 ID after the former one.

Setting the innodb_autoinc_lock_mode = 1 or 0 is not possible.

We are using the ID as part of an title in our project tool. SO, it’s a bit inconvenient.

Any help ?

2

Hi bert, unfortunately there is no way to guarantee that auto increment values are not skipped. Quoting the man page: https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes

“Lost” auto-increment values and sequence gaps

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

If you absolutely need no gaps you need to handle the generation yourself by using a table column for example. However it would be a bad idea from a concurrency perspective, as many transactions would compete for locks against the sequence table.

Hope that helps