Buy Percona ServicesBuy Now!

Avoiding auto-increment holes on InnoDB with INSERT IGNORE

 | November 29, 2011 |  Posted In: MySQL


Are you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This documentation bug is already submitted.

Firstly, we will start with a simple question. Why do we have gaps on auto-increment columns? Secondly, I will show you a trick to mimic the INSERT IGNORE behaviour without losing auto increment values. Let’s start!

Why do we have gaps?

InnoDB checks an auto_increment counter on the table and if a new value is needed, increments that counter and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that counter values called “Traditional“. This one uses a special table lock called AUTO-INC that remains until the end of the query or transaction. Because of this, two queries can’t have the AUTO-INC lock at the same time, so we lose concurrency and performance. The problems are even worse with long running queries like INSERT INTO table1 … SELECT … FROM table2.

In version 5.1.22 and later the lock algorithm for the auto_increment value is configurable and you can select from different algorithms using the innodb_autoinc_lock_mode. By default the value is 1, which is a new algorithm called “consecutive“. Thanks to this new value, a simple insert query like a single-row or multi-row INSERT/REPLACE uses a light-weight mutex instead of a table lock on AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on the auto_increment column.

To avoid this little inconvenience it is possible to return to the traditional method changing the innodb_autoinc_lock_mode to 0. But with a loss of performance and concurrency.

How can I solve this problem for INSERT IGNORE?

As I informed you before, it is not documented that INSERT IGNORE creates gaps, so maybe you have been unaware of this problem for years. You can mimic the INSERT IGNORE behaviour using a special mutex table, as explained on Baron’s blog to get rid of the gaps problem.

A “mutex” table is a clever trick that allows joining tables while keeping them independent of each other in a query. This property allows interesting queries that are not otherwise possible.

This is our mutex table. We only need to insert one integer value:

create table mutex(
i int not null primary key
insert into mutex(i) values (1);

Our InnoDB table with auto increment column will be like this:

name int(11) DEFAULT NULL,
UNIQUE KEY uniqname (name)

Insert a value using a LEFT OUTER JOIN:

insert into foo(name) select 1 from mutex left outer join foo on where mutex.i = 1 and is null;
Query OK, 1 row affected (0.00 sec)

Insert the same value multiple times. As you will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE:

insert into foo(name) select 1 from mutex left outer join foo on where mutex.i = 1 and is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on where mutex.i = 1 and is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on where mutex.i = 1 and is null;
Query OK, 0 rows affected (0.00 sec)

Now check the auto_increment counter:

show create table foo\G
*************************** 1. row ***************************
Table: foo
Create Table: CREATE TABLE foo (
name int(11) DEFAULT NULL,
UNIQUE KEY uniqname (name)

No gaps at all.

This trick was discovered by Michael Rikmas when we were working on a Consulting case for a customer. So, if this saves you from doing an ALTER TABLE to change the auto incremental column size, then send him a beer 😉

Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow.


  • Miguel,

    I wonder if you get gaps in auto increment for any insert ignore or only in case of single/bulk inserts with known number of values. According to the documentation when INSERT … SELECT FROM tbl is ran the AUTO-INC lock is held and as such it could be possible not to produce gaps if desired (code wise)

  • Peter:

    I could reproduce gaps problem with:

    insert ignore into foo (name) VALUES(1);
    insert ignore into foo (name) VALUES(1),(2),(3),(4),(5);

    Not with this one:

    insert ignore into foo select * from foo2;

    So you are right, only single/bulk inserts produce gaps 🙂

  • Hi,

    I have an issue with the gaps…
    I’m creating fake data in tables for testing, my goal is to produce some 25M lines in a table.
    I have a few sample lines I reproduce changing the keys to generate my 25M lines.

    I use a procedure to fill a table :

    create procedure table_lines (nb int)
    declare runnb int;

    drop table if exists tempTbl;

    create table tempTbl (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

    truncate table tempTbl;

    insert into tempTbl
    values (null);

    insert into tempTbl
    select null from tempTbl;
    select count(*) into runnb from tempTbl;
    until nb <= runnb
    end repeat;

    Then I use the table in a join on my sample lines in another table.
    At first look it works fine, except that as I have gaps in my tempTbl I do not get the required amount of lines.

    Any clues on this?

  • I’m not sure if I understand how to apply your SQL statement above to a query that might add multiple rows. In other words, how do you avoid the auto increment gaps using a INSERT IGNORE…SELECT such as:

    INSERT IGNORE INTO foo (name)
    FROM foo2 f2


  • Dear Miguel

    mysql from 5.1.22 change autoincrement mod

    I have the same problem.

    You have to add one row to your my.ini file.

    # =0 is traditional lock mode == > funcionality of auto_increment like before 5.1.22
    # =1 is default value – consecutive lock mode
    # =2 is interleaved lock mode , i don’t investigate what it do

    2. restart mysql server

    now is working perfectly
    id – is primary key
    name – is unique varchar

    insert into tab (name) values (‘Peter’),(‘Oto’),(‘Jan’),(‘Jan’),(‘Jan’),(‘Romco’);
    select * from tab order by 1;

    1 Peter
    2 Oto
    3 Jan
    4 Romco ===> not 7 Romco as before

  • What if you wanted the Insert to happen but with the next auto-inc number. I have a situation where I have a multi threaded process that each thread reads in a file and tries to insert records into the same table. Right now I am getting duplicate key records. Will the only way to fix this is to change the innodb_autoinc_lock_mode=0? Or is there another way. I dont want to do Insert ignore because I want the row inserted, just with a different auto-inc value that isn’t a duplicate.

  • As far as I understand.
    we have to join the insert values with the existing data but cannot, so we create a mutex table

    INSERT INTO {dest_table} (name)
    SELECT {new_value} FROM mutex
    LEFT OUTER JOIN {dest_table} ON {dest_table}.name={new_value}
    WHERE mutex.i = 1 AND {dest_table}.name IS NULL;

  • Variants:

    INSERT INTO foo (id,name) SELECT LAST_INSERT_ID(, “abc” FROM mutex AS m LEFT JOIN foo AS f ON = “abc” WHERE m.i = 1 ON DUPLICATE KEY UPDATE name=VALUES(name);

    INSERT INTO foo (id,name) SELECT, “abc” FROM mutex AS m LEFT JOIN foo AS f ON = “abc” WHERE m.i = 1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(;

    (but I didn’t check this with 5.6.15 replication yet)

    CREATE TABLE foo (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY name (name)

  • Does this work for inserting strings? For example if was a varchar column, would the below statement work?

    insert into foo(name) select ” from mutex left outer join foo on” where mutex.i = 1 and is null;

  • Be Careful With MySQL’s auto_increment. How We Ended Up Losing Data.

  • Instead of using auto-incrementing integer columns, I use a UUID column with a before-insert trigger:

    CREATE TABLE mydb.mytable (
    id char(36) NOT NULL DEFAULT ”,
    name varchar(45) NOT NULL,
    descr varchar(255) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY name_UX (name),

    CREATE DEFINER=myuser@localhost TRIGGER mydb.mytable_BINS
    SET = UUID();


    This is helpful in a few ways:

    1. no integer exhaustion

    2. no autoincrement index holes

    3. no duplicate ID values

    4. 32-bit compatible … ya’ know, since it’s just text (eesentially), and not a 64-bit integer like BIGINT

    5. works well with PHP frameworks like CakePHP

    6. you gain two levels in the prestige class “Web Developer”

    7. Wait, what are we talking about?

    I hope that somebody else finds this to be helpful.


  • As a note, INSERT… ON DUPLICATE KEY UPDATE also causes gaps. This is very different from the MyISAM implementation.

Leave a Reply