SUPER privilege not defined for master user in Amazon MySQL RDS

Question

I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on my local PC. When, I am going to create function on that instance, it show me 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_creators variable)

At my instance, my variable (log_bin_trust_function_creators) shows OFF. now when I go to change with 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

I don't know how to solve this error.

Can anybody help???

Answer 1

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired Options. Then, you must do this to import the new options:

• Create a Custom DB Parameter Group (call it MySettings)
• Download RDS CLI and setup a config file with your AWS Credentials
• Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
• Modify using DB Parameter Option List MySettings
• Restart the MySQL RDS Instance

Using an API for updating single variable and doing a compulsory restart of the RDS instance to implement the change? That's quite a painful process to tweek any one option.

If you want to scale up MySQL, please use EC2. Then, you can tweek my.cnf to your liking like you have always done and have been used to.

Answer 2

You can try to change log_bin_trust_function_creators; however, there is an alternative approach that seems more appropriate when you consider the meaning of that variable:

It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log.

A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation.

All that option does is assume that you know what you are doing, without making you assert that you do by using one of the three characteristics in your CREATE statement... but if you don't properly declare the function, you may miss out on potential optimizations.

misdeclaring a routine might affect results or affect performance

Taken together, this implies that the most correct approach is to declare your stored functions with DETERMINISTIC or READS SQL DATA or NO SQL as appropriate, and, if these do not correctly describe your function's behavior, then your function still may result in unsafe statements being written to the binary log, because these options are also "trusted:"

Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results.

---

Curious aside: astute observers will note that I omitted something from the documentation's description:

If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege.

Since nobody gets SUPER in RDS, and assuming this is not an error in the official documentation, this seems like it must be an AWS customization of MySQL behavior.