<p>MySQL raises error 1592 when a potentially non-deterministic statement is written to the binary log while BINLOG_FORMAT is set to STATEMENT.</p>
<p>MySQL Error 1592 ER_BINLOG_UNSAFE_STATEMENT appears when MySQL detects that a statement is unsafe to replicate with STATEMENT binlog format. Switch to ROW or MIXED format, or rewrite the query to be deterministic, to resolve the issue.</p>
Unsafe statement written to the binary log using
Error 1592 fires when the server tries to log a statement that may produce different results on replicas because BINLOG_FORMAT is STATEMENT. MySQL blocks the write to protect replication consistency.
The full message is: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. %s. It highlights that your current query is considered unsafe for statement-based replication.
The error surfaces during data-changing commands such as UPDATE, INSERT, DELETE, or certain DDL operations executed while the binary log format is STATEMENT and the query contains non-deterministic factors like LIMIT without ORDER BY or non-transactional tables.
If the statement were allowed, replicas could diverge from the primary server, breaking data integrity. Addressing the problem maintains replication correctness and avoids unexpected data drift.
Non-deterministic queries, operations on tables without primary keys, or the use of specific functions cause MySQL to mark the statement as unsafe. The next section lists concrete causes.
You can change the binlog format to ROW or MIXED, or rewrite the query to be deterministic. Each path is detailed with SQL examples below.
Developers frequently hit the error during bulk updates with LIMIT or while altering MyISAM tables. Tightening ordering and adding primary keys remove the unsafe flag.
Set BINLOG_FORMAT=ROW in production, always include ORDER BY with LIMIT, prefer InnoDB with primary keys, and test write statements on a staging environment using Galaxy to catch warnings.
Errors 1595 and 1665 also involve unsafe statements. They share fixes like switching binlog format or rewriting queries; details follow in the related errors section.
Updating or deleting rows with LIMIT but no ORDER BY lets row selection vary between servers, making the statement unsafe.
Use of UUID(), NOW(), RAND(), or USER() inside data-changing statements yields different values on replicas.
Statements touching both InnoDB and MyISAM tables cannot be reproduced exactly with statement logging.
Without a primary key, MySQL cannot guarantee identical row order, flagging the statement as unsafe.
Raised when INSERT IGNORE SELECT is unsafe under statement logging. Fix by switching to ROW or rewriting the query.
Occurs when updating AUTO_INCREMENT columns under STATEMENT format. Use ROW format to resolve.
Triggered when statement-based logs hit non-transactional storage engines. Convert tables or switch formats.
Enable session variable binlog_format warnings or run EXPLAIN to see if functions like RAND() or LIMIT without ORDER BY are present.
Yes. Use SET SESSION BINLOG_FORMAT='ROW' before running the specific statement, then revert if necessary.
ROW logs more data, increasing disk usage, but replicas apply changes faster. On modern hardware, the trade-off is usually acceptable.
Galaxy’s editor flags non-deterministic queries, suggests ROW format when needed, and lets teams share fixed queries, reducing the chance of unsafe statements reaching production.
.avif)
.avif){kind=logo}
