Get A Sys Admin

A sysadmin blog about Linux and cloud IaaS

Get A Sys Admin - A sysadmin blog about Linux and cloud IaaS

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 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/

    October 31, 2013 at 11:59 am
  • 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

    September 30, 2013 at 5:46 pm
  • Marcelo Paris says:

    Muchas Gracias!!! thank you very much!!!

    September 24, 2013 at 6:15 pm
  • Adam Duffy says:

    you can edit the value directly from the website now.

    January 25, 2013 at 7:02 am
    • Pat Betts says:

      How can you set it directly from WebSite?
      THanks,

      February 22, 2013 at 9:22 pm
  • 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.

    May 30, 2012 at 1:38 am
  • 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.

    April 25, 2012 at 7:34 pm
    • Octavian says:

      Are you sure you used the right credentials? Where you get that error exactly?

      April 25, 2012 at 7:38 pm
      • Siva says:

        Yes, I copied multiple times, but the same issue is coming.

        April 25, 2012 at 9:04 pm
        • 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

          April 25, 2012 at 9:07 pm
          • 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

            April 25, 2012 at 9:08 pm
          • 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

            April 26, 2012 at 12:50 am
          • Octavian says:

            Glad that my post was useful to someone and you managed to fix the error:)

            April 26, 2012 at 2:56 am
  • Alejandro says:

    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

    January 17, 2012 at 12:15 am

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

*