Bug #71105 DELETE .. ORDER BY .. LIMIT is safe in some cases
Submitted: 6 Dec 2013 16:00 Modified: 6 Dec 2013 16:55
Reporter: Federico Razzoli Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.74, 5.5.35, 5.6.16 OS:Any
Assigned to: CPU Architecture:Any

[6 Dec 2013 16:00] Federico Razzoli
Description:
IMO, DELETE with ORDER BY and LIMIT should not produce the 1592 warning at least in 2 cases:

1)
mysql> CREATE TABLE t1 (a INT NOT NULL UNIQUE) ENGINE=InnoDB;
Query OK, 0 rows affected (0,46 sec)

mysql> DELETE FROM t1 ORDER BY a LIMIT 1;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Note (Code 1592): 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.

Why? ORDER BY <unique_field> LIMIT is deterministic.

2)
mysql> CREATE TABLE t2 (a INT, b INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0,31 sec)

mysql> DELETE FROM t2 ORDER BY a, b LIMIT 1;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Note (Code 1592): 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.

Why? ORDER BY <all_columns> LIMIT is deterministic.

How to repeat:
see above
[6 Dec 2013 16:17] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=62537
[6 Dec 2013 16:55] Sveta Smirnova
Thank you for the report.

Verified as described. I agree this can be duplicate of bug #62537, but decided to keep it separate for now.