EmergencyEMERGENCY? Get 24/7 Help Now!

Amazon RDS and pt-online-schema-change

 | July 1, 2016 |  Posted In: Cloud and MySQL, Cloud and NoSQL, MySQL

PREVIOUS POST
NEXT POST

Amazon RDS and pt-online-schema-changeIn 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:

The 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:

The following documentation page explains the reason for this message:

http://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

The bottom line is creating triggers on a server with binary logs enabled 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:

Run the tool again. This time, it will work.

After you’re done with the ALTER process, you can change the variable to “0” again.

UPDATE:

As Marc pointed out in the comments, in RDS the variable must be set via instance parameter group instead of SET GLOBAL.

PREVIOUS POST
NEXT POST
Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow.

7 Comments

  • @Miguel Angel Nieto, It is very good information to know while working on RDS. I have small doubt in here, Cant we revert the log_bin_trust_function_creators variable soon after creation of the triggers ? Is it really necessary to wait till end of pt-osc to complete the alter ?

  • I assume that if you have Replicas in RDS that you need to set log_bin_trust_function_creators=1 for all the replicas as well. However, what happens to a MultiAZ standby machine? When you change the parameter group, does that also change on a standby machine?

  • Not only does RDS require the change be made in a parameter group rather than via SET GLOBAL, but it requires the RDS DB instance to be rebooted for the parameter group to be applied.

    • Thanks Joey. As we are investigating RDS, are we saying that with EC2->MySql, I can simply set a global variable… but with RDS->MySql, we have to set the variable in the parameter group, and then *reboot* ??
      So thats a major killer of RDS then. We use ptosc a lot, especially as I am in the process of trimming tables/indexes and other work.

      Thanks for the input

Leave a Reply