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 🙂

Comments

comments

16 replies
  1. Pushpinder Bagga
    Pushpinder Bagga says:

    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/

  2. Deept Kohli
    Deept Kohli says:

    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

  3. adear11
    adear11 says:

    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.

  4. Siva
    Siva says:

    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
          Siva says:

          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
            Siva says:

            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
            Siva says:

            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

Trackbacks & Pingbacks

  1. […] provide direct edit to this, so you need to install the RDS CLI tools to enable this parameter. See http://getasysadmin.com/2011/06/amazon-rds-super-privileges/ for more details (ubuntu […]

  2. […] log_bin_trust_function_creators = 1 to get around this. I tried that using these instructions: http://getasysadmin.com/2011/06/amazon-rds-super-privileges/ (and then restarting the DB server for good measure), but no […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply