I want to 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 doesn't provide system access (SUPER privileges). If you turn on binary logging, then set log_bin_trust_function_creators to true in the custom database (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 parameter groups.
- Create a DB parameter group.
- Modify the custom DB parameter group, and then set the parameter: log_bin_trust_function_creators=1
- Choose Save Changes.
Note: Before using the DB parameter group with a DB instance, wait at least 5 minutes.
- In the navigation pane, choose Databases.
- Choose the DB instance that you want to associate with the DB parameter group.
- Choose Modify.
- Select the parameter group that you want to associate with the DB instance.
- 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're already using a custom parameter group, then complete only steps 2–3. The parameter log_bin_trust_function_creators is a dynamic parameter that doesn't require a DB reboot.
When you turn on automated backup for a MySQL DB instance, you also turn on binary logging. When creating a trigger, you might receive the following error message:
"ERROR 1419 (HY000): You don't 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, then modify the log_bin_trust_function_creators parameter to 1. This allows functions, procedures, and triggers on your DB instance.
Note: When you set log_bin_trust_function_creators=1, unsafe events might be written to the binary log. 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.
Related information
How can I resolve 1227 and definer errors when importing data to my Amazon RDS for MySQL DB instance using mysqldump?
Modifying parameters in a DB cluster parameter group