Description:
InnoDB resets the max value on restart to "SELECT MAX(COL)+1 FROM T". Make
the max value persistent on restart.
How to repeat:
create table t(c int PRIMARY KEY AUTO_INCREMENT) engine=innodb;
insert into t values(NULL);
insert into t values(NULL);
insert into t values(NULL);
show create table t;
-----------------------------------------------------------------+
| t | CREATE TABLE `t` (
`c` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
delete from t;
Query OK, 3 rows affected (0.00 sec)
show create table t;
-----------------------------------------------------------------+
| t | CREATE TABLE `t` (
`c` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
Restart the server
** NOTE: Here the situation is quite tricky, because the max value calculation
doesn't skip delete marked records, the output below depends on whether
purge has removed the delete marked records or not. If it has then we get
the output below, otherwise it will be 4. **
show create table t;
------------------------------------------------+
| t | CREATE TABLE `t` (
`c` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
insert into t values(NULL);
select * from t;
+---+
| c |
+---+
| 1 |
+---+
Suggested fix:
Make the value persistent, so that it will always return MAX(c)+1 == same value before restart.
