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.

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about 90-120 minutes to complete, so I can really only do that on a Saturday or Sunday night to avoid affecting the users of the database. If possible I would like to do this modification prior to then.

The column is also indexed, which I presume will make the ALTER TABLE slower, because it has to rebuild the index after modifying the column length.

The web app is set up in a MySQL replication environment (26 slaves and one master). I recall once reading somewhere that one method is to first perform the ALTER TABLE on each slave (minimising impact on users), then do this on the Master, but won't that then try to replicate the ALTER TABLE command to the slaves?

So my question is: what is the best way for me to modify this table with minimum disruption to my users?

Edit: the table is InnoDB.

share|improve this question
    
adding that tinyint column meant actually adding a column with a default value? Because doing that on a huge table could take long time.. –  Marian Jul 11 '11 at 14:04
    
Thank you, kind sir !!! A +1 for your question since many people have to deal with this issue of doing ALTER TABLE commands in sharded replication architectures (such as FaceBook : gigaom.com/cloud/…) –  RolandoMySQLDBA Jul 14 '11 at 14:37

3 Answers 3

up vote 6 down vote accepted

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;
  • Script 1 can be executed on any slave that do not have binary logs enabled
  • Script 2 can be executed on any slave that does have binary logs enabled
  • Script 3 can be executed on a master or anywhere else

Give it a Try !!!

share|improve this answer
2  
One problem I see is if the table contains an 'auto_increment' field. I did a basic test and was surprised to see that CREATE TABLE .. LIKE doesn't copy the auto_increment value to the new table –  Derek Downey Jul 11 '11 at 16:42
1  
@DTest : Good Catch and Great Comment !!!. I believe you can retrieve the auto_increment value from information_schema.tables column AUTO_INCREMENT. If the table does not have an AUTO_INCREMENT field, then the AUTO_INCREMENT column in information_schema.tables will be NULL. Otherwise, it will contain the AUTO_INCREMENT value needed. I guess it can be scripted to extract that non-NULL value and do ALTER TABLE WorkingSet AUTO_INCREMENT=<somenumber>; just before renaming the temp table back to WorkingSet. –  RolandoMySQLDBA Jul 11 '11 at 16:58
    
+1 Nice detail. Also good to know about the LIKE command. –  StanleyJohns Jul 11 '11 at 19:28

My guess from the documentation would be that merely increasing the length constraint on a varchar would not cause the same trouble as adding a column:

For some operations, an in-place ALTER TABLE is possible that does not require a temporary table:

But that seems to be contradicted in the comments on this SO question.

EDIT

At least on 5.0, I think I can confirm that increasing the length does indeed require a temporary table (or some other equally expensive operation):

testbed:

create table my_table (id int auto_increment primary key, varchar_val varchar(10));
insert into my_table (varchar_val)
select 'HELLO'
from (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s1,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s2,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s3,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s4,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s5,
     (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) s6;

result:

alter table my_table modify varchar_val varchar(20);
Query OK, 1000000 rows affected (2.91 sec)

alter table my_table add int_val int;
Query OK, 1000000 rows affected (2.86 sec)
share|improve this answer
    
Modifying varchar field size involves checking that you aren't exceeding the new size. For increases in size this check MAY be optimized away. –  BillThor Jul 12 '11 at 18:37

I thought I'd mention that since the ENGINE=INNODB... If you have foreign key constraints, you cannot alter and rename w/o your constraints pointing to the old table (now renamed). You'll have to alter afterward or drop the constraints for the duration.

share|improve this answer
    
Shabang !!! That is so true. In that case, one can only be stuck with doing alter table on the original table. At the very least, you may have to play games with disabling constraints, before the ALTER TABLE takes place. +1 for that very good catch !!! –  RolandoMySQLDBA Jul 14 '11 at 14:41

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.