Mike Warren Mike Warren - 2 years ago 146
SQL Question

Creating stored functions and triggers without SUPER privilege

All I am trying to do is create some stored functions and triggers that act on some tables. I find that, for either of those, I get MySQL 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)

Attempt with triggerI checked the
table of
, and find that my username is
and my hostname is
. I tried setting DEFINER as
, but it doesn't work. I go back to
table, try setting it that way (a
column exists), and it still doesn't work (it literally is an error to do so).

I don't know how to use that
variable, let alone why it's less safe. How can I create triggers and stored functions ?

Answer Source

After pondering over Michael-sqlbot 's answer, I decided upon changing that log_bin_trust_function_creators variable to 1. Until now, after looking on Google, and then AWS manual, I didn't know how.

According to one of their manuals, there exists things called parameter groups (damn, AWS has a lot of stuff) which can be created, and RDS instances use. A parameter group is created by default, and from my attempt, the parameters in it are not editable (including the parameter I needed to edit). Long story short, I clicked on Parameter Groups from the RDS Dashboard menu. Then, I clicked the Create Parameter Group button, and then went through to creating it. Once that was finished, I went to Details pageThe icon to click type in the parameter, click Edit Parameters, and select 1 for the value.

Finally, when all was said and done, I go back to Instances (from the RDS dashboard), right click the instance, hit Modify, and change the instance's selected Parameter Group to the one I just created. I wait for that to take effect, reboot the instance, and wait for the all clear.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download