Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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.

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.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}