How do I enable functions, procedures, and triggers for my Amazon RDS MySQL DB instance?

Last updated: 2020-11-20

How can I enable functions, procedures, and triggers for my Amazon Relational Database Service (Amazon RDS) MySQL DB instance?

Resolution

Amazon RDS is a managed service, and it doesn't provide SYS access (SUPER privileges). If binary logging is enabled on your MySQL DB instance, set the log_bin_trust_function_creators parameter to true in the custom DB parameter group that you create for your DB instance. For more information, see the Master user account privileges for Amazon RDS.

If you create a DB instance without specifying a DB parameter group, Amazon RDS creates a new default DB parameter group. For more information, see Working with DB parameter groups.

  1. Create a DB parameter group.
  2. Modify the DB parameter group as follows: log_bin_trust_function_creators=1
  3. Choose Save Changes.
    Note: Before using the DB parameter group with a DB instance, wait at least 5 minutes.
  4. From the navigation pane, choose Databases.
  5. Choose the DB instance that you want to associate with the DB parameter group.
  6. Choose Actions and choose Modify.
  7. Select the parameter group that you want to associate with the DB instance.
  8. Reboot the DB instance.
  9. Note: The parameter group name is changed immediately, but the parameter group changes aren't applied until you reboot the instance without failover.

When automated backup is enabled for a MySQL DB instance, it also enables binary logging. You might receive the following error message when creating a trigger: "ERROR 1419 (HY000): 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)"

If you receive this error, modify the log_bin_trust_function_creators parameter to 1 to allow functions, procedures, and triggers on your DB instance.