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.

Answer 1

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

Answer 2

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

Answer 3

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.