<p>The routine changed data while binary logging was active but was not declared with NO SQL, READS SQL DATA, or MODIFIES SQL DATA, so MySQL aborted to protect replication integrity.</p>
<p>MySQL Error 1417: ER_FAILED_ROUTINE_BREAK_BINLOG appears when a stored routine that writes data is not marked with a SQL characteristic while binary logging is enabled. Declare the routine with MODIFIES SQL DATA or disable binary logging to resolve the issue.</p>
A routine failed and has neither NO SQL nor READS SQL
The exact message is ER_FAILED_ROUTINE_BREAK_BINLOG: "A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes".
MySQL halts the routine because it executed data-changing statements while binary logging is on, yet its definition does not indicate that it can modify data. Logging an unsafe statement could corrupt replicas, so the server stops and raises error 1417.
The most common trigger is a stored procedure or function defined without a SQL characteristic but containing INSERT, UPDATE, DELETE, or LOAD DATA statements.
The error also arises when a trigger calls another routine that lacks the proper characteristic while the server is in STATEMENT or MIXED replication mode.
Using non-transactional engines such as MyISAM inside the routine while log_bin is enabled further increases the risk because their writes cannot be rolled back.
The safest fix is to alter or recreate the routine with an explicit characteristic: MODIFIES SQL DATA for write operations, READS SQL DATA for reads, or NO SQL if it truly does not touch data.
In development environments you can disable binary logging (SET sql_log_bin=0) before creating the routine, but this is not recommended for production replicas.
If you only need row-based logging, switch the global binlog_format to ROW; MySQL no longer checks routine characteristics in ROW mode.
Scenario: Legacy procedure updates audit tables but was created years ago without characteristics.
Solution: ALTER PROCEDURE proc_audit MODIFIES SQL DATA.
Scenario: Function calculates totals and writes to a cache table.
Solution: Recreate the function with DETERMINISTIC MODIFIES SQL DATA.
Scenario: Trigger calls a helper routine that writes to MyISAM while binlog_format=MIXED.
Solution: Convert the helper to InnoDB and mark MODIFIES SQL DATA, or change binlog_format to ROW.
Always include an accurate characteristic clause when you create routines. Add MODIFIES SQL DATA for any write, READS SQL DATA for pure reads, and NO SQL only for computation-only logic.
Set binlog_format=ROW in modern replication setups to bypass statement-based limitations.
Adopt InnoDB for all replicated tables so transactional consistency is guaranteed.
Use a code review checklist that flags CREATE PROCEDURE and CREATE FUNCTION statements without characteristics before deployment.
Error 1418 (ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG): A statement is not permitted in stored function or trigger. Remove unsafe statements or convert to procedure.
Error 1442 (ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG): Updating the same table that invoked the trigger is not allowed. Write changes to another table or use AFTER triggers.
Error 1645 (ER_BINLOG_UNSAFE_ROUTINE): Routine is marked as unsafe for statement-based logging. Switch to ROW format or rewrite routine.
Routine defined without MODIFIES SQL DATA yet performs INSERT, UPDATE, or DELETE.
Function created without READS SQL DATA but selects rows inside its body.
Trigger invokes a helper procedure lacking any SQL characteristic while binlog_format is STATEMENT or MIXED.
Server uses non-transactional engines (MyISAM, MEMORY) inside routine while binary logging is enabled.
Raised when disallowed statements appear inside stored functions or triggers.
Occurs when a trigger tries to update the same table that fired it.
Logged when a routine is considered unsafe for statement-based replication.
The server needs to know whether a routine writes data so it can decide if statement-based replication is safe. Missing characteristics risk data drift across replicas.
No. Declaring NO SQL on a routine that modifies data is misleading and still unsafe. Use MODIFIES SQL DATA instead.
Yes, MySQL skips the characteristic check in ROW mode because row images provide deterministic replication, but ensure all servers in the topology support ROW.
Galaxy highlights routine definitions lacking SQL characteristics during code review and offers AI suggestions to add MODIFIES SQL DATA, reducing production surprises.
.avif)
.avif){kind=logo}
