I have an Aurora Mysql 5.6.10a db cluster with a teacher and a reading, to which I need to apply a migration with a downtime close to zero. I am trying to get a comment field to accept emojis by updating the encoding to utf8mb4. Because I am running this in millions of rows, this is an expensive update.
alter table tablename modify `comments` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
Reading the AWS documentation, I am exploring the update in a replica of my production database and then I use failover to exchange instances. However, I encounter problems with blocking the query. The steps I have done are:
1) Update the cluster parameter group for replication with the new encodings / collation. Expected by resynchronization.
2) The read_only flag was updated to false in both the paramgroup group and the param group, for replication. We wait for the update to apply. I also launched a reboot just in case.
SELECT @@global.read_only; sample
0 for this value, so it has been updated.
The execution of 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 have also received:
ERROR 2013 (HY000): Lost connection to MySQL server during query
In the back of my mind, it occurs to me that all my approach is terrible. Now I will simply add a new column at the end of my table and update my application to point to the new column for a less risky approach.
But, since I have now spent 2 hours trying to simply run my DDL, I am invested and hate not understanding why I cannot execute the above. I guess this is a limitation of Aurora / replication based on the nature of the query, but I would like an authorized response to what is happening.
– EDIT –
Ok, apparently, even adding a new column to my table still results in a table lock, so I go back to the starting point. I didn't think that would happen. What is the best practice here?