<p>Occurs in MIXED binlog format when one statement touches both transactional and nontransactional tables, making the event unsafe for replication.</p>
<p>MySQL Error 1693 ER_BINLOG_UNSAFE_MIXED_STATEMENT arises when a statement in MIXED binlog mode reads or writes both transactional (InnoDB) and nontransactional (MyISAM) tables. Split the operation into separate, single-engine statements or switch to ROW binlog format to resolve the issue.</p>
Statement accesses nontransactional table as well as
MySQL returns error 1693 with SQLSTATE HY000 when binary logging is in MIXED mode and a single SQL statement accesses transactional and nontransactional tables. The server marks the operation unsafe for replication and stops execution to protect data consistency.
The error message is Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. Because MIXED mode chooses STATEMENT logging for many commands, combining engines can cause divergent results on replicas.
If unsafe statements are logged, replicas can apply them differently and drift from the primary. Catching the error early prevents silent data corruption and replication stoppages later.
The most common trigger is updating an InnoDB table and a MyISAM table in one INSERT ... SELECT, UPDATE, DELETE, or multi-table JOIN.
Creating temporary tables from a nontransactional engine inside a transaction that also modifies InnoDB rows also triggers the error.
Stored procedures or events that wrap cross-engine writes inside an implicit transaction will fail for the same reason.
Split the offending statement so each part touches only one storage engine. Commit the transactional section first, then run the nontransactional section outside the transaction.
Alternatively, change the global or session binlog_format to ROW for the duration of the procedure. Row-based logging captures the exact data changes, eliminating unsafe statement detection.
When migrating MyISAM tables to InnoDB, cross-engine INSERT ... SELECT commands frequently fail. Convert the legacy table before running the combined statement.
Reporting jobs that materialize data into MyISAM summary tables inside a transaction should run those inserts after COMMIT or switch the summary tables to InnoDB.
Standardize all persistent tables on a transactional engine such as InnoDB. This removes the primary cause of unsafe mixed statements.
Enable binlog_format = ROW in production clusters where mixed engines cannot be avoided. Monitor for warnings about unsafe statements during development.
Error 1592 (ER_BINLOG_UNSAFE_STATEMENT) flags other unsafe statements in STATEMENT mode. Moving to ROW format fixes both.
Error 1782 (ER_BINLOG_UNSAFE_FULLTEXT_PLUGIN) appears when full-text operations are logged in STATEMENT mode. Upgrading to a transactional full-text engine or using ROW logging resolves it.
An INSERT uses data from an InnoDB table to populate a MyISAM table in the same statement.
An UPDATE joins a MyISAM table to an InnoDB table and modifies one or both.
Creating or populating a nontransactional temporary table while other InnoDB updates are uncommitted.
A procedure wraps several DML commands on different engines inside a single BEGIN ... END block.
Flags unsafe statements in STATEMENT binlog mode even without mixed engines.
Occurs when full-text functions are logged in STATEMENT mode.
Replication stops when a slave thread encounters an unsafe or inconsistent event.
No. Row-based logging records individual row changes, so MySQL does not consider the statement unsafe.
You cannot disable it directly. The correct fix is to avoid unsafe statements or switch to ROW logging.
Legacy installations may retain MyISAM tables. Modern MySQL defaults to InnoDB, and you should migrate old tables.
Galaxy highlights storage engines in schema metadata, warns about mixed-engine queries, and lets you refactor statements quickly with its AI copilot.
.avif)
.avif){kind=logo}
