Amazon RDS and Pt-Online-Schema-Change
Here's a workaround to get RDS to accept the pt-online-schema-change tool, which can throw an error based on permissions.
Join the DZone community and get the full member experience.
Join For FreeIn 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=actorThe tool runs an ALTER on the table "actor" from the database "sakila." The alter adds a column named "c1" of type "integer." In the background, the tool creates a new empty table similar to "actor" but with the new column already added. It then creates triggers on the original table to update the corresponding rows in the new table. After, it starts copying rows to the new table (this is the phase that takes the longest amount of time). When the copy is done, the tables are swapped, triggers removed and the old table dropped.
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.
Topics:
privileges,
process,
database,
logs,
amazon rds,
table,
alter
Published at DZone with permission of Miguel Angel Nieto. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments