1617
MariaDBWARNINGNotableReplicationHIGH confidence
Statement with LIMIT is unsafe for statement-based replication
Production Risk
Medium — replication may produce inconsistent data on replicas.
What this means
A DML statement using LIMIT without ORDER BY is unsafe for statement-based replication because the set of affected rows may differ between master and replica.
Why it happens
- 1DELETE, UPDATE, or INSERT ... SELECT statements with LIMIT but without ORDER BY in STATEMENT binlog mode.
How to reproduce
trigger — this will error
trigger — this will error
DELETE FROM orders LIMIT 10; -- no ORDER BY
expected output
Warning 1617: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
Fix 1
Add ORDER BY to make the LIMIT deterministic
Add ORDER BY to make the LIMIT deterministic
DELETE FROM orders ORDER BY id LIMIT 10;
Why this works
ORDER BY ensures the same rows are selected on both master and replica.
Fix 2
Switch to ROW or MIXED binlog format
Switch to ROW or MIXED binlog format
SET GLOBAL binlog_format = 'ROW';
Why this works
ROW format logs the actual rows affected, not the statement, eliminating the non-determinism.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 1617 ER_BINLOG_UNSAFE_LIMIT
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev