Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE huge_table CHANGE id id int(6) NOT NULL and then wait hours for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes and not too much RAM – you could end up waiting days. If you want to make this happen quick – there’s another way. Not documented, but works well with both – InnoDB and MyISAM.
Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes more of an issue. One of the steps here is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild.
Disclaimer: try this at your own risk. It worked for me, it may work for you too, but always have a backup before doing that kind of stuff, as it is not the way MySQL would advice you to do it and we can’t guarantee it will work well for you either.
So, in a nutshell, all you have to do is create another table with desired table structure and switch .frm table definition files. For safety, I’d recommend to flush tables with read lock while switching .frm files. When and how it works:
auto_increment (removing). Let’s have a simple table with auto_increment we want to get rid of:
|
1 |
<br>CREATE TABLE `huge_table` (<br> `id` int(6) NOT NULL auto_increment,<br> `text` text NOT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=InnoDB<br> |
To remove auto_increment, we (1) create table with the same layout but without auto_increment, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards:
|
1 |
<br>-- 1.<br>mysql> CREATE TABLE `huge_table_new` (<br>-> `id` int(6) NOT NULL,<br>-> `text` text NOT NULL,<br>-> PRIMARY KEY (`id`)<br>-> ) ENGINE=INNODB;<br>Query OK, 0 rows affected (0.01 sec)<br><br>-- 2.<br>mysql> FLUSH TABLES WITH READ LOCK;<br>Query OK, 0 rows affected (0.00 sec)<br><br>-- 3.<br>mysql> ^Z<br>[1]+ Stopped mysql test<br># mv huge_table.frm huge_table_old.frm && mv huge_table_new.frm huge_table.frm && mv huge_table_old.frm huge_table_new.frm<br># fg<br>mysql test<br>-- 4.<br>mysql> UNLOCK TABLES;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> SHOW CREATE TABLE `huge_table`G<br>*************************** 1. row ***************************<br>Table: huge_table<br>Create Table: CREATE TABLE `huge_table` (<br> `id` int(6) NOT NULL,<br> `text` text NOT NULL,<br> PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1<br>1 row in set (0.00 sec)<br><br>mysql> INSERT INTO `huge_table` (text) VALUES ('test');<br>ERROR 1062 (23000): Duplicate entry '0' for key 1<br> |
Unfortunately, adding auto_increment does not work that way.
Enum values (add and remove). Enumerated values are added and removed the same way that auto_increment is removed. I’ve been a bit surprised, that removing value from enum() works as good as adding it – rows that have incorrect values are just returned as empty. But I suppose this does violate mysql data file structure, so be really careful with that one.
Default values. MySQL rebuilds table even if we only want to change the default value for new records so this may save one from a lot of trouble.
Table comment. I’m pretty sure that would work for changing table comment as well, however – changing a comment with a help of ALTER TABLE does not rebuild the table, so we better use the documented method for that.
What I’m surprised about is that changing a comment does not to require table to be rebuilt, while things like removing auto_increment or changing a default value still do even though this information is stored in table definition file.
If you’ll ever try this, please leave a comment if it did work for you. Maybe you have discovered some new things to alter that way?
