Thursday, October 14, 2010

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

14 comments:

Anonymous said...

This was a fantastic tutorial on how to allow stored procs on RDS... great job!

thoughtcrimes said...

Thank you SO much! this saved me so much time and aggravation. Very well explained and complete.

Sean said...

Great info.
One note: if you are doing it through windows you do not need to set the EC2_REGION environment variable. Actually if you do, you may not be able to connect to the RDS server with the CLI tools.

Suraj said...

Excellent tutorial....I was trying to use triggers and this one helped me. I followed the steps in Windows.
As per Sean windows does not require EC2_REGION but I set it and it worked for me. Thanks

cloud_admin said...
This comment has been removed by the author.
cloud_admin said...

Here is my 2 cents and applies to anyone who has difficulty in porting a mysqldump created using the options --routines. The issue is again the DEFINER=`root`@* in the sql file.

To resolve it, either do the following

A) Edit .sql file and remove all reference of /*!50020 DEFINER=`root`@`%`*/. This means delete that part everywhere.

or

B) Edit .sql file and replace all reference of /*!50020 DEFINER=`root`@`%`*/ by /*!50020 DEFINER=`RDS_Current_User`@`%`*/

Where RDS_Current_User is the exact RDS user name you created while creating the RDS instance. Example if your current RDS user name is xunil, then replace
/*!50020 DEFINER=`root`@`%`*/ by /*!50020 DEFINER=`xunil`@`%`*/

Hope this is of use to someone

Ian said...

Thank God for Cloud Admin's much easier solution, at least for a small number of procedures to alter.

Ian said...

Hmmm... it seems to apply only to procedures. Not functions or triggers.

Anonymous said...

Excellent post. Saved me about 3 hours googling time.

"value=on" did not seem to work for me though. I used "value=1" which did the trick:


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

Sitebase said...

Worked like a charm!
Thanks so much man!

Bystander said...

You can also do this through the AWS web console without the tricky RDS CLI, not sure if this is recent though.

Go to your RDS management console->DB Parameter Groups. Create a new set. Search for log_bin_trust_function_creators and set it to 1. Go to your RDS instance and modify it to use these parameters.

mba dissertation topic said...

This was a actually excellent post. Your writing really very good, Taking a few minutes and actual effort to create a actually good blog post. but what can I say I hesitate a complete lot and don't manage to get almost anything done,

Martin Lewis said...

That's great. Are some basic features like disk re-sizing and multi-az on the roadmap for mssql? To get more info just check out plagiarism detect.

Mobile Applications Development said...

This is impressive and also great information. I personally liked going through your solid points on this topic. Many thanks for creating such excellent material. This is excellent.