Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Adding a value for Enum Column for Mysql - will it involve a downtime?

I want to update an Enum in RDS Mysql and add a value to an column. I have huge data existing in the same table which I want to update. I assume that while updating the schema of the table, the table gets locked which clearly involves a downtime during which my service will go down. My MysqlServer version is 5.5.40.

But as I am adding a value to the Enum, is the RDS Mysql server smart enough to detect that it doesn't need to scan existing rows and update the schema quickly in the time of executing a simple query without scanning values of all existing rows of the Enum column?

like image 916
Amit Avatar asked Mar 16 '23 18:03


1 Answers

On Mysql 5.5.46, I added a new ENUM value to an existing list:

enum ('a','b','c','d')


enum ('a','b','c','d','e')

ALTER TABLE CHANGE [column] took less than 1 second for 500k rows.

This seems to be the bug : https://bugs.mysql.com/bug.php?id=45567 and it seems to be fixed in 5.1.40, 5.5.0 and 6.0.14

On Mysql 5.6.33, I tried

enum ('a','b','c','d')


enum ('a','b','c','e','d')

and ALTER TABLE CHANGE [column] did not update the existing d's to e's as described here: https://dba.stackexchange.com/questions/11484/is-it-possible-to-change-enum-lists/11485#11485

I cannot find the version where this bug was fixed.

like image 62
site80443 Avatar answered Mar 18 '23 06:03