Stored procedures on Amazon RDS

Been a long time since i blogged but need to document this pain in the arse.

So for a new project i need to create a mysql function (same for stored proc) on amazon RDS.

When i tried to install it i got this error:

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

So to get them to install you need to set that database parameter to ON. However to do that is not so simple.

Lucky for you people i have found out how to do it and here are the steps:

1) Install the RDS CLI tools. I installed these on my EC2 instance.

Download from here:
http://developer.amazonwebservices.com/connect/entry.jspa?categoryID=294&externalID=2928

They are java based and so you need have Java running. Plus they need your amazon key and secret key. Once you have them installed following the readme do the following:

Important!! make sure you set the AWS region you are working in.
Eg export EC2_REGION=ap-southeast-1

2) create a new parameter group

rds-create-db-parameter-group peters-params -f mysql5.1 -d "peters params"

3) modify the log_bin_trust_function_creators to be set to ON

rds-modify-db-parameter-group peters-params --parameters="name=log_bin_trust_function_creators, value=on, method=immediate"

4) change your running db instance to use the new param group

rds-modify-db-instance petersdbinstance --db-parameter-group-name=peters-params

5) restart the instance

rds-reboot-db-instance petersdbinstance


That will allow you to create a function or stored procedure:

Some point about the function / stored proc:

You need to specify the DETERMINISTIC stuff and you need to add a DEFINER=CURRENT_USER


DELIMITER $$
DROP FUNCTION IF EXISTS `sayhello`$$
CREATE DEFINER=CURRENT_USER FUNCTION `sayhello`(param1 VARCHAR(120))
RETURNS VARCHAR(120)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
RETURN CONCAT('Hello, ',s,'!');

END$$
DELIMITER ;


This works for me hope it helps