Bug #67804 Unsafe statement written to the binary log should not appear w/ ORDER BY
Submitted: 4 Dec 2012 15:15 Modified: 10 Jan 2013 14:22
Reporter: Rob Wagner Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.28 OS:Linux (RHEL 5.6)
Assigned to: CPU Architecture:Any

[4 Dec 2012 15:15] Rob Wagner
https://dev.mysql.com/doc/refman/5.5/en/replace.html indicates:

Because the results of REPLACE ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging these statements for the master and the slave to diverge. For this reason, in MySQL 5.5.18 and later, REPLACE ... SELECT statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using the STATEMENT binary logging mode...

However, if a REPLACE... SELECT statement is called with an order by clause, the warning still gets logged.

How to repeat:
mysql> create table A (a int primary key, b int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into A values (1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into A values (3, 4);
Query OK, 1 row affected (0.00 sec)

mysql> set binlog_format=STATEMENT;
Query OK, 0 rows affected (0.00 sec)

mysql> replace into A select a+100, b from A order by a;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
1 row in set (0.00 sec)

Suggested fix:
Provide a mechanism to selectively avoid this warning, so that it doesn't lard up the .err log.  There are instances where replace can be used safely with statement-based replication (for example, Percona's pt-table-checksum).

One possible mechanism is to avoid logging the error if an ORDER BY clause on a primary key is included in the SELECT query.  Another possibility is to provide a sql hint that can be included to indicate one does not want the warning to be logged.
[4 Dec 2012 19:26] Sveta Smirnova
Thank you for the report.

Verified as described. Looks like we are too strict or too naive in this case.
[10 Jan 2013 14:22] Erlend Dahl
Duplicate of bug#42415