MySQL Bugs: #61058: Autoincrement exceed expected value after INSERT INTO ... SELECT

Bug #61058	Autoincrement exceed expected value after INSERT INTO ... SELECT
Submitted:	4 May 2011 15:53	Modified:	4 May 2011 16:26
Reporter:	Franois Verry	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.37/5.5/5.6/5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement, insert into select, regression

Description:
When executing an INSERT INTO ... SELECT query on InnoDB tables, the autoincrement is incremented by more than the actual number of rows inserted. On MyISAM engine, the increment value equals the number of rows inserted.

In my case, this problem leads to random replication failures, though the full process has not been identified yet (it involves a combination of TRIGGERs, Foreign Keys, etc.).

I suspect that the number of skipped increments isn't consistent depending on the server.

How to repeat:
> CREATE TABLE `t1` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

> CREATE TABLE `t2` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

> INSERT INTO `t1`(`value`) VALUES('a'),('b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

> INSERT INTO `t2`(`value`) SELECT `value` FROM `t1`;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

> INSERT INTO `t2`(`value`) VALUES('c');
Query OK, 1 row affected (0.00 sec)

> SELECT * FROM `t1`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+
2 rows in set (0.00 sec)

> SELECT * FROM `t2`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  4 | c     |
+----+-------+
3 rows in set (0.00 sec)

Thank you for the bug report. Verified as described:

C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.93-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > CREATE DATABASE D20;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > USE D20
Database changed
mysql 5.0 > CREATE TABLE `t1` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT
    -> NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql 5.0 > SHOW CREATE TABLE t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+-----------------------------------------------------------------------------------------------------------------------------------------
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL auto_increment,
  `value` char(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql 5.0 >  CREATE TABLE `t2` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT
    -> NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql 5.0 > SHOW CREATE TABLE t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+-----------------------------------------------------------------------------------------------------------------------------------------
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL auto_increment,
  `value` char(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql 5.0 > INSERT INTO `t1`(`value`) VALUES('a'),('b');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0 > INSERT INTO `t2`(`value`) SELECT `value` FROM `t1`;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0 > INSERT INTO `t2`(`value`) VALUES('c');
Query OK, 1 row affected (0.02 sec)

mysql 5.0 > SELECT * FROM `t1`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+
2 rows in set (0.00 sec)

mysql 5.0 > SELECT * FROM `t2`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
+----+-------+
3 rows in set (0.00 sec)

mysql 5.0 > EXIT;
Bye

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.58-Win X64-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >CREATE DATABASE D20;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >USE D20
Database changed
mysql 5.1 >CREATE TABLE `t1` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT
    -> NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.19 sec)

mysql 5.1 > CREATE TABLE `t2` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT
    -> NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql 5.1 >SHOW CREATE TABLE t2;

mysql 5.1 >INSERT INTO `t1`(`value`) VALUES('a'),('b');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.1 >INSERT INTO `t2`(`value`) SELECT `value` FROM `t1`;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.1 >INSERT INTO `t2`(`value`) VALUES('c');
Query OK, 1 row affected (0.07 sec)

mysql 5.1 >SELECT * FROM `t1`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+
2 rows in set (0.00 sec)

mysql 5.1 >SELECT * FROM `t2`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  4 | c     |
+----+-------+
3 rows in set (0.00 sec)

mysql 5.1 >EXIT;
Bye

C:\DBS>55

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >CREATE DATABASE D20;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >USE D20
Database changed
mysql 5.5 >CREATE TABLE `t1` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT
    -> NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 > CREATE TABLE `t2` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `value` CHAR( 1 ) NOT
    -> NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql 5.5 >INSERT INTO `t1`(`value`) VALUES('a'),('b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >INSERT INTO `t2`(`value`) SELECT `value` FROM `t1`;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >INSERT INTO `t2`(`value`) VALUES('c');
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >SELECT * FROM `t1`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
+----+-------+
2 rows in set (0.00 sec)

mysql 5.5 >SELECT * FROM `t2`;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  4 | c     |
+----+-------+
3 rows in set (0.00 sec)

mysql 5.5 >EXIT;
Bye

Is it a bug? I have the same behaviour. Can it be depend on InnoDB Auto-Increment Locking?

I have two tables, the first table is an empty, autoincrement is 1. I copy rows from table2 to table1 using INSERT INTO ... SELECT ... syntax. After copying, the first table has autoincrement exceed expected value.

I'm experiencing the same bug, running mysql-5.6.10-winx64 on a Windows 7 machine. It's occurring only on InnoDB tables (MyISAM works fine)

I hit the same bug , running Percona-Server-5.5.27-rel29.0 on a Red Hat Enterprise Linux 5.8.expect to be fixed  soon.

I have got the same bug in Mysql 5.6.13 running on Windows 7 machine. Any updates/patches regarding this issue.

Bug #71114 marked as duplicate of this one.