When creating stored functions in MySQL with binary logging enabled, you must explicitly declare how the function interacts with data using DETERMINISTIC, NO SQL, or READS SQL DATA. Without one of these declarations, MySQL raises ERROR 1418 to ensure safe replication.
MySQL uses binary logging to record all changes to the database for replication and recovery purposes. When binary logging is enabled (the default in MySQL 8.0+), stored functions must declare their characteristics to ensure they can be safely replicated across servers. The three main characteristics control how MySQL logs and replicates your function: - **DETERMINISTIC**: The function always returns the same result for the same input parameters - **NO SQL**: The function contains no SQL statements at all - **READS SQL DATA**: The function reads data from tables but never modifies it Without one of these declarations, MySQL cannot determine whether the function is safe for replication, so it rejects the CREATE FUNCTION statement with ERROR 1418.
Before declaring the function characteristics, you need to understand what it does:
1. Does it use ANY SQL statements? If no → use NO SQL
2. Does it modify data (INSERT, UPDATE, DELETE)? If yes → it cannot be DETERMINISTIC; use READS SQL DATA with appropriate caution
3. Does it only read data without modifying? → use READS SQL DATA
4. Does it use date/time functions like NOW(), UUID(), RAND()? → it's NOT DETERMINISTIC
5. Does it perform pure calculation without any SQL? → use NO SQL
Review your function code carefully:
CREATE FUNCTION my_function(param INT)
RETURNS INT
BEGIN
-- Review: Does this function read data?
-- Does it call NOW(), RAND(), or other non-deterministic functions?
-- Does it modify any data?
RETURN param * 2;
END;Once you've identified what your function does, add the appropriate characteristic:
For pure calculation functions (no SQL access):
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
RETURN amount * 0.08;
END;For functions that read data only:
CREATE FUNCTION get_user_count()
RETURNS INT
READS SQL DATA
BEGIN
DECLARE count INT;
SELECT COUNT(*) INTO count FROM users;
RETURN count;
END;For functions without any SQL:
CREATE FUNCTION format_name(first_name VARCHAR(100), last_name VARCHAR(100))
RETURNS VARCHAR(200)
DETERMINISTIC
NO SQL
BEGIN
RETURN CONCAT(first_name, ' ', last_name);
END;Important: Only declare DETERMINISTIC if your function truly is deterministic (same input always produces same output). Functions using NOW(), RAND(), UUID(), or other non-deterministic operations should NOT be marked DETERMINISTIC.
Drop and recreate the function with the proper declaration, or modify it if your MySQL version supports ALTER FUNCTION:
DROP FUNCTION IF EXISTS my_function;
CREATE FUNCTION my_function(param INT)
RETURNS INT
READS SQL DATA
BEGIN
-- Your function body here
RETURN param;
END;For migration files or seed scripts, ensure all CREATE FUNCTION statements include the required characteristics from the start.
After creating the function with proper declarations, verify it works:
-- Test the function
SELECT my_function(100);
-- For reading data functions, ensure results are correct
SELECT * FROM users WHERE id = my_function(1);
-- Check function details
SHOW CREATE FUNCTION my_function;### When to Use Each Characteristic
DETERMINISTIC: Use only when your function truly produces identical output for identical input, with no side effects. Never use this for functions that call NOW(), UUID(), RAND(), or any function that returns different values on each call.
NO SQL: The safest declaration if your function doesn't use any SQL statements at all. This guarantees no data access or modification.
READS SQL DATA: Use this for functions that query tables but never modify data. This is commonly used for lookup functions, aggregations, and other read-only operations.
MODIFIES SQL DATA: If your function modifies data (INSERT, UPDATE, DELETE), declare MODIFIES SQL DATA. However, this limits use of the function in certain contexts (e.g., generated columns, indexes). Such functions should be carefully reviewed for replication safety.
### Understanding Binary Logging and Replication
When binary logging is enabled, MySQL records all data modifications to a binary log file. When that log is replicated to another server, the replica must execute the same functions and operations. If a function is non-deterministic and marked as DETERMINISTIC, it could produce different results on the replica, causing data inconsistency.
This is why MySQL enforces these declarations - to prevent silent data corruption across replicated environments.
### The log_bin_trust_function_creators Variable
You may see advice to set SET GLOBAL log_bin_trust_function_creators = 1; to bypass this error. While this works temporarily, it's not recommended for production systems because:
1. It disables the safety check entirely
2. Your function could still cause replication issues if it's actually non-deterministic
3. Other developers or deployments may not have this setting enabled
4. It creates inconsistency between environments
Always prefer properly declaring your function's characteristics over disabling the check.
### Functions vs Procedures and Triggers
This error only affects stored functions (CREATE FUNCTION). Stored procedures (CREATE PROCEDURE) and triggers do not have the same requirements, but any functions they call must still be properly declared.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL