Description:
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.
