Creating a trigger or function on RDS fails because binary logging is on
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)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.
Fix it fast
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"2Match what you see, then jump to its 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 mydb3Verify
Connect again, read the variable and create the object.
SELECT @@log_bin_trust_function_creators;Confirmed by docs.aws.amazon.com
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\G2Fix
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
A dump restore into RDS fails on a DEFINER clause and asks for SUPER
Access denied; you need (at least one of) the SUPER privilege(s) for this DEFINER operation
The dump names another account as DEFINER on a view, trigger, routine or event.
2 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix