# Upgrade an existing mysql db on aws aurora from latin-1 to utf-8 **URL:** \ **Category:** MySQL & MariaDB\ **Tags:** mysql, percona\ **Created:** [November 30, 2022, 2:33am UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826 "2022-11-30T02:33:59Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![amahajan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/amahajan/32/8509_2.png) [@amahajan](https://forums.percona.com/u/amahajan)\ **Post date:** [November 30, 2022, 2:33am UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/1 "2022-11-30T02:33:59Z")
Hi Team, I am working on a project to identify how an existing MySQL db in TB size with default charset as latin-1 can be upgraded to a default charset of `utf-8.` the problem statement : 1. existing database with data in roughly 1-9tb. 2. requires an upgrade for the charset is mainly on a table or column level. I am able to perform such operations in multi-threaded DB connections with a volatile table or new tables, but I am trying to identify an efficient way. I did read through GHO-st GitHub solution, but I am trying to find a better solution using percona-toolkit. any recommendations are much appreciated. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 30, 2022, 2:59am UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/2 "2022-11-30T02:59:07Z")
Don’t change to utf8. You want utf8mb4. This is the new default. Use pt-online-schema-change to manage this. ---
**Author:** ![amahajan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/amahajan/32/8509_2.png) [@amahajan](https://forums.percona.com/u/amahajan)\ **Post date:** [November 30, 2022, 5:23pm UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/3 "2022-11-30T17:23:15Z")
thanks a lot for the input, are there code samples available in the documentation, usually stack-overflow is a good place to see snippets that can be modified to meet the use case. sorry, 2-day old rookie trying to learn impressive tools and functions offered by percona ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 30, 2022, 6:26pm UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/4 "2022-11-30T18:26:49Z")
It translates pretty much 1-1, but yes, there should be some examples in our docs ``` ALTER TABLE bar.foo ADD COLUMN name VARCHAR(20) NOT NULL DEFAULT 'Bob'; pt-online-schema-change --alter "ADD COLUMN name VARCHAR(20) NOT NULL DEFAULT 'Bob'" -d bar -t foo --execute ``` ---
**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\ **Post date:** [December 1, 2022, 11:40am UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/5 "2022-12-01T11:40:22Z")
Another approach could be to create a second Aurora cluster and link it to the existing one via Mysql replication. Then you could: 1. temporarily stop replication 2. convert all tables on the replica side using direct ALTER 3. start replication again 4. When clusters are in sync again, switch your applications to the new cluster ---
**Author:** ![amahajan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/amahajan/32/8509_2.png) [@amahajan](https://forums.percona.com/u/amahajan)\ **Post date:** [December 1, 2022, 4:45pm UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/6 "2022-12-01T16:45:38Z")
Thanks a ton for your input. **The high-level problem is:** - The project doesn’t support replication of DB in its current state. - even if we are able to add functionality to replication, we would have to stop the replication while altering/upgrading the schema. - additionally, if there is a data update happening to the true source, we would need to move/copy over the delta to an upgraded/updated replica in an effort to keep the system synchronous. - moreover, the database sizes that I am trying to work on are between range of 500gb to 10TB dependent on the use case for the project. ---
**Author:** ![amahajan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/amahajan/32/8509_2.png) [@amahajan](https://forums.percona.com/u/amahajan)\ **Post date:** [December 1, 2022, 11:31pm UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/7 "2022-12-01T23:31:08Z")
@matthewb thanks for all the help, - current db version is 5.7.28 I believe. - I have tried a good amount of -ask-help command to get a good source on the documentation plus the commands. I am still running into an issue where running alter query is through an error back 🙂 `pt-online-schema-change -d {schemaNm} --user {root/admin} -t {tableNm} --host localhost --chunk-size 2000 --alter "ALTER TABLE {schemaNm}.{tableNm} MODIFY {columnNm1} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY {columnNm2} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;" --execute` the goal is to be able to update latin1 **MySQL 5.7.28** to support column level utf8 on specific columns in a table. I have tried the process with and without -d or -t but nothing has helped keep getting errors: **pt-online-schema-change alters a table’s structure without blocking reads or** ```auto writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully. For more details, please use the --help option, or try 'perldoc /usr/local/Cellar/percona-toolkit/3.4.0/libexec/bin/pt-online-schema-change' for complete documentation. ``` would appreciate if you could offer guidance. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [December 2, 2022, 1:04am UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/8 "2022-12-02T01:04:24Z")
Hmm. It’s asking for a DSN. Instead of using -d and -t, do it this way: ``` h=localhost,D=sakila,t=actor ``` ---
**Author:** ![amahajan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/amahajan/32/8509_2.png) [@amahajan](https://forums.percona.com/u/amahajan)\ **Post date:** [December 3, 2022, 1:33am UTC](https://forums.percona.com/t/upgrade-an-existing-mysql-db-on-aws-aurora-from-latin-1-to-utf-8/18826/9 "2022-12-03T01:33:56Z")
HI @matthewb@Ivan_Groenewold : Thanks for all the help. I have been using the following process: `pt-online-schema-change D={dbName},t={tableNm},h={awsEndpoint},u={userNm} --alter="MODIFY {columnNm} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" --alter-foreign-keys-method="auto" --set-vars="log_bin_trust_function_creators=1" --ask-pass --execute` **every time I try this I am getting a bunch of errors:** - Error setting log\_bin\_trust\_function\_creators: DBD::mysql::db do failed: Variable ‘log\_bin\_trust\_function\_creators’ is a GLOBAL variable and should be set with SET GLOBAL [for Statement “SET SESSION log\_bin\_trust\_function\_creators=1”], line 1. The current value for log\_bin\_trust\_function\_creators is OFF. If the variable is read-only (not dynamic), specify --set-vars log\_bin\_trust\_function\_creators=OFF to avoid this warning, else manually set the variable and restart MySQL. - error creating triggers: 2022-12-02T19:27:40 DBD::mysql::db do failed: You do not have the SUPER privilege and binary logging is enabled (you _might_ want to use the less safe log\_bin\_trust\_function\_creators variable) [for Statement "CREATE TRIGGER `pt_osc_{schemaNm}_{tableNm}_del` AFTER DELETE ON I have tried a bunch of options, but have not been able to get any success. it be really helpful if you can offer some recommendations.