Over a million developers have joined DZone.

Amazon RDS and Pt-Online-Schema-Change

DZone's Guide to

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.

· Database Zone
Free Resource

Navigating today's database scaling options can be a nightmare. Explore the compromises involved in both traditional and new architectures.

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.

Understand your options for deploying a database across multiple data centers - without the headache.

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.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}