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

3 分的閱讀內容
0

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 and don't specify a DB parameter group, then Amazon RDS creates a new default DB parameter group. For more information, see Working with parameter groups.

To turn on functions, procedures, and triggers for Amazon RDS for MySQL DB instances, complete the following steps:

  1. Create a DB parameter group.
  2. Modify the custom DB parameter group, and then set the parameter: log_bin_trust_function_creators=1
  3. Choose Save Changes.
    Note: Before you use the DB parameter group with a DB instance, wait at least 5 minutes.
  4. In the navigation pane, choose Databases.
  5. Choose the DB instance that you want to associate with the DB parameter group.
  6. 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 already use 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 you create 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. If you still get access denied errors after you set the parameter to 1, then see How can I resolve 1227 and definer errors when importing data to my Amazon RDS for MySQL DB instance using mysqldump?

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 on the MySQL website.

Related information

Modifying parameters in a DB cluster parameter group

7 評論

Hi, Just the reboot was not helping me! I stopped and started the Database, then the new parameter group was applied. Thank you.

回答 1 年前

I had to remove the DEFINER from the CREATE code. Changing the parameter and rebooting (even with a stop & start) did not help at all.

Taha
回答 9 個月前

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
管理員
回答 9 個月前

Hello, what about RDS for PostgreSQL, I can't find topics and the parameters related to it.

Hino
回答 7 個月前

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
管理員
回答 7 個月前

Running into this issue and setting the parameter log_bin_trust_function_creators to 1 doesn't seem to help. Rebooting the RDS didn't helped either.

回答 4 個月前

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
管理員
回答 4 個月前