| 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: | |
| 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 | ||
[4 May 2011 16:26]
MySQL Verification Team
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
[15 Aug 2012 11:26]
Igor Timoshenko
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.
[28 Feb 2013 17:09]
Marin Bezhanov
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)
[23 Sep 2013 9:25]
shilei shi
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.
[20 Nov 2013 5:47]
Amit Joshi
I have got the same bug in Mysql 5.6.13 running on Windows 7 machine. Any updates/patches regarding this issue.
[9 Dec 2013 10:18]
MySQL Verification Team
Bug #71114 marked as duplicate of this one.
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)