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

Answers


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.


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

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


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.


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.


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.


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.


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.


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


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


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.


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


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:

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

Image 1

Image 2

It worked for me.


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


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


Need Your Help

WPF Dynamic Layout with ItemsControl and Grid

wpf dynamic wpf-controls grid itemscontrol

I am creating a WPF form. One of the requirements is that it have a sector-based layout so that a control can be explicitly placed in one of the sectors/cells.

NHaml T4 templates for CRUD?

c# asp.net-mvc asp.net-mvc-2 t4 nhaml

I want to ask if anyone has or has seen T4 templates for NHaml that are the same as the default T4 CRUD (List, Create etc) templates from MVC 2.0?