MySQL, Triggers and Amazon RDS
June 17, 2013 27 Comments
This article explains how to enable SQL database triggers for the Amazon Relational Database Service (RDS).
If one attempts to create a SQL database trigger, then the database server will complain a confusing and misleading message like: “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)“.
It happens that Amazon decided to disable all user defined SQL code, as binary procedures could compromise the RDS architecture. And for this purpose, Amazon blocked granting the missing ‘SUPER’ privileges to any database user except the ‘rdsadmin’ user owned by Amazon itself.
As a workaround, one may set the mysterious parameter ‘log_bin_trust_function_creators’ to 1, that prevents the database from complaining against simple and inoffensive triggers. Though, setting this parameter requires following steps:
- Open the RDS web console.
- Open the “Parameter Groups” tab.
- Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.
- Select the just created Parameter Group and issue “Edit Parameters”.
- Look for the parameter ‘log_bin_trust_function_creators’ and set its value to ‘1’.
- Save the changes.
- Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
- Select the just created Parameter Group and enable “Apply Immediately”.
- Click on “Continue” and confirm the changes.
- Again, open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
- Dont forget: Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Reboot”.
That’s all! But I think that Amazon could made it easier…
Pingback: MySQL, Scheduled Events and Amazon RDS | Daniel Ferbers Technical Tavern
thank you thank you thank you!
I can finally use triggers with RDS :)
agreed, thank you so much! saved me some research and fiddling.
Pingback: MySQL, Triggers and Amazon RDS | SystemPandit
thanks for saving a day!!!!
Thank you! Many hours looking for this simple answer! Congratulations !
Wow this helped me so much. Thanks man
Thanks so much, it really helps
#11, the reboot step, isn’t required for this parameter since it has the “Apply Type” of “dynamic.” Just tested. Might help those with busy prod instances.
to me it shows : ( pending-reboot ) , so I guess it’s required
BOOM! Thanks
I have followed all the steps and rebooted the instance and while creating a trigger I’m getting an error as “Access Denied: You need atleast one of the super previliges for this operation”
Pingback: Confluence: CtcDocs
Nice one!
This change indeed applies without reboot.
After this actions I have same error as Murali:
ERROR 1227 (42000) at line 2106: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Try to resupply the privileges to that user and try again.
it is not allowing that, saying access denied
Oh my god! You saved my life. THANK YOU!!!
Try to grant the user you are facing the issue with privileges again. then retry
Working
My Dear Friend!
Thank you So Much for your help.
Regards:
Ranjeeth Kumar Pathi
Great, this worked exactly as expected. Thanks for the simplest solution I can think of.
thanks
Thanks for the post..It helped us …
Thank you! This was killing me.
Thank you!! I’m going to use trigger with RDS
Pingback: Still need (at least one of) the SUPER privilege with log_bin_trust_function_creators=1 - Tutorial Guruji