MySQL Auto Increment failing

Question

I am having a table with one column of primary key and auto increment of int(11) data type.

When I am trying to insert into that table, I am getting following error. Failed to read auto-increment value from storage engine Query

When I restart MySQL service, it is working fine.

I am sure that this is not problem with "Auto Increment Number Exceeded", as when I try to insert from outside it is getting inserted properly.

Is this problem with Auto Increment or some other one.

Answer 1

Basically this is a bug in MySQL that causes the problem but a work around is simple. The problem happens when the Auto-Increment value of a table grows beyond the limit.

Just run this SQL query in MySQL to fix the bug.

ALTER TABLE table_name AUTO_INCREMENT =1

Table_name is the name of the table where you found the error while inserting data into. Don’t bother about the Auto_Increment value even if you have records in your table.

Answer 2

When I am trying to insert into that table, I am getting following error. Failed to read auto-increment value from storage engine Query

This could be a MySQL Bug(#35602,#42566). Try this workaround.

Answer 3

On wamp where mysql version is 5.1.30 running on windows 7 64bit, it did not work. By running alter table it happens something (I see altered rows), but value does not fix, so after refresh it comes back to null. Mysteriously removing auto_increment from key and setting it back, auto_increment value returned. Probably, it 64 bit issues, but hope someone will save hours solving this issue by resetting ID key to non auto_increment and setting back!