MySQL Bugs: #34077: Storing auto_increment counter on the disk.

Bug #34077	Storing auto_increment counter on the disk.
Submitted:	26 Jan 2008 10:38	Modified:	20 Oct 2008 21:39
Reporter:	Andrej Nagy	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0-bk	OS:	Any (AUTO_INCREMENT)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Auto increment is stored on the heap and is reset after system restart.
If i want to have an autoicrement column value unique forever (not reused after deletes), there is really nothing better than create other temporary table for every table in my system to get mysql work properly (as other DB's do). I'm really confused that I have chosen to go to InnoDB engine, but now it is too late to go back to MyISAM. I don't understant why the counter is stored on the heap.

"Great job" guys.

How to repeat:
create table with autoincrement column. insert 10 rows, delete 10 rows, restart mysql, insert one row and the value is 1. I would like to have the value 11.

Suggested fix:
Before shutdown store the autoincrement value on disk.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Our newest version at the moment is: MySQL 5.0.51

I tested with MySQL 5.0.38 and 5.0.51, same results. Look here:

mysql> create table t(id serial, num integer) engine=innodb;

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `num` int(11) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> insert into t(num) values(1),(2),(3),(4),(5);

mysql> select * from t;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+

mysql> delete from t;
Query OK, 5 rows affected (0.02 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `num` int(11) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

mysql> insert into t(num) values(1),(2),(3),(4),(5);

mysql> select * from t;
+----+------+
| id | num  |
+----+------+
|  6 |    1 |
|  7 |    2 |
|  8 |    3 |
|  9 |    4 |
| 10 |    5 |
+----+------+

Sorry, my misstake. I forget the restart.

On the same session:

mysql> delete from t;

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `num` int(11) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

mysql> \q

$ ./bin/mysqladmin shutdown
$ ./libexec/mysqld --defaults-file=...

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `num` int(11) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> insert into t(num) values(1),(2),(3),(4),(5);

mysql> select * from t;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+

The id is 1 instead of 11.

Using MyISAM all works as expected. After Shutdown/Start the next id is 11.

This is partly documented behavior.

From http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

Beginning with MySQL 5.0.3, InnoDB supports the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, to set the initial counter value or alter the current counter value. The effect of this option is canceled by a server restart, for reasons discussed earlier in this section.

This is a duplicate of bug#21641