Amazon RDS SUPER privileges

#1419 – 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

This error occurs sometimes on RDS instances when you try to use procedures. You will soon find out that grant super privilege for a user won’t work. So the only way to make things work is to set log_bin_trust_function_creators to 1.

RDS console available at https://console.aws.amazon.com/rds/ allows you to create a new group and modify its parameters. Log in to RDS console, go to “DB Parameters Groups” and click the “Create DB Parameter Group”. Set the following

  • DB Parameter Group Family: mysql5.1
  • DB Parameter Group Name: mygroup
  • Description: mygroup

Confirm by clicking “Yes, create” button.

Here comes the ugly part, since you cannot edit from the console the parameters (for the moment, I hope they are going to change that). You will need to log to your instance using SSH and download RDS cli from here: http://aws.amazon.com/developertools/2928?_encoding=UTF8&jiveRedirect=1

To do so right click on “Download” button and copy link location. In the SSH window use wget to download and unzip it:

wget "http://s3.amazonaws.com/rds-downloads/RDSCli.zip"
unzip RDSCli.zip

If you don’t have unzip you can quickly get it using “apt-get install unzip”(for ubuntu) or “yum install unzip”(for centos). Of course you will need root privileges.

After successfully unpacking the RDSCli cd to that directory and set a few variables. Following is an example on Ubuntu 10.04:

cd RDSCli-1.4.006
export AWS_RDS_HOME="/home/ubuntu/RDSCli-1.4.006"
export JAVA_HOME="/usr/lib/jvm/java-6-sun"
cd bin
./rds --help

If rds –help outputs no errors then you have set it correctly. Congrats. One more command:

./rds-modify-db-parameter-group mygroup --parameters="name=log_bin_trust_function_creators, value=on, method=immediate" --I="YOUR_AWS_ACCESS_KEY_ID" --S="YOUR_AWS_SECRET_ACCESS_KEY"

The AWS keys can be obtain from your AWS account Security Credentials->Access Credentials->Access Keys.

Go to AWS RDS console, “DB Instances”, select your instance and right click “Modify”. Set “DB Parameter group” to “mygroup” and check “Apply Immediately”. Confirm with “Yes, modify”.

You are done 🙂

Pushpinder Bagga

Its easy!

Open the RDS web console.
Open the “Parameter Groups” tab.
Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.
Select the just created Parameter Group and issue “Edit Parameters”.
Look for the parameter ‘log_bin_trust_function_creators’ and set its value to ’1′.
Save the changes.
Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
Select the just created Parameter Group and enable “Apply Immediately”.
Click on “Continue” and confirm the changes.
Again, open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.
Dont forget: Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Reboot”.

Via – http://techtavern.wordpress.com/2013/06/17/mysql-triggers-and-amazon-rds/

Deept Kohli

I did all the above and command was successful however when I try again to create trigger, I get below error:

21:10:11 Apply changes to Error 1419: 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)

Any other clue is appreciated. I can see log_bin_trust_function_creators to 1 in aws web console.

Deepti
http://ghewareunigps.in

adear11

A couple of errors specifically dealing with the problems people mentioned getting the message “Refused: The security token included in the request is invalid”

It should be:
-I “AWS_KEY_ID” -S “AWS_SECRET”

Notice no ‘=’. The ‘=’ is causing it to fail.

Siva

I have taken all the precautions, I am getting the below error.

Refused: The security token included in the request is invalid

Please help us as it is utmost urgent.

Siva

The error is ..

rds-modify-db-parameter-group: Refused: The security token included in the request is invalid
AWSRequestId:3cd7ef72-8efd-11e1-afef-99d1bb24cbe6

Siva

I used the command below.

./rds-modify-db-parameter-group bbymbuyersguide –parameters “name=log_bin_trust_function_creators, value=on, method=immediate” -I=”***” -S=”***”

(I am using latest cli i.e /RDSCli-1.6.001

Siva

Got it man.
Here are the steps followed.

export AWS_RDS_HOME=/home/user/RDSCli-1.6.001;
export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk;
export AWS_CREDENTIAL_FILE=~/.aws/credential-file;
$ ./rds-create-db-parameter-group mygroup -f MySQL5.5 -d “My new parameter mysql5.5 group”
$ ./rds-modify-db-parameter-group mygroup –parameters “name=log_bin_trust_function_creators, value=1, method=immediate”

NOte: I tried with MindTerm SSH console.
Followed the steps http://getasysadmin.com/2011/06/amazon-rds-super-privileges/#comment-653

Alejandro

Hello,

I noticed that when running the command there’s an error on this section:

–parameters=”name=log

Instead it should be:

-parameters “name=log…”

Just an FYI.

Alejandro

Your email address will not be published. Required fields are marked *