I have an Aurora Mysql 5.6.10a db cluster with one master and one read, that I need to apply a migration to with near zero-downtime. I'm trying to get a comments field to accept emojis by updating the encoding to utf8mb4. Because I'm running this on millions of rows, this is an expensive update.
alter table tablename modify `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
Reading AWS documentation, I'm exploring doing the update on a Replica of my production db, and then using failover to swap the instances. However, I'm running into issues with the query crashing. The steps I've done are:
1) Update the cluster param group for the replica with the new encodings / collation. Waited for resync.
2) Updated the read_only flag to false on both the cluster paramgroup and and the param group, for the replica. Waited for the update to apply. I also threw in a reboot for good measure.
SELECT @@global.read_only; shows
0 for this value, so it's been updated.
Running the DDL query results in:
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
I've also received:
ERROR 2013 (HY000): Lost connection to MySQL server during query
In the back of my mind, it occurs to me that my entire approach is a terrible one. I'm now going to simply add a new column on the end of my table instead and update my app to point to the new column for a less risky approach.
But, since I've now spent 2 hours trying to simply run my DDL, I'm invested and I hate not understanding why I'm prevented to run the above. I'm guessing this is a limitation of Aurora / replication based on the nature of the query, but would like an authoritative answer to what's going on.
-- EDIT --
Ok, so apparently even adding a new column to my table still results in a table lock, so I'm back to square one. I didn't think that would happen. What's best practice here?