<p>The error appears when one DML statement touches tables from both self-logging and non-self-logging storage engines while binary logging is enabled.</p>
<p>MySQL Error 1692: ER_BINLOG_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE signals that a single statement updates tables using different storage engines that handle their own binlogs differently, making statement-based replication unsafe. Switch to ROW binlog_format or split the statement to resolve the issue.</p>
Mixing self-logging and non-self-logging engines in a
Error 1692 (SQLSTATE HY000) occurs when a single DML statement mixes tables from a self-logging engine such as MyISAM with engines that rely on MySQLs binary log, like InnoDB, while BINLOG_FORMAT is STATEMENT or MIXED.
The server refuses to run the statement because self-logging engines record their own row changes, and logging the full SQL text again can create duplicates or data drift on replicas.
The error surfaces during INSERT ... SELECT, multi-table UPDATE, DELETE JOIN, or any statement referencing multiple tables whose engines differ in binlog behaviour. It is common after migrating one table to NDB Cluster or enabling ARCHIVE tables for historical data.
Ignoring the error blocks your transaction and halts application logic. For replication setups the risk is higher: forcing execution can corrupt replicas because self-logging engines and MySQL would generate overlapping binlog events.
Mixing storage engines with different logging semantics in one statement is the primary trigger. Self-logging engines write row events directly to the binary log, while others depend on the server to log the entire statement.
Using STATEMENT or MIXED binlog_format amplifies the problem because the whole SQL text is logged instead of individual row changes, leading to unsafe duplication.
Set binlog_format to ROW so MySQL logs each changed row exactly once, regardless of engine. Alternatively rewrite the query to touch one engine at a time or migrate all involved tables to the same engine.
As a last resort disable binary logging for the session if replication is not required, but do this only in controlled environments.
INSERT ... SELECT between an InnoDB source table and an NDB target fails - split into two steps: SELECT into a temporary table, then INSERT into NDB.
Multi-table UPDATE involving MyISAM and InnoDB fails - convert the MyISAM table to InnoDB or use two separate UPDATE statements.
Standardise on a single storage engine per schema when replication is enabled. Keep binlog_format set to ROW in mixed-engine environments and enforce it via configuration management.
Monitor slow query logs for cross-engine statements and review schema changes to ensure new tables use the preferred engine.
Error 1592 - TRIGGER_IN_WRONG_SCHEMA arises when triggers reference tables in another schema. Resolve by qualifying table names properly.
Error 1595 - VIEW_LOGGING_UNSAFE warns that a view definition is unsafe for statement-based logging. Switch to ROW format or materialise the view.
Copying data from an InnoDB table to an NDB table within one statement.
Updating or deleting rows across engines in a single joined statement.
A trigger on an InnoDB table writes to a MyISAM audit table, mixing engines during one event.
Statement-level logging makes replication unsafe whenever self-logging engines participate.
Occurs when engines with incompatible logging modes combine in the same transaction. Fix by using ROW format.
Raised when a view used in a statement is unsafe for statement-based logging.
Triggered when a trigger references objects in a different schema without proper qualification.
Yes, if you run with STATEMENT or MIXED binlog_format and mix engines, MySQL 8.0 still returns Error 1692.
ROW logging eliminates this specific error and is generally safer for replication, but increases binlog size. Monitor storage and network capacity.
No. MySQL blocks the statement even for SUPER users to protect replication integrity.
Galaxys editor highlights engine mismatches and suggests ROW logging or engine conversion, reducing trial-and-error during query design.
.avif)
.avif){kind=logo}
