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!!!)
Best Answer
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.