super priviliege not set for master user in aws rds mysql

2
votes

I have created an AWS RDS instance, I have created my master user with master password, and it is working/connecting fine.

But when I am going to create a function on that instance, it shows me the following error: 

ERROR 1418: This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled  
(you might want to use the less safe log_bin_trust_function_creator variable).

In my instance the variable log_bin_trust_function_creators shows OFF, and if I try to change the variable using SET GLOBAL log_bin_trust_function_creators = 1;, it gives me another error "Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

3
mysqlamazon-web-servicesamazon-rds

3 Answers

2
votes

Set log_bin_trust_function_creators = 1 for Parameter group of the RDS instance.

Note: Default Parameter-Group is not editable. Create a new Parameter-Group and assign it to the RDS instance by modifying it from UI (AWS Admin Console) OR maybe using commands

DELIMITER $$
CREATE DEFINER=`DB_USERNAME_HERE`@`%` FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120)) RETURNS decimal(12,8)
READS SQL DATA
BEGIN
   DECLARE distance DECIMAL(12,8);
   /*Business logic goes here for the function*/
   RETURN distance;
END $$
DELIMITER ;

Here, you have to replace DB_USERNAME_HERE with you RDS database username and function names according to you need.

Important thing is: DEFINER=`DB_USERNAME_HERE`@`%`

This was the problem I was facing after setting log_bin_trust_function_creators = 1 in parameter group. And it worked like a charm.

1
votes

A better way is to apply your own parameter group, with log_bin_trust_function_creators set to true. (its false by default)

0
votes

This happens when you try to create a procedure/function/view with a DEFINER that is not the current user.

To solve this remove or update the DEFINER clause.