Search code examples
mysqlinsertsequenceauto-incrementbulk

mysql query bulk insert caused sequence gap

I have a query to select and insert rows in a table with auto increment col as key.

insert into table a select * from table B

I found that after executing the query, there is sequence gap in the auto increment column in table B

Can it be possible? Or my select statement failed to insert some records ??

Please help.

I can not repro the case as this is a production server, and I don't have the backup before exec the query.

Many thanks.

Solution

  • If you are using InnoDB with INSERT IGNORE, you may want to read this article that talks about this issue:

    Avoiding auto-increment holes on InnoDB with INSERT IGNORE

    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.

    When using a simple insert from select, there's a known bug about this

    In conclusion, i wouldn't say this is a problem with your query, but more related to the way the engine deals with the insert from select blocks.