Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When trying to create a trigger in RDS, I get the following error:

ERROR 1419 (HY000): 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)

Is there a way to get SUPER privileges or to disable binary logging?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

I don't know if there is a way to get SUPER privileges, but there is a way to disable binary logging (thereby removing the need to have SUPER privileges for creating triggers). This is done by setting the variable log_bin_trust_create_function_creators to 1.

  1. Create a new RDS Parameter Group. Alternatively, you may modify your current parameter group (if its not the default parameter group) and skip steps 3-5.
  2. Change the value of log_bin_trust_create_function_creators to 1 for that parameter group
  3. Also change any parameters that have non-default values in the parameter group that your RDS instance is currently using. This is necessary because an RDS instance can only be associated with one parameter group
  4. Modify your RDS instance so that it uses your new parameter group
  5. Wait for the parameter group change to show in the event log
  6. Restard the RDS instance. log_bin_trust_create_function_creators is labeled as dynamic vairable, and therefore shouldn't require a restart, but I have always needed to restart the RDS instance for the change to take effect. Perhaps there is another way but I haven't found it.
share|improve this answer
    
Very good. +1. I thought you had to export and reimport. I am still evaluating RDS. Thanks for the lesson. –  RolandoMySQLDBA Aug 23 '12 at 17:18
    
That was very helpful @JustinY, issuing a reboot did the trick. Too bad that AWS forces the need for an RDS reboot for a dynamic variable change. –  Panagiotis M. Nov 25 '13 at 15:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.