# combination of log-bin true and innodb\_autoinc\_lock\_mode  set to 2 (is default) will skip ID's

**URL:** <https://forums.percona.com/t/combination-of-log-bin-true-and-innodb-autoinc-lock-mode-set-to-2-is-default-will-skip-ids/8214>\
**Category:** Percona Operator for MySQL\
**Created:** [October 30, 2020, 8:11am UTC](https://forums.percona.com/t/combination-of-log-bin-true-and-innodb-autoinc-lock-mode-set-to-2-is-default-will-skip-ids/8214 "2020-10-30T08:11:59Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![bert](https://avatars.discourse-cdn.com/v4/letter/b/9e8a1a/32.png) [@bert](https://forums.percona.com/u/bert)\
**Post date:** [October 30, 2020, 8:11am UTC](https://forums.percona.com/t/combination-of-log-bin-true-and-innodb-autoinc-lock-mode-set-to-2-is-default-will-skip-ids/8214/1 "2020-10-30T08:11:59Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [November 3, 2020, 7:18am UTC](https://forums.percona.com/t/combination-of-log-bin-true-and-innodb-autoinc-lock-mode-set-to-2-is-default-will-skip-ids/8214/2 "2020-11-03T07:18:48Z")

</div>

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](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](https://dev.mysql.com/doc/refman/5.7/en/insert.html)-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