Amazon RDS Creating Functions in MySQL

Had a problem creating functions in MySQL with the standard user for an RDS instance on Amazon.

For those who don’t know, Amazon RDS is a sweet place to host a remote database, kind of expensive but worth it if you are using a distributed system. Anyway…

The problem is when you try to run something like:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
  RETURNS TEXT
  DETERMINISTIC
  READS SQL DATA
  LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples
BEGIN
  RETURN CONCAT('Hello ', addressee);
END;
$$
DELIMITER ;

SELECT hello_world('Earth');

You get an error message saying something like:

“ERROR 1419: You do not have the SUPER privilege and binary logging is enabled”

This is because the sql server is not accepting incoming create requests from a SUPER

To fix this is not so straight forward. Basically, the solution is here. You need to fix the problem by relaxing the “trust function creators” pool in the parameters.

1. Go to your amazon RDS portal and select “Parameter Groups” from the left hand menu
2. Create a new group and name it whatever you want, also put the description to whatever you want
3. Now select that group and click “Edit Parameters”
4. Look for the parameter called “log_bin_trust_function_creators” and set it to 1 in the drop down menu



5. Now you can go back to your instances and select the instance that you want to apply the new settings to.
6. In your “instance actions” select to modify



7. Now set your parameters group to the new group and make sure you apply changes immediately



8. Press “continue” and wait for everything to be updated (or say “pending reboot”), then reboot your rds instance

That should be it, you should be able to run the above function on MySQL 5.5+ no prob bob