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: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | 5.5.20 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | binlog, log, unsafe statement |
[25 Sep 2011 16:58]
Thomas Auge
[30 Dec 2011 19:11]
Valeriy 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]
MySQL Verification Team
Bug #68468 is a duplicate
[6 Dec 2013 16:55]
Sveta Smirnova
See also Bug #71105