АДЪ и ИЗРАИЛЬ, где InnoDB

[robinbobin]

аццкий ад происходит с AUTO_INCREMENT на INSERT IGNORE в InnoDB


счетчик увеличивается ВСЕГДА, даже если record не вставилась....

и это mysql bug

но вроде в последних версиях mysql можно регулировать
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-configurable.html

зато нашел аццкий способ обходить это

https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/

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