MySQL, Triggers and Amazon RDS

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:
  1. Open the RDS web console.
  2. Open the “Parameter Groups” tab.
  3. Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.
  4. Select the just created Parameter Group and issue “Edit Parameters”.
  5. Look for the parameter ‘log_bin_trust_function_creators’ and set its value to ‘1’.
  6. Save the changes.
  7. Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
  8. Select the just created Parameter Group and enable “Apply Immediately”.
  9. Click on “Continue” and confirm the changes.
  10. Again, open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
  11. 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…

17 Responses to MySQL, Triggers and Amazon RDS

  1. Pingback: MySQL, Scheduled Events and Amazon RDS | Daniel Ferbers Technical Tavern

  2. Ophir says:

    thank you thank you thank you!
    I can finally use triggers with RDS :)

  3. Erica Davis says:

    agreed, thank you so much! saved me some research and fiddling.

  4. Pingback: MySQL, Triggers and Amazon RDS | SystemPandit

  5. thanks for saving a day!!!!

  6. Ale Rapchan says:

    Thank you! Many hours looking for this simple answer! Congratulations !

  7. Dirk Coetsee says:

    Wow this helped me so much. Thanks man

  8. Charles McMatt says:

    Thanks so much, it really helps

  9. Alex Bernier says:

    #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.

  10. Shaheen Ghiassy says:

    BOOM! Thanks

  11. Murali says:

    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”

  12. Pingback: Confluence: CtcDocs

  13. ve says:

    Nice one!
    This change indeed applies without reboot.

  14. Alex says:

    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

  15. sumarsky says:

    Oh my god! You saved my life. THANK YOU!!!

  16. Justin Case says:

    Try to grant the user you are facing the issue with privileges again. then retry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: