CODEWITHSUNDEEP
mysql
MJ X
MJ X

Reputation: 9044

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

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.

Upvotes: 13

Views: 37297

Answers (11)

Michael Njuguna
Michael Njuguna

Reputation: 118

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

Upvotes: 0

Abass Olasunkanmi
Abass Olasunkanmi

Reputation: 1

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

Upvotes: 0

y77
y77

Reputation: 1

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

Upvotes: 0

Stephen
Stephen

Reputation: 1

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

Upvotes: 0

Sakerbook
Sakerbook

Reputation: 1

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

Upvotes: -5

Debanjan Roy
Debanjan Roy

Reputation: 17

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.

Upvotes: -2

risoldi
risoldi

Reputation: 469

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.

Upvotes: 7

kiddingmu
kiddingmu

Reputation: 41

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.

Upvotes: 2

swe
swe

Reputation: 61

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` ;

Upvotes: 5

MJ X
MJ X

Reputation: 9044

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

Upvotes: 10

spencer7593
spencer7593

Reputation: 108380

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

Upvotes: 4

Related Questions