Bug #62537 False "unsafe statement" log report
Submitted: 25 Sep 2011 16:58 Modified: 30 Dec 2011 19:11
Reporter: Thomas Auge Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.20 OS:Any
Assigned to:
Tags: binlog, log, unsafe statement
Triage: Needs Triage: D3 (Medium)

[25 Sep 2011 16:58] Thomas Auge
Description:
When updating a table with an exact match on a unique key in there WHERE clause and LIMIT 1, the following log entry is falsely created if binary logging is enabled:

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. Statement: UPDATE table SET x = y WHERE primary_key = z LIMIT 1

I realize that the LIMIT is redundant there, but obviously some developers tried to go overboard with the optimization, which can result in VERY spammy mysql logs.

How to repeat:
UPDATE table SET col1 = x WHERE unique_key = y LIMIT 1;

Suggested fix:
I don't know if it is fixable with a reasonable amount of overhead in the logging mechanism. Maybe the optimizer knows the total amount of affected rows without the limit anyway?
[30 Dec 2011 19:11] Valerii Kravchuk
Verified just as described with recent 5.5.20 on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.20-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'binlog%';
+-----------------------------------------+-----------+
| Variable_name                           | Value     |
+-----------------------------------------+-----------+
| binlog_cache_size                       | 32768     |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                           | STATEMENT |
| binlog_stmt_cache_size                  | 32768     |
+-----------------------------------------+-----------+
4 rows in set (0.01 sec)

mysql> create table tl(c1 int primary key, c2 int unique);
Query OK, 0 rows affected (0.54 sec)

mysql> insert into tl values (1,1), (2,2);
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tl;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.05 sec)

mysql> update tl set c1 = 3 where c1 = 2 limit 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 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. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
1 row in set (0.00 sec)
[6 Jul 2012 16:14] Marcus Bointon
I wonder if it's also wrong to display this warning in much broader cases. Given that it says 'This is unsafe because the set of rows included cannot be predicted.', it would make sense that if the order IS predictable, then it would be safe. All this should take is an unambiguous ORDER BY clause (where there is no possiblity for variation in order), for example (where id is a primary key):

UPDATE mytable SET c1 = 1 WHERE c1 = 0 ORDER BY id LIMIT 200

In a given table state, this should always give the same set of records in the same order, and thus should be replication safe, yet the warning is still displayed. Or am I wrong about how this works?
[23 Feb 2013 7:01] Shane Bester
Bug #68468 is a duplicate
[6 Dec 2013 16:55] Sveta Smirnova
See also Bug #71105