Explain the character set to move a table?

212    Asked by Aashishchaursiya in SQL Server , Asked on Mar 16, 2023

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 to 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 beginning 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 approximately 70 tables, we already tested this in one of our non-production environments and the entire script 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 contain lots of data, which are the ones that take lots of time to migrate.

If downtime is a concern, then use one of the online schema change tools for character set to move a table: pt-online-schema-change. You can perform most types of ALTER TABLE statements with nearly no downtime. See more of my notes about that in my answer here: https://dba.stackexchange.com/a/313406/2703 gh-ost. This uses a different approach, but has a similar benefit, that you can alter tables without downtime.



Your Answer

Interviews

Parent Categories