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
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