1

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.

Phanindra
  • 203
  • 1
  • 4
  • 11

4 Answers4

3

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.

Ladadadada
  • 25,847
  • 7
  • 57
  • 90
Chuck Solie
  • 31
  • 3
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.

Jayakrishnan T
  • 278
  • 2
  • 8
  • 22
0

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.

Prince John Wesley
  • 340
  • 5
  • 9
0

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!

zvz
  • 1