Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 0 down vote accepted

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.

share|improve this answer

I saw the same error message on a table with a bigint(20) key and looking at the output of

mysql>show table status like 'my_big_table';

It showed:

Auto_increment: 0

when it should have been:

Auto_increment: 2157452851

(as the last row inserted had a key value of 2157452850) There was no explanation of why the Auto_increment went to a value of 0 but it blocked inserts by our application.

I tried to do the ALTER TABLE to change the Auto_increment value, but it was apparent that the entire table was going to be rebuilt, which on a table this size would have taken many hours, so I canceled the alter.

The solution I found was to explicitly add a row with the next key value:

mysql> INSERT INTO `my_big_table` VALUES (2157452851,22808084,71,36376,'2013-03-16 15:09:55','2013-03-16 15:07:18','2013-03-16 15:09:55','Sent',NULL,7426);
Query OK, 1 row affected (0.03 sec)

This corrected the Auto_index value.

mysql> show table status like 'my_big_table'\G
Auto_increment: 2157452852

Hopefully this will help someone else.

share|improve this answer

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.

share|improve this answer

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.