<p>MySQL blocks creation of a stored function or trigger that is not marked deterministic or read-only when binary logging is enabled.</p>
<p>MySQL Error 1418: ER_BINLOG_UNSAFE_ROUTINE occurs when you create a function, trigger, or procedure without the DETERMINISTIC, NO SQL, or READS SQL DATA clause while binary logging is on. Declare the routine as DETERMINISTIC READS SQL DATA or set log_bin_trust_function_creators=1 to resolve it.</p>
This function has none of DETERMINISTIC, NO SQL, or READS
The server refuses to create a routine because it could break replication consistency. Binary logging is enabled, and the routine is not declared DETERMINISTIC, NO SQL, or READS SQL DATA. Without these attributes, MySQL cannot guarantee identical results on replicas.
The error protects statement-based replication. If a function produces nondeterministic output, slaves might diverge from the primary, causing data drift and inconsistent analytics.
The message is thrown during CREATE FUNCTION, CREATE TRIGGER, or ALTER ROUTINE statements executed by users lacking SUPER privileges while log_bin is active and log_bin_trust_function_creators is OFF (default).
It also appears after a server restart if a routine definition lacking the required clauses is reloaded from the mysql.proc table and fails validation.
Leaving the routine uncreated blocks application deployments, ETL jobs, and migrations that depend on it. For replication setups, bypassing the safety check without understanding the risk can introduce silent data inconsistency.
Resolving the error ensures reliable failover, accurate read replicas, and predictable audit trails in the binary log.
Missing DETERMINISTIC clause lets MySQL assume the function can return different results for the same inputs, violating deterministic replication rules.
Omitting NO SQL or READS SQL DATA means the routine may modify data, making statement-based logging unsafe if row changes differ across servers.
A disabled log_bin_trust_function_creators variable forces extra safety for users without SUPER, intensifying the check.
Add the DETERMINISTIC keyword and the READS SQL DATA or NO SQL attribute to the CREATE FUNCTION or TRIGGER statement. This assures MySQL the routine will not change data unpredictably.
If you fully control replication reliability, temporarily set SET GLOBAL log_bin_trust_function_creators = 1; to allow routine creation without extra attributes. Remember to persist it in my.cnf for restarts or revert after deployment.
CI/CD migrations often fail when developers forget deterministic markers. Update the migration script with proper clauses and rerun.
On managed MySQL services where SUPER privileges are restricted, enabling log_bin_trust_function_creators is usually impossible. Editing the routine definition is the only safe route.
Adopt a coding standard requiring DETERMINISTIC READS SQL DATA on every pure function and NO SQL on metadata routines.
Set log_bin_trust_function_creators to 1 in non-production but keep it 0 in production to force review before deployment.
Use Galaxy’s linting in the SQL editor to flag missing determinism clauses during code review, preventing failed releases.
ER_BINLOG_UNSAFE_STATEMENT (1592) warns about unsafe statements like INSERT ... SELECT with AUTO_INCREMENT under statement logging. Switch to row-based logging or rewrite the query.
ER_BINLOG_UNSAFE_CREATE_IGNORE_SELECT (1604) occurs on CREATE TABLE ... SELECT combined with IGNORE. Use row logging or rewrite to two statements.
The routine lacks the DETERMINISTIC keyword, so MySQL treats it as potentially nondeterministic.
Without NO SQL or READS SQL DATA, MySQL assumes the routine may modify data, which is unsafe under statement logging.
The global variable is OFF, so creators without SUPER privileges must prove routine safety via attributes.
When binlog_format is STATEMENT or MIXED, extra safety checks apply to prevent divergence on replicas.
Statement unsafe for statement replication, like UPDATE with nondeterministic functions.
Unsafe CREATE TABLE ... SELECT with IGNORE under statement binlogging.
Similar to 1418 but raised after execution of an already created routine that is considered unsafe.
Appears when replication freezes DDL on tables in backup or clone operations.
If a function returns the same result for identical inputs, mark it DETERMINISTIC. Otherwise, MySQL may still allow creation if you enable log_bin_trust_function_creators, but replication risk increases.
It is acceptable when creators are experienced and routines are reviewed. In multi-team environments, keep it OFF to force explicit routine attributes.
Yes. Setting binlog_format = ROW skips most unsafe routine checks, but row-based logs grow larger and may impact performance.
Galaxy highlights missing routine safety clauses in real time and suggests deterministic keywords, preventing deployment failures before they reach CI.
.avif)
.avif){kind=logo}
