September 16, 2014

Avoiding auto-increment holes on InnoDB with INSERT IGNORE

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:

CREATE TABLE foo (
id int(11) NOT NULL AUTO_INCREMENT,
name int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uniqname (name)
) ENGINE=InnoDB;

Insert a value using a LEFT OUTER JOIN:

insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name 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 foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name is null;
Query OK, 0 rows affected (0.00 sec)
insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1 and foo.name 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 (
id int(11) NOT NULL AUTO_INCREMENT,
name int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uniqname (name)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

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 ;)

About 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's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Comments

  1. The LEFT JOIN is a cool trick!

  2. 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)

  3. 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 :)

  4. Miguel,

    If you ran “insert into foo(name) select (name) from foo2;” and there are 2 rows in foo2, the auto_increment of foo will grow 3

    Is there any trick method :)

  5. TNT says:

    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)
    begin
    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);

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

    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?

  6. Mark R says:

    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)
    SELECT f2.name
    FROM foo2 f2

    Thanks,
    Mark

  7. Roman Piller says:

    Dear Miguel

    mysql from 5.1.22 change autoincrement mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

    I have the same problem.

    1.
    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
    innodb_autoinc_lock_mode=0

    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

  8. Curt says:

    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.

  9. 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;

  10. Variants:

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

    INSERT INTO foo (id,name) SELECT f.id, “abc” FROM mutex AS m LEFT JOIN foo AS f ON f.name = “abc” WHERE m.i = 1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(f.id);
    SELECT 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)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

  11. Part says:

    ALTER TABLE table_name ENGINE=MyISAM

    Workes for me. Our table is always kept very small, so no need for InnoDB.

  12. santosh says:

    is this issue fixed in the recent Mysql InnoDB version >= 5.6 ?

Speak Your Mind

*