Function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
Production Risk
High — can cause replication data inconsistency if used with STATEMENT binlog format.
Binary logging is enabled and a stored function or trigger was created without declaring it as DETERMINISTIC, NO SQL, or READS SQL DATA, which could cause replication inconsistency with statement-based logging.
- 1Creating a function without any of: DETERMINISTIC, NO SQL, READS SQL DATA
- 2Binary logging enabled with binlog_format=STATEMENT (or MIXED falling back to STATEMENT)
CREATE FUNCTION f() RETURNS INT BEGIN RETURN RAND(); END;
expected output
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled
Fix 1
Add the appropriate data access characteristic
CREATE FUNCTION f() RETURNS INT READS SQL DATA BEGIN RETURN 1; END;
Why this works
Declares the function behavior so MySQL can log it safely.
Fix 2
Switch to ROW-based binary logging
SET GLOBAL binlog_format = 'ROW';
Why this works
ROW format does not require the DETERMINISTIC declaration.
Fix 3
Override with log_bin_trust_function_creators (not recommended for production)
SET GLOBAL log_bin_trust_function_creators = 1;
Why this works
Disables the check; dangerous if functions are non-deterministic.
✕
MySQL 8.0 — 1418 ER_BINLOG_UNSAFE_ROUTINE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev