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

Last updated: 2022-11-01

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

Resolution

Amazon RDS is a managed service, and it doesn't provide SYS access (SUPER privileges). If binary logging is turned on, then set log_bin_trust_function_creators to true in the custom DB parameter group for your DB instance.

If you create a DB instance without specifying a DB parameter group, then 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 custom DB parameter group and set the parameter: 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.

Note: The parameter group name changes immediately, but parameter group changes aren't applied until you reboot the instance without failover.

If you are already using a custom parameter group, then complete only steps 2-3. The parameter log_bin_trust_function_creators is a dynamic parameter so a DB reboot isn't required.

When automated backup is turned on for a MySQL DB instance, binary logging is also turned on. 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. This allows functions, procedures, and triggers on your DB instance.

Note: Unsafe events might be written to the binary log when you set log_bin_trust_function_creators=1. Binary logging is statement-based (binlog_format=STATEMENT).

For more details about the parameter log_bin_trust_function_creators, see log_bin_trust_function_creators and Stored program binary logging in the MySQL documentation.