How to change character set/collation on one column in a very large table

[eric.peterson]

I do not want to run the following in production. Take forever while locking the table.

ALTER TABLE <2.5B row table>
MODIFY COLUMN TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

SELECT @@aurora_version a, VERSION() v;
±-------±-------+
| a | v |
±-------±-------+
| 3.05.2 | 8.0.32 |
±-------±-------+

I’ve seen examples of changing everything, but I just want to do one column on one table. Is this doable? Do you have some guidance?

[eric.peterson]

Here is what I came up with. for a very large table, how will this impact production? what sort of locks might I expect (if any)?

I’ll try to make a copy of the table and run this to see what the timing is. But I’m concerned that the switchover might be a problem. If the ALTER takes more than 4 hours, then the outage window is closed and there will be user traffic on this busy table.

pt-online-schema-change
–host 127.0.0.1
–port 3366
–user root
–password xxx
–no-version-check
–execute
–sleep 0.1
–alter-foreign-keys-method drop_swap
–preserve-triggers
–statistics
–analyze-before-swap
–alter “MODIFY COLUMN |column| TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci”
D=|schema|,t=|table|

[matthewb]

Hey @eric.peterson,
Your ptosc command looks good. Since you are using Aurora, I would recommend using their blue/greeen deployment system. Create the B/G, then run ptosc against the non-active side. Once finished, do a B/G swap. I don’t recommend changing just 1 column. Change the entire table since B/G won’t impact main systems, even if it takes more than 4 hours.