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 2 3 4 5 | CREATE TABLE `huge_table` ( `id` int(6) NOT NULL auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB |
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | -- 1. mysql> CREATE TABLE `huge_table_new` ( -> `id` int(6) NOT NULL, -> `text` text NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) -- 2. mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.00 sec) -- 3. mysql> ^Z [1]+ Stopped mysql test # 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 # fg mysql test -- 4. mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> INSERT INTO `huge_table` (text) VALUES ('test'); ERROR 1062 (23000): Duplicate entry '0' for key 1 |
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?
I think if you use the alternate ALTER TABLE… ALTER COLUMN syntax, you can change the default without rebuilding the table.
More specifically,
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
I think this doesn’t rebuild the table, but I could be wrong. It has been a while since I’ve done this and I’m not testing it, just relying on my memory. Am I wrong? 🙂
We have done this (ALTER TABLE…ALTER COLUMN…SET DEFAULT) in production and it is verified that the entire table does not need to be rebuilt.
Baron,
thanks, indeed – changing/dropping default value using ALTER column works here. Even though I would expect MODIFY to work the same way if only the default value is changed or removed, it’s good to know there’s a documented way to do it without a need to rebuild the table. You’re memory seems to be just fine!
Oh my gosh. This could be a huge win. I’ve got several tables well north of 500 million rows using MySQL v4.1.14.
I followed MySQL recommendation when these tables were small and first created to use auto-inc primary key. At
the time (over 2 years ago), I didn’t know there was any problem.
Since these tables get parallel inserts, I’m getting lots of auto-inc waits. Now that these tables are huge, I’ve got
a big problem with scalability. It is really frustrating to take a vendor’s advice and then be let down when you really
need it to work. That is the kind of thing I got bit with in the past using a well know vendor’s PC software.
If this works, then I can push the auto-inc’ing up into my application and relieve MySQL of this burden
and the resulting contention.
SWEET. I was just thinking about this the other day.
It would be really nice to have this for adding columns with NULL values or default values.
We have a LOT of immutable data structures in some of our tables. Adding a column won’t matter to older rows because they will NEVER change.
A good example of this is log data. If your Apache is setup to log to MySQL the older rows don’t need to ever hold values for these columns.
Adding new columns should only apply to new records.
In the case of SELECTing older records without the columns the default would just be used.
It would be nice to see if removing the auto increment works with the ALTER syntax.
The ALTER COLUMN syntax does not note that this doesn’t resort in a full table copy:
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
“ALTER … SET DEFAULT or ALTER … DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value as described in Section 10.1.4, “Data Type Default Valuesâ€.”
The documentation should be updated.
Am I missing something? Maybe I should just file a bug for this.
It would be nice if other ALTER TABLE commands which didn’t strictly need a rebuild didn’t do one.
For example, setting DELAY_KEY_WRITE should not require a table rebuild. Likewise, a change of an ENUM column to add more ENUM values shouldn’t require one provided the layout of the rows won’t change.
Of course if you know this, then it’s possible to hack the change by doing the ALTER to a similar, empty table, then copying the .frm file (this requires the table to be closed with a FLUSH TABLES) – but it’s a hack.
Mark
Mark,
You’re absolutely right. ALTER TABLE is way too simplistic and a lot of stuff can be done without table rebuild or with less impact. Why would dropping index for example require full table rebuild ?
There are plans to improve ALTER TABLE in future versions but so far one often needs to resolve to hacks as such.
Few years ago I also wrote about hacks to speed things up for UNIQUE keys:
http://peter-zaitsev.livejournal.com/11772.html
Still not fixed as I remember 🙂
Nice post guys,
I was trying to do this trick to actually change the auto_incremenet and it didn’t work 🙁
Anyone knows how to do it without running “ALTER TABLENAME auto_increment = ….” because it takes about 6 hours on my table with 56,000,00 rows and 10 Indexes …
* 56,000,000 not 56,000,00 🙂
This is a huge pet peeve of mine… just renaming a column rebuild the entire table. Totally foobar, IMHO. Renaming a column should simply modify table metadata, not needing a full table rebuild.
Jay.
Don’t worry. It’s already fixed in MySQL 7.0…
🙂
Great info here. I am adding a nullable column to a table as we speak in a production environment. The table has over 43 million rows. Does anyone know a way to check the progress of this change – i.e. is it worth waiting or do I stop it and employ this change? I know it’s a little off topic, but help would be appreciated.
Thanks!
Re: “15. Travis” – For MyISAM tables, you should see files created in the MySQL data directory with names something like #sqlnnn.frm, #sqlnnn.MYD, #sqlnnn.MYI, which are the temporary files being created to eventually replace your actual table (nnn is a sequence number). The .MYD file (data file) is written first, and you can monitor the change in its size – it will probably end up being somewhat larger than the original .MYD file. Next the .MYI file is built, and you can monitor its size as it’s written, too. If there had been no deletes or updates to the original index, then the final size of #sqlnnn.MYI will probably be exactly the same as the original .MYI, so you’ll be able to judge how rapidly the rebuild is progressing.
Now if there was just some way for me to update the COMMENT field of a column without defining everything else and rebuilding the table.
ALTER TABLE accounts ALTER COLUMN cWork SET COMMENT ‘Foo’;
But the current structure is more like:
ALTER TABLE accounts ALTER COLUMN cWork cWork BIGINT(11) UNSIGNED NOT NULL COMMENT ‘Foo’;
And changing all that is just silly for something as minor as a comment change.
Hi guys
I tried this though instead changed a signed INT to an unsigned INT.
MySQL uses twos complement for negative ints and knowing that a table doesn’t contain any negative values, it was possible to just tell MySQL to use unsigned ints instead of signed without it needing to do anything.
On a test server, I ran::
(using world.sql)
mysql> FLUSH TABLE WITH READ LOCK;
mysql> ALTER TABLE City ENGINE=InnoDB;
mysql> CREATE TABLE Citytmp LIKE City;
mysql> ALTER TABLE Citytmp MODIFY ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
# cp /var/lib/mysql/world/Citytmp.frm /var/lib/mysql/world/City.frm
mysql> UNLOCK TABLES;
I then did it on a customers box that had 2^31 rows in Innodb with a primary key of signed int which was the initial problem. it worked great!
Does anyone know how to find out which commands will lead to a table rebuild?
More specifically: how do I add comments to tables and columns without rebuilding them?
Hi, This sounded interesting to me.
I managed to create a large table” heavy” to test.
rows =5000000.
Size >7GB
OS =windows xp sp2
mysql vesion =5.0.83
Followed the steps as said.
As expected indexes were there in the
show indexes from heavybut the unexpected “disabled” comment.I didn’t understand this behaviour!
They’re not at all disabled in the frm that I replaced.
Later I further tried::–
1. Check if indexes are used:-
mysql> explain select * from heavy where s_arid > 1000 and s_id>30000 limit 10;
+—-+————-+——-+——+—————+——+———+——+—–
—-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+—-+————-+——-+——+—————+——+———+——+—–
—-+————-+
| 1 | SIMPLE | heavy | ALL | NULL | NULL | NULL | NULL | 5000
000 | Using where |
+—-+————-+——-+——+—————+——+———+——+—–
—-+————-+
1 row in set (0.08 sec)
2.. If I need to do something extra??
mysql> analyze table heavy;
+————–+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————–+———+———-+———-+
| lyrics.heavy | analyze | status | OK |
+————–+———+———-+———-+
1 row in set (0.03 sec)
mysql> optimize table heavy;
+————–+———-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————–+———-+———-+———-+
| lyrics.heavy | optimize | status | OK |
+————–+———-+———-+———-+
1 row in set (0.06 sec)
But nothing seemed working!
Later I read:
http://forums.mysql.com/read.php?21,66550,67204#msg-67204
and
http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
Is it the case that my index size is increased beyond limits and hence they’re disabled?
I wonder what impact the renaming of files has on MySQL Replication?
@sk – no impact really. Well all SQL statements will be executed if you don’t SET SQL_LOG_BIN=0 but physical rename of .frm files won’t be replicated of course, so I’d recommend doing that separately on master and slaves.
thanks a lot for this idea. it’s good news)) I make it easily.
1. go to the MySQL Query Browser
2. on needed table you can click right mouse button and select “Copy CREATE statement to Clipboard”
3. Paste from clipboard to SQL Query Area
4. remove first line like “DROP TABLE IF EXISTS
dbname.tablename;”5. change
dbname.tablename->dbname.tablename26. change auto_increment value at the last line
7. execute
8. shutdown mysql server
9. delete tablename2.ibd
10. rename tablename2.frm -> tablename.frm
11. start mysql server
that’s all