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

Post your table definition and let's see which data type is taht field assigned — Marco, Sep 08 '11 at 11:00

score 30 · Answer 1 · answered Jun 05 '13 at 21:09

30

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

answered Jun 05 '13 at 21:09

Alex Rashkov

9,833
3
32
58

1

mine was saying "0" on phpmyadmin . i set it to 1 and all went back to the real value with the first insert – kommradHomer Jun 24 '14 at 10:30
Worked for me. This Question needs a combined answer from Marco (current accepted) because of the explanation, and infinity (this answer) because it's a very easy solution. – amenthes Jun 10 '16 at 23:35
Didn't work for me. So maybe not the universal panacea. Shinbo's solution worked for me: like turning the computer off and on again. But I'd kind of like to know what causes this sort of problem...! – mike rodent Nov 14 '17 at 19:23
never claimed it's the "ultimate" solution :) I'm glad you found answer though. – Alex Rashkov Nov 16 '17 at 10:50
1

DANGEROUS , very dangerous solution. – Nasser Al-Wohaibi Jun 18 '19 at 12:26

Mike · Answer 2 · 2018-07-11T09:18:04.683

22

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

edited Jul 11 '18 at 09:18

answered Oct 10 '12 at 16:06

Mike

628
10
22

2

Helped me! I was banging my head against this all day, and this saved me a lot more headache. – moustachio Jan 03 '14 at 01:07
This was perfect. Make sure that you check how big the number can go, as smallint and tinyint have relatively small limits. – Runner Jul 10 '14 at 01:29
1

You are a saviour. – Koushik Das Nov 12 '21 at 08:51
Exactly the same sequence of errors I came up against. Thank you @Mike – Ifedi Okonkwo Jan 17 '22 at 18:09

score 20 · Accepted Answer · answered Sep 08 '11 at 11:01

20

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.

answered Sep 08 '11 at 11:01

Marco

56,740
14
129
152

This is the field `ID` int(11) NOT NULL AUTO_INCREMENT and am having only 200 rows in that table that is field `ID` has the maximum value of 200 – ashu Sep 08 '11 at 11:05
@ash: export your db (or simply the table), DROP existing table and create a new one; then import data back being careful not to touch auto-increment value... – Marco Sep 08 '11 at 11:08
Actually now am having problem in dropping the table as the field `ID` used as foreign key i cant drop table. – ashu Sep 08 '11 at 11:40
@ash: if you want, export your db and sent it to me by email... I'll take a look... – Marco Sep 08 '11 at 11:43
@Marco i has this problem, my system is 64bit,Is this problem have been solve if i install a 64bit of mysql? – Sajad Nov 21 '13 at 19:43
@Marco Back old days, when you were even ready to get email :D – Nabin Mar 03 '19 at 15:25

score 7 · Answer 4 · answered Nov 10 '15 at 20:33

7

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.

answered Nov 10 '15 at 20:33

Cedric Ipkiss

5,662
2
43
72

score 5 · Answer 5 · edited Sep 21 '12 at 23:39

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.

edited Sep 21 '12 at 23:39

Mike Mackintosh

13,917
6
60
87

answered Sep 12 '12 at 14:58

Shinbo

143
2
8

3

and then you get duplicate key errors when inserting records (assuming the column is unique and/or primary key). Good work. – fancyPants Oct 05 '12 at 09:07
@tombom, I think you might have misunderstood his solution, but it does work and it doesn't cause duplicate key errors. – JamesRLamar Apr 28 '13 at 02:48

score 3 · Answer 6 · answered Apr 14 '16 at 04:45

3

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.

answered Apr 14 '16 at 04:45

Deepak Sharma

51
5

score 2 · Answer 7 · answered Aug 21 '14 at 13:13

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.

score 2 · Answer 8 · answered Feb 07 '19 at 05:53

2

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

answered Feb 07 '19 at 05:53

JakesIV

41
4

score 1 · Answer 9 · answered May 25 '18 at 11:19

1

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.

answered May 25 '18 at 11:19

Christian Lundahl

2,000
3
18
29

score 0 · Answer 10 · answered Jun 10 '14 at 18:43

0

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

answered Jun 10 '14 at 18:43

Lee

1,389
3
18
28

score 0 · Answer 11 · answered Aug 11 '18 at 10:47

0

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.

answered Aug 11 '18 at 10:47

Markus Grob

1

score 0 · Answer 12 · answered Apr 26 '19 at 14:15

0

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

answered Apr 26 '19 at 14:15

Diego Gandino

81
1
1

score 0 · Answer 13 · answered Mar 08 '20 at 23:51

0

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.

answered Mar 08 '20 at 23:51

Waqar

826
5
16

score 0 · Answer 14 · answered May 28 '20 at 19:24

0

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

answered May 28 '20 at 19:24

Iwan Ross

196
2
10

jacouh · Answer 15 · 2020-09-21T15:37:46.847

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

score -1 · Answer 16 · answered Dec 17 '17 at 12:01

-1

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.

Image 1

Image 2

It worked for me.

answered Dec 17 '17 at 12:01

shikhar vijayvergiya

1
1

1

Please, embed images into your post. Links to external resources may get broken easily in case they are moved or deleted from the external hosting. – Eduard Malakhov Dec 17 '17 at 13:29

score -1 · Answer 17 · answered Apr 12 '18 at 13:48

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

answered Apr 12 '18 at 13:48

ananthakumar

1

What parameter are you talking about? How did you restart exactly? What were the commands? How do you enable/disable parameters during startup? This answer is unclear. – pawamoy Apr 12 '18 at 14:05

score -2 · Answer 18 · answered Jan 04 '18 at 03:04

-2

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

answered Jan 04 '18 at 03:04

Rheena

1
2

mysql Failed to read auto-increment value from storage engine

18 Answers18

Linked