Slow auto_increment reset

Question

I have a lot of tables and because of some reasons I need to adjust auto increment value for this tables on application startup.

I try to do this:

mysql> select max(id) from item;
+----------+
| max(id)  |
+----------+
| 97972232 |
+----------+
1 row in set (0.05 sec)

mysql> alter table item auto_increment=1097972232;

In another session:

afrolov@A1-DB1:~$ mysql -u root -e "show processlist" | grep auto_increment
472196  root    localhost       test    Query   39      copy to tmp table       alter table item auto_increment=1097972232

MySQL is starting to rebuild table! Why MySQL need to do it? How can I avoid rebuilding huge tables while adjusting auto_increment value?

MySQL 5.0, InnoDB.
Table definition:

 CREATE TABLE `item` (
      `id` bigint(20) NOT NULL auto_increment,
      `item_res_id` int(11) NOT NULL default '0',
      `stack_count` int(11) NOT NULL default '0',
      `position` int(11) NOT NULL default '0',
      `place` varchar(15) NOT NULL default '',
      `counter` int(11) NOT NULL default '-1',
      `is_bound` tinyint(4) NOT NULL default '0',
      `remove_time` bigint(20) NOT NULL default '-1',
      `rune_res_id` int(11) default NULL,
      `rune_id` bigint(20) default NULL,
      `avatar_id` bigint(20) NOT NULL,
      `rune_slot_res_id` int(11) default NULL,
      `is_cursed` tinyint(4) NOT NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `avatar_id` (`avatar_id`,`place`,`position`),
      UNIQUE KEY `rune_id` (`rune_id`),
      KEY `idx_item_res_id` (`item_res_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=97972233 DEFAULT CHARSET=utf8;

About why I have to do this. Long story short i want to workaround mysql innodb issue about reseting auto_increment value on server restart. Sometimes we copy rows from our tables to another tables and we must keep rows id unchanged. When we add one row (with id=1 for example) to table1, copy row to table2 , delete row from table1 and restart MySQL, then when we create a new one row in table1 this row will get id=1 too. So if we will have to copy row to table2 we get unique constraint violation. We already have a lot of code and it will be hard to rewrite it all. Adjusting autoincrement value seems the easiest way to fix this problem.

Added:

MySQL 5.5 - all the same :(

Answer 1

simply add a temporary record that has desired auto_increment_id-1 to each table, and remove the record after that, quick and easy, but probably too dirty

example:

insert into item set id=1097972232-1;

after the execution, the next auto_increment will be 1097972232, which is what you desired

this can avoid slowness

Answer 2

This is a documented "feature" of MySQL:

If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column). For MyISAM tables, you can speed up the index re-creation operation (which is the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

MySQL 5.1 and 5.5 support a few more alter table operations w/o a temporary table, but changing the auto_increment is not documented to be one of those.

Why do you need to change the auto_increment value, anyway? This isn't something you should be doing routinely.