Hacking to make ALTER TABLE online for certain changes

Hacking to make ALTER TABLE online for certain changes

PREVIOUS POST
NEXT POST

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:

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:

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?

PREVIOUS POST
NEXT POST

Share this post

Comments (26)

  • Xaprb Reply

    I think if you use the alternate ALTER TABLE… ALTER COLUMN syntax, you can change the default without rebuilding the table.

    October 29, 2007 at 7:15 am
  • Xaprb Reply

    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? 🙂

    October 29, 2007 at 7:19 am
  • Partha Dutta Reply

    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.

    October 29, 2007 at 7:55 am
  • Aurimas Reply

    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!

    October 29, 2007 at 8:03 am
  • mike Reply

    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.

    October 29, 2007 at 11:52 am
  • Kevin Burton Reply

    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.

    October 29, 2007 at 8:59 pm
  • Kevin Burton Reply

    It would be nice to see if removing the auto increment works with the ALTER syntax.

    October 29, 2007 at 9:01 pm
  • Kevin Burton Reply

    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.

    October 29, 2007 at 9:05 pm
  • Mark Robson Reply

    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

    October 30, 2007 at 2:37 am
  • peter Reply

    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 🙂

    October 30, 2007 at 4:07 pm
  • Pavel Reply

    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 …

    December 3, 2007 at 2:12 pm
  • pavel Reply

    * 56,000,000 not 56,000,00 🙂

    December 3, 2007 at 3:14 pm
  • Jay Pipes Reply

    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.

    January 9, 2008 at 12:36 pm
  • Kevin Burton Reply

    Jay.

    Don’t worry. It’s already fixed in MySQL 7.0…

    🙂

    January 9, 2008 at 1:12 pm
  • Travis Reply

    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!

    January 16, 2008 at 11:55 am
  • Gregert Johnson Reply

    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.

    February 4, 2008 at 1:06 pm
  • Nicholas Blasgen Reply

    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.

    February 21, 2008 at 1:37 am
  • Using MMM to ALTER huge tables | MySQL Performance Blog Reply

    […] months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes […]

    March 27, 2008 at 1:42 pm
  • Trent Hornibrook Reply

    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!

    May 22, 2008 at 4:44 am
  • Walter Heck Reply

    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?

    June 20, 2008 at 4:06 pm
  • Picking datatype for STATUS fields | MySQL Performance Blog Reply

    […] (ALTER TABLE) causes table rebuild which is not acceptable for many environments. It is possible to hack this around though I would not like to do this as a standard production practice. It is also worth to note ENUM […]

    August 10, 2008 at 5:16 pm
  • Faster MySQL failover with SELECT mirroring | MySQL Performance Blog Reply

    […] this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER TABLE, for […]

    February 1, 2009 at 7:13 am
  • kedar Reply

    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 heavy but 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?

    September 23, 2009 at 5:02 am
  • sk Reply

    I wonder what impact the renaming of files has on MySQL Replication?

    November 3, 2009 at 12:53 pm
  • Aurimas Reply

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

    November 3, 2009 at 2:05 pm
  • flet Reply

    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.tablename2
    6. 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

    December 8, 2009 at 3:02 pm

Leave a Reply