MySQL 1418: function has none of DETERMINISTIC, NO SQL…

What it means

The binary log records changes so replicas and point-in-time recovery can replay them. A stored function that gives different results each time it runs, or that changes data, could replay differently, so when binary logging is on (it is by default on MySQL 8) the server asks each new function to say what it does:

DETERMINISTIC: the same arguments always give the same result.
NO SQL: it contains no SQL statements.
READS SQL DATA: it reads tables but doesn’t change them.

Error 1418 means the CREATE FUNCTION gave none of these. It has a companion, error 1419, for accounts without the SUPER privilege: while log_bin_trust_function_creators is off, they can’t create stored functions or triggers at all, whatever the declaration:

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)

The server doesn’t check that the declaration is true; it takes your word for it.

Common causes

A function written without characteristics, often copied from an example, or created on a server without binary logging (MariaDB’s default) and then moved to MySQL.
Restoring a dump with functions into a server that has binary logging on.
An application or migration account without SUPER, which gets 1419 even after the function is declared correctly. On hosted databases (Amazon RDS, Aurora and others) no account has SUPER.

How to fix it

Declare what the function does

Add the characteristic that’s true, after RETURNS and before the body:

CREATE FUNCTION add_tax(amount decimal(10,2)) RETURNS decimal(10,2)
DETERMINISTIC
RETURN amount * 1.2;

CREATE FUNCTION email_count() RETURNS int
READS SQL DATA
RETURN (SELECT COUNT(*) FROM emails);

A function that reads tables is READS SQL DATA, not DETERMINISTIC: the same call returns different results as the table changes. Don’t mark something deterministic that uses NOW(), RAND() or UUID().

If you get 1419: trust function creators

An administrator sets the variable for the running server and in its configuration:

SET GLOBAL log_bin_trust_function_creators = 1;

[mysqld]
log_bin_trust_function_creators = 1

On a hosted database, set it in the provider’s parameter settings (on Amazon RDS, the DB parameter group). It lifts both the SUPER requirement and the declaration check. MySQL 8.4 warns that the variable is deprecated, but it still works:

Warning	1287	'@@log_bin_trust_function_creators' is deprecated and will be removed in a future release.

Or create the function as an administrator

An account with SUPER only needs the right declaration. Mind the DEFINER: the function then runs with that account’s privileges unless you declare SQL SECURITY INVOKER.

Reproduce it

On the shared MySQL 8.4.11 server (log_bin 1, log_bin_trust_function_creators 0), as seo_err_app, an account with all privileges on its own database but no SUPER:

CREATE FUNCTION add_tax(amount decimal(10,2)) RETURNS decimal(10,2)
RETURN amount * 1.2;

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

With DETERMINISTIC added, the same account got 1419, as it did for READS SQL DATA, NO SQL and a CREATE TRIGGER. A function declared MODIFIES SQL DATA got 1418 again.

In a temporary MySQL 8.4.11 container (removed afterwards), root got the same 1418 for the first statement and created the DETERMINISTIC version (SELECT add_tax(10) returned 12.00). After SET GLOBAL log_bin_trust_function_creators = 1, which gave the 1287 warning above, an account without SUPER created a function with no characteristics at all.

The shared MariaDB 11.4.13 server has binary logging off (log_bin 0), so every one of these statements worked there. A temporary MariaDB 11.4.13 container started with --log-bin behaved like MySQL: 1418 for the undeclared function, for root too, and 1419 for a declared function or a trigger from an account without SUPER.

In Inlet

When a CREATE FUNCTION or CREATE TRIGGER fails, Inlet shows the server’s error and links to this page. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement.

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled