MySQL Bugs: #58826: auto_increment value reset on table after database server system crash

Bug #58826	auto_increment value reset on table after database server system crash
Submitted:	8 Dec 2010 18:40	Modified:	22 Jan 2011 22:04
Reporter:	Kevin Williams	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.52	OS:	Linux (sles10 sp3)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, crash, innodb, reset

Description:
An InnoDB table is created and auto_increment is enabled.  During regular processing, as new rows are added, the value increments as expected.  During normal service restart or stop/start, the value of the auto_increment key is preserved.  However, if the server system crashes and the database is restarted, the auto_increment value is reset to 1.

The command "show create table" will show the current value of the auto_increment as part of the output (could also query the table, directly)

After a crash and the database restarts, the value AUTO_INCREMENT is missing in the output of the above command and has to be reset to the proper value via an "alter table" command.

As an additional note, the database and all control files are stored on a common storage device that is attached to two identical servers.  The MySQL instance can be migrated from server A to server B by command, or by system failure.  The storage device is only visible to the server running the instance at that time.  This configuration allows high availability to be implemented for MySQL.  The controlled fail over works correctly (where a service is stopped on one server and restarted on the other), but the auto_increment reset occurs when a server crashes to force the fail over. 

How to repeat:

create an InnoDB table and enable auto_increment...
update the table to increment the value...
crash the system and examine the value...  ours will reset to 1

Suggested fix:
unknown

Thank you for the report.

This should be some bad crash. Please send us your error log file.

Regretfully, I cannot send logs from this event.

I can clarify that the issue only occurs when the mysql daemon is abruptly terminated by a system failure or by simply killing off the daemon with a SIGKILL signal.

The interesting thing is that doing a show table create before the event shows the AUTO_INCREMENT in the response.  After the event, the same command shows there is no AUTO_INCREMENT in the response.  In order to correct this, an update table needs to be executed with an appropriate value for the AUOT_INCREMENT entry, else it gets set to 1.  Fortunately, we have a source for the correct value in another table where it is explicitly archived.

This is not a mysql crash, rather the system event causes the error.  It seems that there is either a missing cached vector for the auto_increment values, or that a transaction replay on restart is incorrect.

As I cannot transmit the error logs (which ones are of interest?) what should I be looking for in order to assist you in resolving this event?

Again,

many thanks for your help.

Thank you for the feedback.

> I can clarify that the issue only occurs when the mysql daemon is abruptly terminated by
a system failure or by simply killing off the daemon with a SIGKILL signal.

Have you tried forcing InnoDB recovery? See http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html for details

No I have not tried the InnoDB recovery.  I will do so the next chance I have and let you know how it fared.

kevin

OK. We will wait feedback from you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".