Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ? –  anttiR Nov 9 '11 at 9:34
add comment

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

share|improve this answer
    
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 5 '11 at 7:03
1  
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/… –  sreimer Apr 5 '11 at 13:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.