<p>The statement mixes transactional and non-transactional tables in one transaction, making the binary log entry unsafe for replication.</p>
<p>MySQL Error 1675: ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS occurs when a single transaction reads or writes transactional tables (InnoDB) and then writes to a non-transactional table (MyISAM). MySQL marks the statement unsafe for row-based replication and stops execution. Separate the operations or convert the non-transactional table to InnoDB to resolve the issue.</p>
Statement is unsafe because it accesses a
The error appears when a transaction that has already accessed a transactional table then tries to access a non-transactional table. MySQL flags the statement as unsafe because the binary log cannot guarantee atomicity across different storage engines.
It surfaces during row-based or mixed replication modes, or when binlog_format=STATEMENT and the optimizer spots potential data drift between master and replica.
Ignoring the warning risks silent replication inconsistencies, data loss, or slave divergence, especially in high-availability clusters.
Mixing InnoDB and MyISAM inside one explicit transaction triggers the safeguard. MySQL cannot roll back the MyISAM part if the InnoDB part fails, so it blocks the statement.
Convert non-transactional tables to InnoDB, split the logic into separate autocommit statements, or wrap only transactional tables in START TRANSACTION while handling non-transactional tables afterward.
Legacy reporting tables in MyISAM updated after OLTP writes often raise the error. Converting those tables or moving updates to a separate connection eliminates it.
Standardize on a transactional engine, audit mixed-engine transactions in CI, and enable MySQL strict mode to catch unsafe patterns early.
Error 1671 (ER_BINLOG_UNSAFE_AUTOCOMMIT) warns about unsafe autocommit statements. The fix is similar: restructure or change engine types.
Running UPDATE or INSERT on a MyISAM table after touching an InnoDB table inside the same transaction.
Audit or log tables created before engine unification often remain MyISAM and get updated in large transactions.
Triggers that write to non-transactional tables while the caller operates on transactional tables silently create unsafe binlog sequences.
Raised when autocommit statements are unsafe for replication; solved by using row-based format or restructuring queries.
Occurs when non-deterministic functions are used in statements logged in statement mode; fix by switching to row-based logging.
Signals potentially unsafe INSERT IGNORE ... SELECT operations; solved by rewriting the query or using row-based format.
No. Proceeding can cause replication drift. Always restructure the transaction or change table engines.
Row-based format reduces many unsafe cases but MySQL still blocks mixed-engine transactions to protect data integrity.
Any non-transactional engine, including ARCHIVE or CSV, can trigger it after transactional access.
Galaxy flags mixed-engine statements during query review and suggests engine conversion commands, preventing the error before deployment.
.avif)
.avif){kind=logo}
