How to Fix MySQL Error 1418: Binary Logging and Stored Functions/Procedures

MySQL raises error 1418 (ER_BINLOG_UNSAFE_ROUTINE) when you attempt to create or alter a stored function or procedure that MySQL considers unsafe for binary logging. The full error message looks like:

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)

The SQLSTATE is HY000 (general error). This error only occurs when the log_bin system variable is ON.

What This Error Means

When binary logging is enabled — which is required for point-in-time recovery and replication — MySQL must be able to reproduce the exact same data changes on replica servers or during recovery. Stored functions (created with CREATE FUNCTION) are called within SQL statements, meaning a non-deterministic function could produce different results on a replica than it did on the source, silently corrupting replicated data.

To prevent this, MySQL requires that every stored function explicitly declare one of the following characteristics when binary logging is active:

• DETERMINISTIC — the function always returns the same result given the same input arguments, with no side effects.
• NO SQL — the function contains no SQL statements at all.
• READS SQL DATA — the function reads data but does not modify it, and contains no non-deterministic built-ins.

If none of these are declared, MySQL refuses to create the function unless the log_bin_trust_function_creators system variable is set to 1, which bypasses the check entirely and trusts the developer to ensure safety.

Note that this restriction applies specifically to stored functions (which return a value and can be embedded in queries). Stored procedures called via CALL are subject to a similar but less strict set of rules; procedures that modify data are logged using statement-based or row-based binary logging depending on the binlog_format setting, and may trigger related warnings rather than a hard error.

Common Causes

1. Creating a stored function without any characteristic declaration. When you write CREATE FUNCTION ... BEGIN ... END without specifying DETERMINISTIC, NO SQL, or READS SQL DATA, MySQL defaults to NOT DETERMINISTIC CONTAINS SQL, which is blocked when log_bin=ON.

2. Migrating functions from a development server where binary logging is disabled. Dev environments often run without log_bin, so functions work fine locally but fail when deployed to a production server with replication or binary logging enabled.

3. Importing a mysqldump that includes stored functions. Dumps taken from servers with log_bin_trust_function_creators=1 may not include the characteristic declarations, causing the import to fail on a stricter server.

4. Using non-deterministic built-ins and declaring the function DETERMINISTIC anyway. MySQL does not validate that the declared characteristic is accurate. A function using NOW(), RAND(), or UUID() can be declared DETERMINISTIC, which will make MySQL accept it — but this can cause replication inconsistencies.

How to Fix ER_BINLOG_UNSAFE_ROUTINE

1. Add the correct characteristic declaration to the function. This is the safest and recommended fix. Analyze what the function actually does and declare accordingly:

   -- Function that computes a value purely from its arguments
   CREATE FUNCTION calculate_tax(price DECIMAL(10,2))
   RETURNS DECIMAL(10,2)
   DETERMINISTIC
   NO SQL
   BEGIN
     RETURN price * 0.08;
   END;
   
   -- Function that reads from a table but does not modify data
   CREATE FUNCTION get_customer_tier(cust_id INT)
   RETURNS VARCHAR(20)
   READS SQL DATA
   BEGIN
     DECLARE tier VARCHAR(20);
     SELECT loyalty_tier INTO tier FROM customers WHERE id = cust_id;
     RETURN tier;
   END;
   

2. Enable log_bin_trust_function_creators if you accept the risk. This global variable bypasses the safety check. Use this only if you are certain your functions are safe for replication, or if binary logging is enabled for backup purposes only (no replicas):

   -- Session-level (does not persist across restarts)
   SET GLOBAL log_bin_trust_function_creators = 1;
   

   To make it persistent, add it to your my.cnf or my.ini:

   [mysqld]
   log_bin_trust_function_creators = 1
   

   On MySQL 8.0+ with replicas, be cautious: enabling this on the source without enabling it on replicas can cause CREATE FUNCTION statements in the binary log to fail during replay on the replica.

3. Convert the stored function to a stored procedure if a return value is not required. Stored procedures called via CALL are logged differently and do not have the same hard restriction. If the logic doesn't need to be embedded in a SELECT statement, a procedure may be more appropriate.

4. When importing a mysqldump, set log_bin_trust_function_creators temporarily:

   mysql -u root -p \
     --init-command="SET GLOBAL log_bin_trust_function_creators=1;" \
     dbname < dump.sql
   

   Remember to reset it afterward if you do not want it permanently enabled.

Additional Information

• This behavior has been present since MySQL 5.0 when binary logging and the SUPER privilege requirement for stored function creation were introduced. MySQL 8.0 tightened replication safety rules further.
• In MySQL 8.0, the SUPER privilege required to create stored functions with binary logging was replaced by the SYSTEM_VARIABLES_ADMIN + BINLOG_ADMIN privileges in some contexts.
• Related SQLSTATE HY000 errors around replication safety include error 1419 (ER_BINLOG_CREATE_ROUTINE_NEED_SUPER), which is raised when a non-SUPER user tries to create a stored routine when log_bin_trust_function_creators=0.
• ORMs and migration tools (e.g., Flyway, Liquibase) that manage stored routines will surface this as a migration failure with the raw MySQL error message. The fix must be applied to the SQL definition in the migration script itself.
• This error has no impact on stored routines created before binary logging was enabled; it only applies at creation or ALTER time.

Frequently Asked Questions

Why does this error appear on production but not on my local machine?

Your local development environment almost certainly has binary logging disabled (log_bin=OFF), which is common for local MySQL installs. Production servers typically have log_bin=ON for replication or point-in-time recovery, which activates the safety check. Always test stored function creation against a MySQL instance with log_bin=ON before deploying.

Is it safe to just set log_bin_trust_function_creators=1 and move on?

It depends on your setup. If you are running replicas, enabling this variable means MySQL will trust you not to create non-deterministic functions that could diverge on replicas. If a function uses NOW(), RAND(), UUID(), or reads session variables and is declared DETERMINISTIC, replication will silently produce different data on the replica. If binary logging is only used for backups with no replicas involved, the practical risk is lower, but fixing the function declaration is still the cleaner solution.

Can I use NOW() or RAND() inside a stored function?

Yes, but you cannot declare such a function DETERMINISTIC. You must use NOT DETERMINISTIC (or omit any characteristic declaration and rely on the default). With log_bin=ON and log_bin_trust_function_creators=0, this means the function cannot be created at all. Either enable log_bin_trust_function_creators or redesign the function to pass the timestamp or random seed as an argument, which would allow the function itself to be DETERMINISTIC.

Does this error affect stored procedures as well?

The hard block (error 1418) applies specifically to stored functions. Stored procedures are subject to different rules depending on binlog_format. With statement-based logging, procedures that modify data are generally logged as the CALL statement. However, procedures can trigger other replication-safety warnings or errors in certain configurations, particularly when they use non-deterministic functions inside row-based replication contexts.