2

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"

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

That would be awesome if it worked, but still no joy with DEFINER = CURRENT_USER.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.