<p>The error appears when an unsafe statement targets a statement-only storage engine while the server runs in MIXED binary log mode.</p>
<p>MySQL Error 1663: ER_BINLOG_UNSAFE_AND_STMT_ENGINE occurs when an unsafe statement tries to write to a statement-only storage engine while BINLOG_FORMAT is MIXED. Switch to ROW logging or move the table to InnoDB to resolve the issue.</p>
Cannot execute statement: impossible to write to binary
MySQL error 1663 appears with message 'Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED'. The server blocks the statement to protect replication integrity.
The conflict happens when an unsafe, non-deterministic statement targets a storage engine that only supports statement-based logging while the server is running in MIXED mode. Because the engine forbids row-logging, MySQL aborts the request instead of corrupting replicas.
Using MEMORY, BLACKHOLE, or custom statement-only engines in combination with unsafe statements triggers the condition.
Queries containing non-deterministic functions, LIMIT without ORDER BY, subqueries altering selected rows, or updates to tables used in triggers are flagged as unsafe for statement-based replication.
BINLOG_FORMAT=MIXED directs MySQL to switch automatically to row format for unsafe statements, but a statement-only engine blocks the switch and forces the error.
Set binlog_format to ROW at the session or global level so that the statement can be logged row by row even when the engine allows it.
If changing the format is impossible, move the affected table to an engine that supports row-based logging such as InnoDB or MyISAM.
Alternatively, rewrite the statement to become deterministic and safe for statement logging, removing non-deterministic functions or adding explicit ORDER BY.
INSERT INTO memory_tbl SELECT * FROM source_tbl WHERE updated_at > NOW() fails because NOW() is non-deterministic and MEMORY is statement-only. Converting memory_tbl to InnoDB resolves it.
UPDATE blackhole_tbl SET val = val + 1 ORDER BY RAND() LIMIT 10 fails for the same reason; switching binlog_format to ROW before executing works.
Standardize on ROW binlog format in replicated environments to eliminate most unsafe statement problems.
Avoid using statement-only engines for tables that need writes in replication setups.
Review queries for non-deterministic functions and add deterministic clauses or rewrite them.
Error 1592 'Statement violates row-based logging' appears when the server is in STATEMENT mode; switch to MIXED or ROW.
Error 1677 'Statement violates MIXED mode' indicates unsafe statement but engine supports row; enable ROW or rewrite query.
Error 1785 'Statement unsafe because it uses system variables' is resolved by removing the variable or using row logging.
Using NOW(), RAND(), UUID(), or subqueries makes the statement unsafe for statement-based replication.
Engines such as MEMORY, BLACKHOLE, or custom plugins do not support row-based logging, forcing MySQL to stay in STATEMENT mode.
MIXED mode tries to switch formats automatically; the combination with a statement-only engine causes conflict.
These constructs are marked unsafe because they may affect rows unpredictably when replayed on replicas.
Raised when a statement requires row logging but server is in STATEMENT mode.
Occurs when unsafe AUTOINCREMENT updates are detected in MIXED format.
Thrown when an unsafe system variable is used inside the statement during mixed logging.
Disabling binary logging avoids the check but breaks replication and point-in-time recovery. Use it only on standalone, non-critical servers.
InnoDB writes to disk, so read/write latency increases, but you gain durability and replication safety. For pure in-memory speed, consider cache layers.
Row logging writes more data to the binary log but reduces replica replay time and avoids full-table scans on replicas, often balancing the overhead.
Galaxy highlights unsafe statements in its editor and recommends changing binlog_format or engine before execution, reducing replication incidents.
.avif)
.avif){kind=logo}
