Reputation: 1359

mysql Failed to read auto-increment value from storage engine

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

Upvotes: 31

Answers (18)

shikhar vijayvergiya

Reputation: 11

I had the same problem.

Reason: I have changed the name of my table field's name with primary key and auto increment property, due to which auto increment stopped working and the error 1467 appeared.

What worked for me is resetting the primary key in phpmyadmin as shown below:

Goto 'Structure' tab of table and click on 'indexex' as shown in Image 1.
Now edit the primary key as in Image 1.
Simply click Go as in Image 2.

It worked for me.

Upvotes: 0

jacouh

Reputation: 8741

Today, I've the same error when I tried to insert a record into a table named mytablename:

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

In the table I've only 52269 records, max(id) = 52269. I've tried following command under mysql command console, but nothing takes place, error persists:

#
# set  AUTO_INCREMENT  = max(id) + 1: not work:
#
ALTER TABLE mytablename AUTO_INCREMENT = 52270;

I must find another solution. When I do:

SHOW TABLE STATUS FROM mydatabase WHERE `name` LIKE 'mytablename' ;

It shows that my Auto_Increment value is 0, that is clearly in error, it must be greater or equal to 1.

I modified any colum in the table like:

ALTER TABLE mytablename CHANGE COLUMN mycharfield mycharfield varchar(255) DEFAULT NULL AFTER id;

I do again the show table status command,

Auto_Increment value has been automatically changed to 52270, that is good value.

All goes since then...

Upvotes: 0

Iwan Ross

Reputation: 354

fixed it very easily. Truncated the table and uploaded the data again and the error disappears.

Upvotes: -1

Waqar

Reputation: 848

My workaround was to alter the table and rename it to something like orignal_backup and save, then again rename it back to orignal one, this trick worked for me.

Upvotes: 0

Diego Gandino

Reputation: 111

I resolve this problem uncheck the option AUTO_INCREMENT of the field and check again

Upvotes: 0

JakesIV

Reputation: 41

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

Upvotes: 2

Markus Grob

Reputation: 1

I had the problem, that the auto_increment was set to 0, but setting the auto_increment explicitly did not work (stayed at 0). I entered an entry manually with an ID and after this, the value was set right.

Upvotes: 0

Mike

Reputation: 678

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 :)

Upvotes: 25

Christian Lundahl

Reputation: 2060

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.

Upvotes: 1

ananthakumar

Reputation: 1

I have found the parameter was enabled. it should not be more than '1' as per DB-experts....

In my case it was set to '4', shown below.

mysql> select @@innodb_force_recovery; +-------------------------+ | @@innodb_force_recovery | +-------------------------+ | 4 | +-------------------------+

Hence, i did restart the mysql without passing the parameter during startup ,

later i was able to insert or update the tables ...hope it will help someone ..

Upvotes: -1

Rheena

Reputation: 1

I have the same issue. I have a table with thousands of records. I just changed the type of the field from int to bigint

Upvotes: -2

Deepak Sharma

Reputation: 61

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.

Upvotes: 4

Cedric Ipkiss

Reputation: 6337

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.

Upvotes: 7

Robert

Reputation: 21

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.

Upvotes: 2

Lee

Reputation: 1445

I had the same problem and the solution was to change the column from smallint(6) to int.

Upvotes: 0

Alex Rashkov

Reputation: 10015

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

Upvotes: 31

Shinbo

Reputation: 143

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.

Upvotes: 5

Marco

Reputation: 57573

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.

Upvotes: 20

mysql Failed to read auto-increment value from storage engine

Answers (18)

Related Questions