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 behavior. 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 behavior 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 fooG
*************************** 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 😉

21 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Shlomi Noach

The LEFT JOIN is a cool trick!

0
Peter Zaitsev

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)

0
Miguel Angel Nieto

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 🙂

0
Lin Xiaobin

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 🙂

0
TNT

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?

0
Mark R

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

0
Roman Piller

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

0
Curt

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.

0
YuriKolovsky

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;

0
Ruud H.G. van Tol

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

0
Part

ALTER TABLE table_name ENGINE=MyISAM

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

0
santosh

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

0
Art

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

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

0
Anna

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

http://desmart.com/blog/be-careful-with-mysqls-auto-increment-how-we-ended-up-losing-data

0
Cai Black

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),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE DEFINER=myuser@localhost TRIGGER mydb.mytable_BINS
BEFORE INSERT ON mytable FOR EACH ROW
BEGIN
SET NEW.id = UUID();
END
$$

DELIMITER ;

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.

Enjoy.

0
Adam

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

0
Adam

Ignore my previous comment

0
Piotr

With auto_increment you should be careful 😉 I leave here a link to our article because it could be helpful for a lot of people. Cheers! Be Careful With MySQL’s auto_increment. How We Ended Up Losing Data

0
Renato Byrro

Great solution, thank you very much for sharing!

0
Rick James

This avoids the mutex table:

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

INSERT INTO iignore(name)
SELECT a.name
FROM ( SELECT ‘foo’ AS name FROM DUAL ) AS a
LEFT JOIN iignore AS i USING(name)
WHERE i.id IS NULL;

0
Viktor Kovacs

Rick, Error Code: 1054. Unknown column ‘‘foo’’ in ‘field list’

0