Hi Team, so I am trying to perform upgrade of a Mysql db version 5.7 and above in effort to change the charset definition from latin1 to utf8mb4.
I am able to perform successfully pt-online-schema-chang on my localhost, but when I am running the same query against AWS instance with masterusername: as in admin not rdsadmin, I am getting the below-mentioned error.
Error creating triggers: 2022-12-01T20:25:02 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)
Question:
- Is there any way to force the percona-toolkit to not use super privilege
- As one the failure in the statement is
log_bin_trust_function_creators is set on a global level, it is blocked as a MySQL execution, and there is no alternative to run this as session-level query.
- Are there any recommended patterns to follow here, the DB size can vary from low 100GB to 10 TB with major upgrade to column definition to utf8mb4.
- The system doesn’t have a way to setup a replica node, and copying over the delta would be a bigger challenge.
The example query I am using successfully in my localhost is :
pt-online-schema-change D={dbName},t={tableNm},h={instanceEndpoint},u=admin --alter="MODIFY {columnNm} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" --alter-foreign-keys-method="auto" --set-vars="innodb_lock_wait_timeout=50" --ask-pass --execute
Appreciate the support.
Hi, you should change the log_bin_trust_function_creators to 1 in the parameter group. Then pt-osc will work.
1 Like
Hi @Ivan_Groenewold thanks for response, but I still have any success.
I tried a couple of approaches:
- set the property on the mysql.cnf level
- tried to set the value in
/etc/percona-toolkit/pt-online-schema-change.conf and /Users/.percona-toolkit.conf as
but every time, I try to run the query first it blocks the process itself.
Unknown option: a Unknown option: s Unknown option: k pt-online-schema-change alters a table's structure without blocking reads or 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.
can you please share an example of how I should set the parameter group in case I am getting
Error creating triggers: 2022-12-02T18:24:29 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)
@matthewb it be helpful If you can chime in as well.
appreciate the support.
1 Like
Hi, you need to change this at the instance level via the parameter group. Check Working with DB parameter groups - Amazon Aurora
1 Like
Sure, I would try to explore this avenue. Really appreciate the response.
1 Like
@Ivan_Groenewold
quick update: the recommendations worked like a charm,
question if you may help: how can the alter be made performant?
I do see parameters such as : --chunk-size and --max-load : is there a place where I can read how these affect an pt-online-schema change,
I am currently testing pt-online-schema-change on 8 million rows with around 8-9 columns being upgraded to ut8mb4 and the process is taking roughly 21 min, with the rds being an AWS instance and process being executed from ec2 instance in the same region.
I have tried --chunk-size= with value of 10000, 20000, 40000 and --max-load=“Threads_running=10/20/30/50”
I am trying to understand on a granular level of what function each variable plays
1 Like
Best place to check if the pt-osc doc site: pt-online-schema-change — Percona Toolkit Documentation
Keep in mind the goal is to make the alter have the least possible impact rather than have it run fast.
2 Likes
