Failed to read auto-increment value from storage engine, Error Number: 1467

Question

When inserting data in mysql i get this error:

Error Number: 1467 Failed to read auto-increment value from storage engine

I don't now how to solve this issue please any help will be appreciated.

MJ X · Accepted Answer · 2013-10-07T09:28:16.847

10

After some searching i found the answer and it solved my problem.

run this sql query it will fix the problem

 ALTER TABLE `YOUR_TABLE`  AUTO_INCREMENT =1

edited Oct 07 '13 at 09:28

answered Jul 17 '13 at 04:12

MJ X

8,506
12
74
99

7

It's a bad approach. What about the existing rows? Consider changing data type as @spencer7593 suggested. – Wasif Jul 17 '13 at 04:43
1

Knowing how would you solved it would help other people a lot. Could you publish the solution and mark it as the good one please? – enTropy Aug 23 '13 at 20:19
One thing might be useful for the people who are having this issue is to , when adding **auto_increment** default to an existing table, first check if there are already sparse (non-continuous or discrete) value existing for the field you are trying add this attribute to. For my case there has already been existing sparse value in that field and I was getting this error while trying to add this. I had to _truncate_ the table and then add the attribute, as that was a test DB. But it might not be a solution for many of us. – codarrior Oct 22 '15 at 04:30

score 7 · Answer 2 · answered Jul 22 '15 at 08:09

To add a little comment to kiddingmu's answer: it is not just a question of the number of digits, but also of the range of the datatype. If the column is INT(11), the 11 says that 11 digits are used for display; but this does not release the constraint that INT can only encode the range -2147483648:2147483647 when signed, and 0:4294967295 when unsigned.

So: for an INT(11) column, an AUTO_INCREMENT of 10000000000 will work; an AUTO_INCREMENT of 90000000000 will not, despite it being 11 digits.

If a larger range is needed, then another type should be used, like BIGINT.

score 5 · Answer 3 · edited Dec 18 '13 at 17:18

5

I received this message too. My problem was that my table was not sorted by index. Try using the following:

ALTER TABLE  `YOUR-TABLE` ORDER BY  `index` ;

edited Dec 18 '13 at 17:18

Joel

4,732
9
39
54

answered Dec 18 '13 at 17:01

swe

61
1
1

yay, thanks, this worked, I spent more time than was needed to find this solution, thank You, thank You – DennisWPaulsenJR Apr 05 '19 at 20:21

score 4 · Answer 4 · answered Jul 17 '13 at 04:12

One possible explanation for this behavior is that the autoincrement value has reached the maximum value for the datatype, and its not possible for the database engine to increment it by one.

I suggest you check the current value. One relatively easy way to do that is to run a SHOW CREATE TABLE mytable;, the table definition will show the current value. (You can also query the information_schema.tables view, to get the same information.)

score 1 · Answer 5 · answered Sep 11 '14 at 08:30

As spencer7593 says, the autoincrement value has reached the maximum value for the datatype

I just came to the problem.

use the command SHOW CREATE TABLE tablename;, I get

table name {
`id` int(11) NOT NULL AUTO_INCREMENT,
 ......
}ENGINE=InnoDB AUTO_INCREMENT=100000000000 DEFAULT CHARSET=utf8

You will see the length of 100000000000 is 12, beyond the limit 11.

score 0 · Answer 6 · answered Jan 22 '16 at 10:26

0

Check your database structure properly. I have also faced this problem, but I found some error in database structure. After fix the structure, problems were resolved.

answered Jan 22 '16 at 10:26

Debanjan Roy

21
1
8

score 0 · Answer 7 · answered Mar 08 '18 at 18:58

Another possibility with this error is that you've hit the max value in the ID field (generally an INT). We had this error when our ID values got close to 4294967295. We ended up having to drop the ID from the table in order to get past the issue. The various INT fields are mentioned in this answer: https://stackoverflow.com/a/5634147/4573630

score 0 · Answer 8 · edited Dec 06 '18 at 18:18

0

I had the same problem, after changing ID column to id, and after exporting my db, so I just switched the column back to ID and then back to id again, and every thing worked fine after. Working with elequent orm in laravel, it expects column id and I had column ID, that is why I changed it in the first place

edited Dec 06 '18 at 18:18

marc_s

732,580
175
1,330
1,459

answered Dec 05 '18 at 15:08

y77

1

score 0 · Answer 9 · answered May 19 '21 at 11:44

0

**For me it was the auto increment ID(column) that brings about the Error ** What i just do to solve it is i drop(delete) the ID column and add it again.

answered May 19 '21 at 11:44

Abass Olasunkanmi

1

score 0 · Answer 10 · answered Mar 07 '23 at 11:34

0

Just alter the table if you are using sequelize run sync({force: false})

answered Mar 07 '23 at 11:34

Michael Njuguna

98
1
5

score -4 · Answer 11 · edited Mar 29 '17 at 16:09

-4

ps aux | grep mysql 
sudo kill (your pid) 
/etc/init.d/mysql restart

edited Mar 29 '17 at 16:09

FelixSFD

6,052
10
43
117

answered Mar 29 '17 at 15:52

Sakerbook

1

1

Restarting MySql does not address this issue. – Layton Everson Mar 29 '17 at 16:37
In addition to this answer not being relevant to the post, there's no accompanying commentary. Putting the instructions to restart MySQL without saying what they do is potentially dangerous as it could break things (if the user is a beginner enough to blindly execute instructions without knowing what they do, they are probably unable to fix problems if MySQL does not come back up). – risoldi Jun 21 '19 at 09:33

Failed to read auto-increment value from storage engine, Error Number: 1467

11 Answers11

Linked