Amazon RDS : Trigger Error – You might want to use the less safe log_bin_trust_function_creators variable

While I’m trying to execute triggers to a RDS Mysql database I got the following errors

ERROR 1419 (HY000) at line #: 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).

I’m using the SUPER root account which will create at the instance RDS instance launch time. Googling lead me to have a solution which stating that we need to set the variable “log_bin_trust_function_creators = 1″ in mysql server parameter to enable this privilege.

You can set it by either adding it in my.cnf or runtime by executing “SET GLOBAL log_bin_trust_function_creators = 1;” command.

How do I set Mysql variables on a RDS hosted server.

A. Create new MySQL Parameter Group

1. Open the RDS web console.

2. Open the “Parameter Groups” tab.
3. Create a new Parameter Group. On the dialog, select the proper MySQL Engine version, and give a new name to the Parameter Group and confirm it.

4. Select the newly created Parameter Group and click on “Edit Parameters”.

5. Search the variable “log_bin_trust_function_creators” from the search field shown inside it
6. It will show the variable and showing the available parameter value in next column 0 or 1.
7. Set the ‘log_bin_trust_function_creators’ value to ’1′ and save the Parameter Group.

B. Modify the Instance Parameter group

1. Select the instance and and hit the Modify button to change the MySQL server config. details

2. You can see new parameter name is listed under “Parameter Group” combo box
3. Click on Save button and reboot the instance to take effect the new changes.

This will not cause any data loose and outage of service. But you have to do it off peak hours after taking the backup using mysqldump utility from command line for safety.

Once you have done the above steps, reboot the instance to take effect the changes and you could able to execute the trigger and procedures easily from command line.

Cheers 🙂