Resetting AUTO_INCREMENT on myISAM without rebuilding the table

Question

Please help I am in major trouble with our production database. I had accidentally inserted a key with a very large value into an autoincrement column, and now I can't seem to change this value without a huge rebuild time.

ALTER TABLE tracks_copy AUTO_INCREMENT = 661482981

Is super-slow.

How can I fix this in production? I can't get this to work either (has no effect):

myisamchk tracks.MYI --set-auto-increment=661482982

Any ideas?

Basically, no matter what I do I get an overflow:

SHOW CREATE TABLE tracks
CREATE TABLE tracks (
...
) ENGINE=MYISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1

Answer 1

Have you dropped the record with the very large key? I don't think you can change the auto_increment to a lower value if that record still exists.

From the docs on myisamchk:

Force AUTO_INCREMENT numbering for new records to start at the given value (or higher, if there are existing records with AUTO_INCREMENT values this large)

Answer 2

After struggling with this for hours, I was finally able to resolve it. The auto_increment info for myISAM is stored in TableName.MYI, see state->auto_increment in http://forge.mysql.com/wiki/MySQL_Internals_MyISAM. So fixing that file was the right way to go.

However, myisamchk definitely has an overflow bug somewhere in the update_auto_increment function or what it calls, so it does not work for large values -- or rather if the current value is already > 2^31, it will not update it (source file here -- http://www.google.com/codesearch/p?hl=en#kYwBl4fvuWY/pub/FreeBSD/distfiles/mysql-3.23.58.tar.gz%7C7yotzCtP7Ko/mysql-3.23.58/myisam/mi_check.c&q=mySQL%20%22AUTO_INCREMENT=%22%20lang:c)

After discovering this, I ended up just using "xxd" to dump the MYI file into a hexfile, edit around byte 60, and replace the auto_increment value manually in the hexfile. "xxd -r" then restores the binary file from the hex file. To discover exactly what to edit, I just used ALTER TABLE on much smaller tables and looked at the effects using diffs. No fun, but it worked in the end. There seems to be a checksum in the format, but it seems to be ignored.