Amazon RDS and pt-online-schema-change

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:

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.


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

Share this post

Comments (8)

  • Anup

    Thanks .Good Information .pt-osc is always been an awesome tool.

    July 2, 2016 at 11:39 am
  • Marc Reilly (@marcyreilly)

    Just a note in case anyone gets stuck using SET GLOBAL.. in RDS. You cannot use ‘SET GLOBAL …’ in RDS, you must set log_bin_trust_function_creators via the RDS db instance parameter group 🙂

    July 5, 2016 at 10:05 am
  • Miguel Angel Nieto

    Thanks for the update Marc. I will update the blog post later on!

    July 5, 2016 at 10:12 am
  • vidyadhar

    @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 ?

    July 5, 2016 at 12:44 pm
  • Peter Colclough

    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?

    August 4, 2016 at 6:34 am
  • Joey

    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.

    October 24, 2016 at 11:11 pm
    • Peter Colclough

      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

      November 1, 2016 at 7:16 am
    • William W

      This only applies if you are using the RDS default parameter group. So worst case scenario this is a one-time issue that you will run into no matter what DB parameter you change. If you’re already using your own parameter group, the parameter type is dynamic so if you change it and then run the “SHOW VARIABLES LIKE ‘log_bin_trust_function_creators'” command it shows as enabled right away.

      April 4, 2018 at 4:12 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.