Configuring Amazon’s RDS to allow stored procedures on a Mac

Published on 19/07/2012

It’s likely that if you’ve tried to create a stored procedure on a vanilla Amazon RDS instance you will have seen the following error message:

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)

Unfortunately setting the log_bin_trust_function_creators isn’t possible via Amazon’s AWS console. Attached below are the steps I had to take to get stored procedures running:

  1. Grab Amazon’s RDS Command Line Tools, rename the unzipped directory to RDSCli and copy it somewhere it won’t be disturbed (I chose the root of my hard drive).
  2. Next, edit (or create) your ~/.bash_profile, like so:

    nano ~/.bash_profile

    And enter the following (note that you should change your EC2_REGION to something more applicable):

    export AWS_RDS_HOME=/RDSCli
    export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home
    export PATH=$PATH:${AWS_RDS_HOME}/bin
    export AWS_CREDENTIAL_FILE=${AWS_RDS_HOME}/awscredentials
    export EC2_REGION=us-east-1
  3. Next you’ll want to rename the credential-file-path.template file in your RDSCli directory and call it something like awscredentials. Open it up in your text editor of choice and follow the commented instructions to include your AWS credentials.
  4. You should now be ready to use the RDS command line tools. Run the following commands to test everything has been configured correctly:

    echo $JAVA_HOME
  5. If everything’s gone according to plan, this should echo out the Java path you entered in your .bash_profile. Next we’ll create a new parameter group and turn the log_bin_trust_function_creators flag to ON.

    rds-create-db-parameter-group admin-params -f mysql5.5 -d "Administrative Params"
    rds-modify-db-parameter-group admin-params --parameters="name=log_bin_trust_function_creators, value=on, method=immediate"
  6. Once that’s done, we tell our database instance to run with our new parameter group (note that you’ll need to change dbinstance to match your instance name):

    rds-modify-db-instance dbinstance --db-parameter-group-name=admin-params
  7. Finally, tell your database instance to reboot:

    rds-reboot-db-instance dbinstance

And that’s it! Note that you may need to add the following flag to any SQL-based CREATE functions:

DEFINER=CURRENT_USER