0

We're in the process of migrating to a new character set for several tables in our database. Some tables are currently using utf8/utf8mb3 character set, which is deprecated now and we want move on using utf8mb4 on those tables. We figured out that we don't only need to change the character set for the tables, we also need to convert our data to work with this new character set, for that we are using the following ALTER command:

ALTER TABLE mySchema.TableToConverty
convert to CHARACTER SET utf8mb4 , COLLATE utf8mb4_bin

Note: We had to disable @foreing_key_checks at the beggining of the script and then turn it back on at the end.

This works fine as mysql docs suggests, but the issue is the service degradation/downtime we might have in our production environment, which is not desired.

We have to apply this ALTER statement to 70 tables approximately, we already tested this in one of our non-production environments and the entire scripts takes around 5 hours to complete, which will cause the same time of service degradation on our production environment. I was wondering if there's a way to make these ALTER statements not last that long, improve them in some way, specially for tables that contains lots of data, which are the ones that takes lots of time to migrate. Thanks.

2
  • A side note... There are advantages and disadvantages to _bin collation. Be aware that comparisons different even with must letter case will be treated as not equal. Is that OK?
    – Rick James
    Oct 12, 2022 at 22:22
  • @RickJames Apologizes, I did not understand your warning. Can you elaborate a bit more on this?
    – Retrosec6
    Oct 13, 2022 at 0:06

1 Answer 1

1

If downtime is a concern, then use one of the online schema change tools:

2
  • Do they handle FOREIGN KEYs gracefully?
    – Rick James
    Oct 12, 2022 at 22:21
  • Thanks for the suggestion, will take a look at them and test them. Will post any update about it.
    – Retrosec6
    Oct 13, 2022 at 0:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.