<p>The statement uses a LIMIT clause that makes it unsafe for statement-based binary logging and replication, so MySQL aborts the query with error 1668.</p>
<p>MySQL Error 1668: ER_BINLOG_UNSAFE_LIMIT occurs when a statement with a LIMIT clause cannot guarantee the same row order on replicas, making it unsafe for statement-based logging. Switch to row-based logging or rewrite the query without nondeterministic LIMIT to resolve the issue.</p>
The statement is unsafe because it uses a LIMIT clause.
MySQL raises error 1668 when a statement that modifies data also uses a LIMIT clause while the server is in STATEMENT or MIXED binary logging mode. The server cannot ensure that the same subset of rows will be affected on replicas, so it blocks the statement.
The error protects data consistency in replication setups. With statement-based logging, row order can vary between source and replicas, especially when no ORDER BY is present. Blocking the statement prevents silent divergence.
If the unsafe statement were allowed, replicas might update or delete different rows from the source, leading to data drift that is difficult to detect. Fixing the error keeps primary and replica data identical and avoids costly resynchronization.
The primary trigger is a data-modifying statement (UPDATE, DELETE, INSERT ... SELECT) that includes LIMIT without a fully deterministic ORDER BY. MySQL flags it as unsafe when binlog_format is STATEMENT or MIXED.
Another cause is using stored procedures or triggers that eventually execute such a LIMIT statement while statement logging is active. The server still evaluates safety at runtime and aborts execution.
The fastest remedy is to switch the session or server to ROW binary logging, which records each affected row and eliminates ambiguity.
Alternatively, rewrite the query: add a deterministic ORDER BY on a unique column, remove the LIMIT, or break the change into primary-key-based batches using WHERE conditions instead of LIMIT.
Bulk cleanup with LIMIT: A nightly DELETE FROM logs LIMIT 10000 fails. Replace LIMIT with WHERE id IN (SELECT id FROM logs ORDER BY id LIMIT 10000) or use primary-key ranges.
Paginated updates: UPDATE users SET status='inactive' ORDER BY last_seen LIMIT 500 looks deterministic but last_seen is not unique. Add ORDER BY last_seen, id to guarantee order.
Default to ROW binlog format in high-availability clusters. When STATEMENT logging is required, avoid LIMIT in data-changing queries or always pair LIMIT with ORDER BY on a unique indexed column.
Use application-level batching keyed by primary keys rather than relying on nondeterministic LIMIT clauses. Monitor the MySQL error log for ER_BINLOG_UNSAFE events to catch issues early.
Error 1592 (ER_BINLOG_UNSAFE_STATEMENT) indicates a broader class of unsafe statements, often fixed by similar rewrites or changing binlog_format.
Error 1665 (ER_BINLOG_UNSAFE_AUTOINC) surfaces when AUTO_INCREMENT columns are affected without proper locking. Switching to ROW logging or adding locking hints resolves it.
UPDATE or DELETE statements include LIMIT without ORDER BY, leading to unpredictable row selection across replicas.
Queries use ORDER BY on columns that do not uniquely identify rows, so the affected set may still vary.
The server runs in MIXED mode and falls back to STATEMENT logging for the offending query, triggering the safety check.
Procedures or triggers call internal queries containing LIMIT, surfacing the error when executed under statement logging.
General unsafe statement warning triggered by functions, non-determinism, or system tables. Fix by switching to ROW format or rewriting the query.
Occurs when multiple rows with AUTO_INCREMENT are inserted without proper locking in STATEMENT mode. Use ROW format or INSERT ... LOCK IN SHARE MODE.
Fires on INSERT IGNORE ... SELECT statements that may silently skip duplicates, leading to inconsistencies. Prefer ROW logging.
ROW logging increases binlog size but usually has minimal CPU overhead. Modern storage can handle the larger logs, and the data safety benefits outweigh the cost.
Yes, if ORDER BY includes a unique indexed column that guarantees the same row order across replicas. Otherwise, the query is still unsafe.
MIXED format helps but can still fall back to STATEMENT logging in many cases. If you depend on LIMIT clauses, use ROW format or deterministic queries.
Galaxy's AI copilot surfaces replication-safety warnings as you type. The editor highlights LIMIT clauses in data-modifying statements and suggests deterministic rewrites or session-level ROW logging.
.avif)
.avif){kind=logo}
