Description:
If you create a table with an auto-inc key... and then you delete from the table where it removes the MAXIMUM values of the auto-inc key, and then restart mysql, MySQL for some reason auto-resets the Auto-inc key back to the previous maximum value, and thus overwrites values that may be used in other foreign key tables...
I have tested this on MySQL 5.1.29 and MySQL 5.1.38, and it works the same on both. The auto-inc counter key should stay where it's at... It should never go down unless you truncate the table to 0.
How to repeat:
create table t1
(
a int(10) NOT NULL AUTO_INCREMENT,
b int,
PRIMARY KEY (a)
) ENGINE=InnoDB;
Insert into t1 (b) VALUES(1),(2),(3),(4),(5),(6);
show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(10) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from t1 where b in (4,5,6);
Query OK, 3 rows affected (0.00 sec)
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(10) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
<<< RESTART MYSQL >>>
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(10) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
Make it not happen
