Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:

ALTER TABLE `table` MODIFY `column` ENUM('value_one','value_two','First value','Second value');
UPDATE `table` SET `column`='First Value' WHERE `column`='value_one';
UPDATE `table` SET `column`='Second Value' WHERE `column`='value_two';
ALTER TABLE `table` MODIFY `column` ENUM('First value','Second value');

Is there a more efficient way to do this, E.G. a way to accomplish this with a single ALTER TABLE statement?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

The follolwing technique I am about to show you will require guts of steel.

Given the following criteria

  • datadir is /var/lib/mysql
  • table is mydb.mytb
  • enum column called is called enum_col
  • engine is MyISAM

Here is a death-defying crack at it:

  1. CREATE TABLE mydb.mybt LIKE mydb.mytb;

  2. ALTER TABLE mydb.mybt MODIFY enum_col ENUM('First value','Second value');

  3. SET wait_timeout=86400; SET interactive_timeout=86400;

  4. FLUSH TABLES WITH READ LOCK;

  5. In a separate OS/SSH Session, swap the .frm files

    • $ mv /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/myxx.frm
    • $ mv /var/lib/mysql/mydb/mybt.frm /var/lib/mysql/mydb/mytb.frm
    • $ mv /var/lib/mysql/mydb/myxx.frm /var/lib/mysql/mydb/mybt.frm
  6. UNLOCK TABLES;

  7. DROP TABLE mydb.mybt;

That's it !!!

CAVEAT : I CANNOT TAKE CREDIT FOR THIS !

This technique comes from "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

Give it a try ! (Please let us know how it turned out)

UPDATE 2011-10-05 17:49 EDT

If the table is MyISAM and you have enough space in production and a straight-up downtime window, try this:

  1. service mysql restart --skip-networking

  2. In a separate OS/SSH Session, make a copy of the table

    • cp /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/mytbplay.frm
    • cp /var/lib/mysql/mydb/mytb.MYD /var/lib/mysql/mydb/mytbplay.MYD
    • cp /var/lib/mysql/mydb/mytb.MYI /var/lib/mysql/mydb/mytbplay.MYI

INFORMATION_SCHEMA.TABLES will automatically detect the presence of the new table called mydb.mytbplay.

  1. Perform the guts-of-steel algorithm on mydb.mytbplay

  2. You test the integrity of mydb.mytbplay

If you are satisfied

  1. ALTER TABLE mydb.mytb RENAME mydb.mytb_backup;

  2. ALTER TABLE mydb.mytbplay RENAME mydb.mytb;

  3. service mysql restart

Give it a try!

share|improve this answer
    
+1 for an ingenious answer! I will not, however, be giving this a try and letting you know how it turned out as the question refers to an upgrade script for all my customer's live, production databases ;-) I may, however, try this on a development environment just for fun. But with that warning I'd never run this in production! –  Josh Oct 5 '11 at 21:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.