0

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.

3) 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.