Mysql auto increment jumps when insert-select

Question

I am testing insert-select query and noticed an weird result.

CREATE TABLE `test` (
  `cnt` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`cnt`)
)

CREATE TABLE `test_current` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
)

First I created two tables, and insert some values into test_current

mysql> insert into test_current (a,b) values (1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

And I did this query

mysql> INSERT INTO test (a,b) SELECT a,b FROM test_current;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from test;
+-----+------+------+
| cnt | a    | b    |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    2 |
+-----+------+------+
2 rows in set (0.00 sec)

But when I did the query again

mysql> INSERT INTO test (a,b) SELECT a,b FROM test_current;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from test;
+-----+------+------+
| cnt | a    | b    |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    2 |
|   4 |    1 |    1 |
|   5 |    2 |    2 |
+-----+------+------+

The auto increment just skipped cnt for 3. I want to know what is this about.

In the result, you see "2 rows affected, 1 warning". Perhaps the warning has something to do with it. (edit: tried it myself, same result, no warning, MySQL 5.5) — Bart Friederichs, Sep 01 '12 at 08:18
The warning was this | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. | — user1640242, Sep 01 '12 at 08:22
I turned off binary log and did it without error but it resulted same. — user1640242, Sep 01 '12 at 08:26
If you do a normal insert after the insert..select, you'll see it also skips one. Looks like the use of the temporary table (that's how insert..select works) adds and removes a row or something. — Bart Friederichs, Sep 01 '12 at 08:28
It is not a bug, it is by-design. https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html — Gnought, Jun 17 '20 at 11:04

score 3 · Answer 1 · edited Sep 01 '12 at 08:40

3

You can reset the auto_increment value to 1 every time before inserting values into your table:

ALTER TABLE `test` AUTO_INCREMENT = 1;
INSERT INTO test (a,b) SELECT a,b FROM test_current;

edited Sep 01 '12 at 08:40

Jocelyn

11,209
10
43
60

answered Sep 01 '12 at 08:37

AnandPhadke

13,160
5
26
33

That may be a solution, but why do you think the problem is occurring in the first place? – SexyBeast Sep 01 '12 at 08:39

Haritsinh Gohil · Answer 2 · 2018-07-27T09:50:57.570

You can change innodb_autoinc_lock_mode=0 (“traditional” lock mode) from my.ini to avoid skipping values in primary key in some cases. See the manual mysql manual for innodb auto increment handling for more details.

As per manual 'The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics'.

'In this lock mode, all “INSERT-like” statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERT statements, and to ensure that auto-increment values assigned by any given statement are consecutive'.

Another thing to check is the value of the auto_increment_increment config variable. It's 1 by default, but you may have changed this. it is very uncommon to set it to something higher than 1 or 2, but possible.

Or if it dont work in your case you can also use query like the answer of AnandPhadke in this same page like :

ALTER TABLE tablename AUTO_INCREMENT = 1;
INSERT INTO tablename (col1,col2,col3) SELECT col1,col2,col3 FROM tablename;

score 1 · Answer 3 · answered Oct 02 '14 at 12:12

1

Put in your My.cnf:

innodb_autoinc_lock_mode=0

answered Oct 02 '14 at 12:12

Jonathan Edgardo

503
1
9
23

Mysql auto increment jumps when insert-select

3 Answers3

Linked