Add fields to a MySQL table without doing an ALTER TABLE

I have a database table that was created about 2 years ago and has been filling up quite quickly over the years. These days, it’s massive. Our database dumps are 68gb uncompressed, and 60gb of that is this table. It’s used quite regularly, as it contains all of the error reports we receive, but to call it “unwieldy” is an understatement.

I was content to just let sleeping dogs lie, but alas — one of my devs needs a couple extra fields added to the table for more data and sorting and whatnot. If this wasn’t a 60gb table in our production database, I’d happily run an ALTER TABLE and call it a day. (In fact, I attempted to do this — and then the site went down because the whole db was locked. oops)

Instead, I discovered a better way to add fields while retaining both uptime and data (!). MySQL’s CREATE TABLE command actually has a lot of interesting functionality that allows me to do this:

CREATE TABLE errors2 (
  keywords VARCHAR(255), 
  errorid VARCHAR(64), 
  stacktrace TEXT, 
  is_silent BOOL, 
  PRIMARY KEY (id), 
  KEY playerid (playerid,datecreate), 
  KEY datecreate (datecreate), 
  KEY hidden (hidden,datecreate), 
  KEY hidden_debug (hidden,is_debug,datecreate)
SELECT * from errors; 

What this CREATE TABLE statement does is create a new table with 5 explicitly-specified fields (keywords, errorid, stacktrace, is_silent, and id). Four of these are what I wanted to add; ‘id’ exists in the original table, but I specify it here because I need to make it AUTO_INCREMENT (as this is a table setting, not a bit of data or schema that can be copied). Additional keys are specified verbatim from a SHOW CREATE TABLE errors (the original table), as is the AUTO_INCREMENT value.

After specifying my table creation variables, I perform a SELECT on the original table. MySQL is smart enough to know that if I’m SELECTing during a CREATE TABLE, I probably want any applicable table schema copied as well, so it does exactly that — copies over any columns missing from the schema I specified in my CREATE statement. Even better, because the various keys were specified, the indexes get copied over as well.

The result? An exact copy of the original table — with four additional fields added. All that’s left is to clean up:

DROP TABLE errors;
RENAME TABLE errors2 TO errors;

And that, as they say, is that.

One Comments

  • crankysysadmin

    December 23, 2013

    Table rotation is a fine tradition, but it doesn’t work as well with a table that is being written to a bunch. Also, DROP TABLE can take a while. To do what you’re describing without losing any writes, you could do this:

    CREATE TABLE errors2 LIKE errors;
    ALTER TABLE errors2 (whatever changes you want to make);
    RENAME TABLE errors TO errors_old, errors2 TO errors;

    Then select and insert everything from errors_old to errors.

    Another tool that works well is Percona’s free pt_online_schema_change, which allows you to do things like back off on the copying of data while your database is loaded (those select-inserts arent free; they go on top of your normal writes).

Leave a Reply