Search code examples
mysqlenumsrdsalteramazon-aurora

How do I add more members to my ENUM-type column in MySQL for a table size of more than 40 million rows?

I have a table size of 40 million rows and I wish to modify an enum column of a table an Aurora MySQL RDS Database V5.6.10 to add more. This table is a frequently updated one. Has anyone ever tried altering such tables before? If so, can you please elaborate on the experience?

Table Structure:

CREATE TABLE `tee_sizes` (
id bigint auto_increment,
customer_id bigint,
tee-size enum('small', 'large', 'x-large'),
created_at  timestamp NOT NULL default CURRENT_TIMESTAMP(),
updated_at  timestamp NOT NULL default CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
PRIMARY KEY(id)
) ENGINE=InnoDB AUTO_INCREMENT=36910751 DEFAULT CHARSET=utf8;

I wish to add 'xx-large' to the column tee-size.

Will there be a downtime while doing this?

Solution

  • MySQL 5.6 should allow InnoDB online DDL without anny downtime on that table and concurrent queries should still work on that table while altering. 

       ALTER TABLE tee_sizes MODIFY COLUMN `tee-size` enum('small', 'large', 'x-large', 'new-item'),
   ALGORITHM=INPLACE, LOCK=NONE;

    ALGORITHM=INPLACE, LOCK=NONE would force MySQL in executing in the requested level of concurrency without downtime.
    If your MySQL version does not execute then the requested level of concurrency was not available meaning ALGORITHM=INPLACE, LOCK=NONE needs to be changed.

    see demo

    Edited because of comment:

    Wait.. So, does this force any locks? ALGORITHM=INPLACE, LOCK=NONE would force MySQL in executing (if allowed) without downtime if your MySQL does not execute it means it can't be done using ALGORITHM=INPLACE, LOCK=NONE This statement is confusing.

    No it does not lock copy/paste from the manual

    You can control aspects of a DDL operation using the ALGORITHM and LOCK clauses of the ALTER TABLE statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. .. To avoid accidentally making the table unavailable for reads, writes, or both, specify a clause on the ALTER TABLE statement such as LOCK=NONE` (permit reads and writes) or LOCK=SHARED (permit reads). The operation halts immediately if the requested level of concurrency is not available.