<p>The server blocks switching binlog_direct_non_transactional_updates inside a stored function or trigger, raising error 1686.</p>
<p>MySQL Error 1686: ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_DIRECT occurs when a stored function or trigger tries to change the binlog_direct_non_transactional_updates flag. Move the SET statement outside the routine or drop and recreate the routine without that statement to resolve the issue.</p>
Cannot change the binlog direct flag inside a stored
MySQL raises error 1686 with the condition name ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_DIRECT when code inside a stored function, procedure, or trigger attempts to change the session variable binlog_direct_non_transactional_updates.
The variable controls whether non transactional tables write directly to the binary log. MySQL does not allow toggling that flag within stored program context, so the statement fails immediately.
The error is triggered by any SET statement that targets binlog_direct_non_transactional_updates while the call stack is inside a stored routine or trigger.
It often appears in migration scripts that wrap DDL in procedures, or in replication maintenance code that tries to enable direct logging temporarily.
Remove the SET statement from the routine, or refactor logic so the session variable is changed outside the stored program. An external wrapper script or application layer call can set the variable, invoke the routine, then reset the variable afterward.
If the routine must manipulate non transactional tables, leave binlog_direct_non_transactional_updates unchanged and redesign the logging strategy or convert the tables to transactional engines like InnoDB.
Backup utilities sometimes create temporary procedures that enable the flag. Rewrite those utilities to run SET commands before creating the procedure.
Triggers that log to MyISAM tables may attempt to toggle the flag. Move logging logic to application code or use AFTER triggers without modifying the variable.
Never include SET binlog_direct_non_transactional_updates statements inside stored routines. Document the restriction in code reviews.
Use transactional storage engines whenever possible so the flag never needs to be toggled. Monitor new procedures for forbidden SET statements using static analysis tools or a code linter in Galaxy.
Error 1235 (ER_NOT_SUPPORTED_YET) can appear when attempting other unsupported operations inside triggers.
Error 1475 (ER_CANT_SET_GTID_PURGED_WHILE_SLAVE_STILL_RUNNING) is similar, blocking GTID changes in restricted contexts.
Direct assignment inside a function or procedure immediately triggers error 1686.
BEFORE or AFTER triggers that attempt to optimize logging by toggling the variable will fail.
Some auto generated scripts wrap DDL in procedures that set the flag without considering the restriction.
Raised when attempting binary log changes that conflict with GTID enforcement.
Occurs when a trigger tries to update the same table it monitors.
General error for unsupported operations inside stored routines.
No. The server blocks the operation, so ignoring the error means your routine will never run as intended.
Yes. The restriction exists in all supported versions that implement the binlog_direct_non_transactional_updates variable.
Switching non transactional tables to InnoDB removes the requirement, eliminating the error source.
Galaxy highlights session variable statements during code review and lets teams endorse corrected routines, reducing the chance of committing invalid SET commands.
.avif)
.avif){kind=logo}
