3

There just HAS to be a way to reset the auto_increment value on a table without rebuilding the entire index. I have a table with over 2 billion rows in it that accidentally got an ID inserted into it near 4.2 billion. From past experience I know that trying to set the auto_increment value back to what it should be will force mysql to rebuild the entire index, which would probably take 24 hours on a table this size. To be honest, I can't believe that this is just "how" it works by default. There's absolutely no need to rebuild an entire table index just beacuse you want to change this value.

There has to be a way. But I can't find one anywhere. IDEAS PLEASE!

(Rebuilding indexes, I know that myisamchk can do it 100x faster than the mysql process itself can. But I can't tell mysql to use myisamchk instead of itself to rebuild the index after changing the auto increment ID. There has to be a way!!!)

weegee
  • 143
  • 7
Sean
  • 379
  • 2
  • 3
  • 8

1 Answers1

1

Based on this article, it looks like you could reset the accidental ID to a proper value, or drop the row and then restart the server as the auto-increment value for innodb tables is held in memory. I haven't tried this before but it may work.

sreimer
  • 2,168
  • 14
  • 17
  • Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time. – Sean Apr 05 '11 at 07:03
  • 1
    This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way http://stackoverflow.com/questions/2686032/resetting-auto-increment-on-myisam-without-rebuilding-the-table – sreimer Apr 05 '11 at 13:55