1

I receive this error when trying to insert something in the DB.

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

This is my table info: Row Statistics

Format Compact Options avg_row_length=620 checksum=1 delay_key_write=1 row_format=DYNAMIC Collation utf8_general_ci Next autoindex 52,267

how to solve this? Thanks.

Iman
  • 73
  • 3
  • 9
  • Where is the PHP code that generates the error? –  Jul 10 '13 at 05:02
  • sql insert statement but it's not the problem as the code was working correctly before the server was crashed and recovering the DB and importing it into new server. – Iman Jul 10 '13 at 05:21

1 Answers1

7

Any chance the auto incremented value is exceeding it's limit? What is your data type? and what is the current auto-incremented field's value?

Here is a similar discussion. Please check if it helps!

You can try this once on the table:

 ALTER TABLE table_name AUTO_INCREMENT =1
Community
  • 1
  • 1
Mayukh Roy
  • 1,815
  • 3
  • 19
  • 31
  • 2
    this can be caused by a negative auto_increment value being assigned.. an integer overflow on a signed field could cause this. – Orangepill Jul 10 '13 at 05:05
  • id is int(15) unsigned, The last inserted id is 52266 – Iman Jul 10 '13 at 05:09
  • how may records and what storage engine are you using? – Orangepill Jul 10 '13 at 05:11
  • 13650 records .. storage engine: InnoDB – Iman Jul 10 '13 at 05:15
  • I did take a look on it before asking and it doesn't help. @Mayukh – Iman Jul 10 '13 at 05:19
  • yes, of-course! checking it as per your given inputs :) – Mayukh Roy Jul 10 '13 at 05:21
  • Try this: ALTER TABLE `table_name` AUTO_INCREMENT =1 – Mayukh Roy Jul 10 '13 at 06:00
  • 1
    but this will reset my id to 1 which means the next id will be 1, may be that will cause duplication issue after that? @MayukhRoy – Iman Jul 10 '13 at 06:07
  • I dont think this will reset your id to 1, It will remove AI key and set it back as per the last inserted row. I tested it on my box! Anyway, you have a crashed system! you may create a duplicate table and check on it. – Mayukh Roy Jul 10 '13 at 06:13
  • again, if it doesn't work, try: ALTER TABLE some_table AUTO_INCREMENT = 0 .This will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column. Please let me know if any of these works, and which one :) – Mayukh Roy Jul 10 '13 at 06:19
  • none of them worked :( – Iman Jul 10 '13 at 06:24
  • Also when I try to update row I get that error: Got error -1 from storage engine – Iman Jul 10 '13 at 06:34
  • Well, I can not reproduce the error. It's getting very difficult for me to imagine and guess :( – Mayukh Roy Jul 10 '13 at 06:42
  • I had the same issue and had to set the primary key, auto_increment column to `bigint` instead of `int`, which maxes out at **2,147,483,647**. See http://stackoverflow.com/questions/7025153/duplicate-entry-2147483647-for-key-1 – Joshua Pinter Jan 08 '14 at 01:20