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