Home / server / Questions / 273074
Accepted
Phanindra
Asked: 2011-05-25 00:46:42 +0800 CST

MySQL Auto Increment failing

  • 772

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.

mysql autoincrement

4 Answers

  1. 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.

    • 2
  2. Best Answer

    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.

    • 1

  3. 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.

    • 0

  4. 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!

    • 0