MySQL Bugs: #727: auto_increment after mysqld restart

Bug #727	auto_increment after mysqld restart
Submitted:	26 Jun 2003 1:01	Modified:	26 Jun 2003 1:38
Reporter:	Thomas Mayer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Redhat 7.3)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I had some auto_increment problems after I restarted the Server:

It should not make a difference wether I restart mysqld in between or not.

Thanks in advance,
Thomas Mayer

How to repeat:
=================
MySQL 4.0.13 
RedHat Linux 7.3
MySQL RPM-Binaries installed
================= 
How-To-Repeat:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 353 to server version: 4.0.13-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test1
    -> (id int(10) auto_increment not null,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values(DEFAULT), (DEFAULT), (DEFAULT);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

mysql> insert into test1 values(DEFAULT), (DEFAULT), (DEFAULT);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
+----+
3 rows in set (0.00 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 353 to server version: 4.0.13-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

=========
This seems to be correct.

Next, we do the same, but restart mysqld before the delete-statement.
=========

mysql> create table test1
    -> (id int(10) auto_increment not null,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values(DEFAULT), (DEFAULT), (DEFAULT);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

=================
<---Restart mysql, this makes the difference!
$service mysql --full-restart
=================

mysql> delete from test1;
Query OK, 3 rows affected (0.01 sec)

mysql> insert into test1 values(DEFAULT), (DEFAULT), (DEFAULT);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

================
I would expect the auto_increment field to continue with 4, 5, 6. 

Suggested fix:
current auto_increment value should not be reset in any case.

I've just seen this has already been reported (#199 in suspended) months ago and the behaviour should even be documented:

"
[22 Apr 10:37am] Heikki Tuuri 
Does no break replication as the binlog contains the auto-inc column value for
each new insert / replace.

This behavior is a documented feature which will be fixed in the future at the
same time a fast COUNT(*) is introduced to InnoDB.[26 Apr 7:25am] Michael Widenius 
I have now documented this behaviour in the MySQL manual
"

Sorry for duplicate entry. Hope, this is solved soon.

Hi!

Yes, this is the documented behavior.

http://www.innodb.com/ibman.html#InnoDB_auto_inc
"
InnoDB uses the following algorith to initialize the auto-increment counter. After a database startup, when a user the first time does an insert to a table T or calls SHOW TABLE STATUS where the table T is shown, then InnoDB executes 

SELECT MAX(auto-inc-column) FROM T FOR UPDATE,

and assigns that value incremented by one to the column and the auto-increment counter of the table. 
"

You should not assume that the auto-inc counter survives over a mysqld restart. In the future we will probably make it to survive, though it will consume some CPU time.

Regards,

Heikki

I have the same problem.

I'm using MySQL 5.1.52-enterprise-commercial-advanced, on an Red Hat 5.4 x64.

Exists any patch for this problem?

Sergio,

starting from MySQL 5.0 you can use triggers to address this problem. A workaround for me is to use the auto_increment of an archive_table:

delimiter //
drop trigger if exists trigger_autoinc_tbl;
CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
declare auto_incr1 BIGINT;
declare auto_incr2 BIGINT;
SELECT AUTO_INCREMENT INTO auto_incr1 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl';
SELECT AUTO_INCREMENT INTO auto_incr2 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl_archiv';
IF (auto_incr2 > auto_incr1 and NEW.id<auto_incr2) THEN
SET NEW.id = auto_incr2;
END IF;
END;//
delimiter ;

Further reading: http://www.slicewise.net/index.php?id=82

This is duplicate of bug #199.