[amazon RDS - function, Stored procedure 만들기]
1. command_line_tools 설치 및 환경설정
첨부된 command_line_tools.mp4 동영상 보면서 따라하되
windows 환경변수에 EC2_REGION 값을 세팅 해야 함
; EC2_REGION 값은 DB Domain 정보에서 가져오거나
"AWS Management Console(https://console.aws.amazon.com/s3/home)" -> RDS -> DB Instances
에서 가져올 수 있음 (Zone: ap-northeast-1b 에서 마지막 b를 뺀 ap-northeast-1 만 가져와야 함)
[command_line_tools]
(아래 동영상은 그냥 참고만)
[Amazon_RDS_Signup_and_DB_Instance_Creation]
2. command_line_tools를 이용한 DB 세팅
http://blog.peterdelahunty.com/2010/10/stored-procedures-on-amazon-rds.html
(혹시 몰라서 위 내용 아래 첨부함)
에 따라서 작업함
1. command_line_tools 설치 및 환경설정
첨부된 command_line_tools.mp4 동영상 보면서 따라하되
windows 환경변수에 EC2_REGION 값을 세팅 해야 함
; EC2_REGION 값은 DB Domain 정보에서 가져오거나
"AWS Management Console(https://console.aws.amazon.com/s3/home)" -> RDS -> DB Instances
에서 가져올 수 있음 (Zone: ap-northeast-1b 에서 마지막 b를 뺀 ap-northeast-1 만 가져와야 함)
[command_line_tools]
(아래 동영상은 그냥 참고만)
[Amazon_RDS_Signup_and_DB_Instance_Creation]
2. command_line_tools를 이용한 DB 세팅
http://blog.peterdelahunty.com/2010/10/stored-procedures-on-amazon-rds.html
(혹시 몰라서 위 내용 아래 첨부함)
에 따라서 작업함
Stored procedures on Amazon RDS
Thursday, October 14, 2010
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
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
