ALTER TABLE on a large table with an indexed column

Question

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.

score 15 · Accepted Answer · answered Jul 11 '11 at 15:31

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 !!!

score 4 · Answer 2 · edited May 23 '17 at 11:33

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)

score 4 · Answer 3 · edited Mar 01 '18 at 11:20

4

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.

edited Mar 01 '18 at 11:20

Hassaan

105
1
5

answered Jul 11 '11 at 20:52

randomx

3,944
4
31
44

ALTER TABLE on a large table with an indexed column

3 Answers3

Linked