Amazon RDS and Pt-Online-Schema-Change

In this blog post, I discuss some of the insights needed when using Amazon RDS and pt-online-schema-change together.

The pt-online-schema-change tool runs DDL queries (ALTER) online so that the table is not locked for reads and writes. It is a commonly used tool by community users and customers. Using it on Amazon RDS requires knowing about some specific details. First, a high-level explanation of how the tool works.

This is an example from the documentation:

pt-online-schema-change--alter"ADD COLUMN c1 INT"D=sakila,t=actor

As we can see, it is a tool that uses the basic features of MySQL. You can run it on MySQL, Percona Server, MariaDB, Amazon RDS, and so on. But when using Amazon, there is a hidden issue: you don't have SUPER privileges. This means that if you try to run the tool on an RDS with binary logs enabled, you could get the following error:

DBD::mysql::db dofailed: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)[forStatement"CREATE TRIGGER `pt_osc_db_table_del` AFTER DELETE ON `db`.`table` FOR EACH ROW DELETE IGNORE FROM `db`.`_table_new` WHERE `db`.`_table_new`.`table_id` <=> OLD.`table_id` AND `db`.`_table_new`.`account_id` <=> OLD.`account_id`"]at/usr/bin/pt-online-schema-change line10583.

This documentation page explains the reason for this message.

The bottom line is creating triggers on a server with binary logs enabled. This requires a user with SUPER privileges (which is impossible in Amazon RDS). The error message specifies the workaround. We need to enable the variable log_bin_trust_function_creators. Enabling it is like saying to the server:

“I trust regular users' triggers and functions, and that they won't cause problems, so allow my users to create them.”

Since the database functionality won't change, it becomes a matter of trusting your users. log_bin_trust_function_creators is a global variable that can be changed dynamically:

mysql>SET GLOBAL log_bin_trust_function_creators=1;

Run the tool again. This time, it will work. After the ALTER is done, you can change the variable to 0 again.