Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Creating a trigger or function on RDS fails because binary logging is on

ErrorERROR 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)

Short answer

RDS never gives you the SUPER privilege, and MySQL asks for it when binary logging is on and you create a trigger or stored function. Set log_bin_trust_function_creators to true in your own DB parameter group, attach the group, and reboot. If the error is 1418 instead, add DETERMINISTIC, NO SQL or READS SQL DATA to the function.

  • Applies to Amazon RDS for MySQL 8.0 and 8.4, MySQL 8.4 manual
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find out which parameter group the instance uses and whether the setting is already on. If the group name starts with default., you cannot edit it and need a custom one.

    aws rds describe-db-instances --db-instance-identifier mydb \
      --query "DBInstances[0].DBParameterGroups"
    mysql -h mydb.xxxx.us-east-1.rds.amazonaws.com -u admin -p \
      -e "SELECT @@log_bin_trust_function_creators"
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 1419 when running CREATE TRIGGER or CREATE FUNCTIONBinary logging is on and log_bin_trust_function_creators is offFix
    2. 2ERROR 1418 names DETERMINISTIC, NO SQL and READS SQL DATAThe function does not declare how it behaves (error 1418)Fix

What does the error look like?

  • 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)
  • ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

Cause 1 of 2

Binary logging is on and log_bin_trust_function_creators is off

1Check

With binary logging enabled MySQL requires a privilege that RDS restricts for every user, the master user included. The variable defaults to 0. The check from the first step returns 0.

SELECT @@log_bin_trust_function_creators;

2Fix

Create a DB parameter group (or edit your own), set log_bin_trust_function_creators to true, attach the group to the instance, and reboot it so the change applies. The variable is the less safe option: it tells MySQL to trust the people who create functions, which matters when you have replicas.

aws rds create-db-parameter-group --db-parameter-group-name allow-triggers \
  --db-parameter-group-family mysql8.0 --description "allow triggers"
aws rds modify-db-parameter-group --db-parameter-group-name allow-triggers \
  --parameters "ParameterName=log_bin_trust_function_creators,ParameterValue=true,ApplyMethod=pending-reboot"
aws rds modify-db-instance --db-instance-identifier mydb \
  --db-parameter-group-name allow-triggers --apply-immediately
aws rds reboot-db-instance --db-instance-identifier mydb

3Verify

Connect again, read the variable and create the object.

SELECT @@log_bin_trust_function_creators;

Confirmed by docs.aws.amazon.com

Did this fix it?

Cause 2 of 2

The function does not declare how it behaves (error 1418)

1Check

When binary logging is on, a stored function must be declared with at least one of DETERMINISTIC, NO SQL or READS SQL DATA. The default is CONTAINS SQL and NOT DETERMINISTIC, which MySQL refuses. MySQL does not check that the declaration is true, so declare only what the body really does.

SHOW CREATE FUNCTION my_function\G

2Fix

Add the right characteristic to the CREATE FUNCTION statement. A function that only reads data and always returns the same result for the same input takes both.

CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;

3Verify

The statement now runs without the 1418 error.

SELECT f1(3);

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create a custom parameter group when you create the instance, so nothing depends on the default group.
  • Declare DETERMINISTIC, NO SQL or READS SQL DATA on every stored function.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.