RDS - MySQL User Defined Functions

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: 

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.

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

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.