Bug #70692 auto_increment wrong value when using insert into... select from
Submitted: 23 Oct 2013 3:46 Modified: 23 Oct 2013 6:21
Reporter: ricky leung Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.12,5.6.14,5.5.34,5.1.72 OS:Linux
Assigned to: CPU Architecture:Any

[23 Oct 2013 3:46] ricky leung
Description:
When copying into a table with a primary key with auto_increment, using insert into...select from, the auto_increment value of the table becomes wrong.  Subsequent inserts into the table will use the wrong value

How to repeat:
1. Create any table and insert some values:
create table t1(name varchar(10));
insert into t1 values ('abc');
insert into t1 values ('abcd');
insert into t1 values ('ghj');
insert into t1 values ('ghjd');
insert into t1 values ('ghjd oop');

2. Create a table with auto_increment field:
create table t2 (ID int not null primary key auto_increment, NAME varchar(10) not null);
alter table t2 add unique(name);

3. Populate the table t2 with values from t1:
insert into t2(name) select name from t1;

4. Verify the values from t2, everything seems fine up to now:
mysql> select * from t2;
+----+----------+
| ID | NAME     |
+----+----------+
|  1 | abc      |
|  2 | abcd     |
|  3 | ghj      |
|  4 | ghjd     |
|  5 | ghjd oop |
+----+----------+
5 rows in set (0.00 sec)

5. Check the table t2 you will see the auto_increment value not correct:
mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(10) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6. Now insert some more values into t2, the auto_increment field will be wrong:
mysql> insert into t2(name) values ('new');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+----------+
| ID | NAME     |
+----+----------+
|  1 | abc      |
|  2 | abcd     |
|  3 | ghj      |
|  4 | ghjd     |
|  5 | ghjd oop |
|  8 | new      |
+----+----------+
6 rows in set (0.00 sec)

Suggested fix:
Please fix the wrong auto_increment symptom when using insert into...select from syntax
[23 Oct 2013 6:21] MySQL Verification Team
Hello ricky,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[23 Oct 2013 9:25] Peter Laursen
There have been lots of similar discussion here.

In my understanding this is expected behavior with innodb_autoinc_lock_mode variable set to "1".

Peter
(not a MySQL/Oracle person)