RDS - MySQL User Defined Functions

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

User Defined Function (UDF)

When creating a User Defined Function (UDF) in RDS MySql, you may get a funky little error that makes you scratch your head a bit. ๐Ÿค”  ๐Ÿคจ.

The error will include the following text:

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

Diving in deeper and looking at the MySql support docs it says the following: 

The CREATE FUNCTION and INSERT statements are written to the binary log, so the replica executes them. Because the replica SQL thread has full privileges, it executes the dangerous statement. Thus, the function invocation has different effects on the source and replica and is not replication-safe.

To guard against this danger for servers that have binary logging enabled, stored function creators must have the SUPER privilege, in addition to the usual CREATE ROUTINE privilege that is required. Similarly, to use ALTER FUNCTION, you must have the SUPER privilege in addition to the ALTER ROUTINE privilege. Without the SUPER privilege, an error occurs:

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

If you do not want to require function creators to have the SUPER privilege (for example, if all users with the CREATE ROUTINE privilege on your system are experienced application developers), set the global log_bin_trust_function_creators system variable to 1. You can also set this variable at server startup. If binary logging is not enabled, log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

RDS is not going to give us SUPER user privileges ๐Ÿ˜ฟ

Since RDS is a managed service we're not going to get SUPER user privileges and we can't update the variables this way (SET GLOBAL ... etc) but luckily RDS does allow us to override parameters with customer parameter groups.

Custom Parameter Groups to the Rescue

Now we'll take a look at how we can essentially execute "SET GLOBAL log_bin_trust_function_creators = 1" within the confines of a managed instance.

When you create an RDS instance, it also creates default parameters and db options.  Those parameters and options are loaded with the default use case the majority of databases will require.  If we need to override them, we simply create a new set and apply our changes.

If you already have a custom parameter group, you can simply edit that one.  Pay special attention to the word "custom", if you see a parameter group named "default".something, it is the default one provided by RDS and you can't update it.

Create a new custom Parameter Group

Assuming you need to create a new one. Click on the "Create parameter group" button. Otherwise, skip to the Edit section.

Just remember you can't edit a default group.

Select the DB engine, that you are targeting.  The "type", which is just a DB Parameter Group in this case.  Enter a "Group Name" and "Description" that is meaningful to you and your applications designed for the database. 

Keep in mind you can bind more than one parameter to this group, so you should avoid naming it based on the parameter you are adding.  For example instead of naming it "user-defined-function-parameter-group", you should name it "prod-application-name-parameter-group"

For this example, I'm just going to stick with "test-mysql-parameter-group"


Edit the custom Parameter Group

Now you should see your new parameter group in the list of available parameter groups.  Select it (check the box) and open the actions, and then Edit

You will see a ton of options.  Feel free to look around. Once you are ready and if you haven't found the option, just search for it by the full or partial name "log_bin_trust_function_creators", "log_bin", "log_bin_trust", etc

  1. Search
  2. Select value of 1
  3. Save Changes

At this point, you've successfully created a new Parameter Group, which is prefilled with all the default values found in the "default" parameter group. Then you modified it to set the parameter of log_bin_trust_function_creators to 1.  This is similar to doing the following:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

However, the new parameter group isn't actually bound to anything yet.  Next, you'll need to update your instance to actually use that new parameter.

Navigate to the RDS instance you want to apply this to, click modify, scroll down to the "Additional configuration" section and expand it if needed.

You should see you're new DB parameter group.  If you don't see it:

  1. Make sure the engine + version number, you selected when making the parameter groups is the same engine + version in the instance you are wishing to update.  If not simply create a new one in the correct engine + version.
  2. Make sure you're in the same Region


Now Update your RDS Instance to use the new Parameter Group

Once you've changed the DB Parameter groups selection scroll to the bottom and click "Continue"

Apply Immediately or Wait?

Next, you'll be prompted to apply the changes immediately or wait until a maintenance window in which they will be updated. 

This decision will depend on if you you're ok with a few seconds to minutes of downtime.  If you can't have any downtime, then you'll want to wait to a maintenance window (which will still have some minor downtime, but hopefully you have the maintenance scheduled on a day/time with low traffic).

In my case, I'm going to apply it immediately, since I need the changes now in order to roll out my scripts to create the UDFs, well that and I'm in a "test" environment, which won't impact production.

During this time you should "Applying" in the Parameter group area on the "Configuration" tab within your instance.

Your database will go into a "modifying" state, which may or may not reboot during the process. 

Once it's complete, you'll need to check to see if a reboot is required.  If this is the case, then RDS didn't automatically do a reboot and you'll need to do this yourself.

If that's the case, just reboot when you're ready.

Tada!!  The End

Once it's done, you're all set and you can add user-defined-functions to the databases in your RDS instances that have the updated parameter group.