mysql Failed to read auto-increment value from storage engine

Question

I am having mysql table with one id field as auto-increment .

When I insert values to the table am getting error as

1467 - Failed to read auto-increment value from storage engine

Also the show table status shows me that the field with auto increment has

18446744073709551615 as Auto_increment value.

What would be the issue can any one help me ....?

Answer 1

I had the same error but in my case I had about 1.5k records in the table. I fixed it by resetting the AUTO INCREMEN like that:

ALTER TABLE `table_name`  AUTO_INCREMENT = 1

Answer 2

Problem could absolutely be that: convert 18446744073709551615 to hex and you'll find
$FFFF-FFFF-FFFF-FFFF.
If your field is an unsigned 64bit you reach its limit.

Answer 3

I started getting this error a couple of weeks back when running insert statements:

Duplicate entry '127' for key 'PRIMARY'

... even though my table was set to auto increment. I went in and changed the auto_increment value from 127 to 128 then I started getting this error:

1467 - Failed to read auto-increment value from storage engine

I eventually figured out that the table had been initially created with tinyint columns for the ID not standard ints ... so basically it couldn't comprehend numbers bigger than 127. I switched the column type to proper integers and that solved the issue.

Hope that helps someone :)

Answer 4

For my part, I made a dumb mistake. I had earlier altered my table and changed the name of the AUTO_INCREMENT column from ID to id. So, given column names are case-sensitive, subsequent inserts couldn't find the original column.

Answer 5

Actually, you can simply alter the column to delete its auto_increament property and set it as auto_increment again. On my side, this way did work.

Answer 6

I go the same error. I just alter the table and increase the size of my auto increment field and then run the following query -

ALTER TABLE `table_name`  AUTO_INCREMENT = 6221;

where 6221 is the last value of the filed with Auto_increment.

Answer 7

I experienced this error for the first time less than an hour ago. Resetting the auto_increment using a SQL statement in PHP MyAdmin failed. After looking for a solution I dropped the table and created a replacement. The error remained. Looking closer revealed the auto_increment was set to 0 even though I had specifically set the primary_key and auto_increment while creating the fields. Manually resetting auto_increment to 1, again using PHP MyAdmin, eliminated the error. Luckily for me I was only working with a 3-column table containing a few rows of test data.

Answer 8

I fixed it by removing the auto increment , saving table and then add auto increment again.

Answer 9

I had this problem today, too. I have a table with over two million rows and tried to add another 140K rows with LOAD DATA when this error occurred. I switched to the MyISAM engine and it all worked.