MySQL, Scheduled Events and Amazon RDS

This article explains how to enable SQL database scheduled events for the Amazon Relational Database Service (RDS). By default, Amazon does not allow one to create (in other words, to schedule) events. Events are SQL code intended to execute periodically or in future. The solution is quite similar my last article. One needs to provide the ‘event_scheduler’ parameter set to “ON” or “1”. Parameters are set by creating a new parameter group (or editing an existing one) and assigning this parameter group to the database instance.

  1. Open the RDS web console.
  2. Open the “Parameter Groups” tab.
  3. If necessary, 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 ‘event_scheduler’ and set its value to “ON” or “1”.
  6. Save the changes.
  7. Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
  8. If you have not yet assigned the Parameter Group to your database instance, select the Parameter Group and enable “Apply Immediately”.
  9. Click on “Continue” and confirm the changes.
  10. Dont forget: Again, open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Reboot”.

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…