Bug #73896 | MySQL issues an "Unsafe statement" warning for what should be a safe statement | ||
---|---|---|---|
Submitted: | 11 Sep 2014 15:40 | Modified: | 2 Feb 2015 22:04 |
Reporter: | Christopher Schultz | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5.40/5.6.23 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 Sep 2014 15:40]
Christopher Schultz
[11 Sep 2014 17:27]
MySQL Verification Team
Thank you for the bug report. Please provide your my.cnf file. Thanks.
[11 Sep 2014 17:56]
Christopher Schultz
I've removed all comments. I think this is a pretty standard configuration; it probably came out-of-the-box from the Mac OS X bundle for MySQL. [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql-data port = 3306 character-set-server=utf8 collation-server=utf8_general_ci socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin=mysql-bin server-id = 1 Without a binlog_format declaration, the binlog_format should default to 'statement'. I still believe that this is a "safe" statement, even for statement-based replication. I'm happy to upgrade to 5.5.latest (currently 5.5.39) to test against that version as well.
[7 Oct 2014 22:59]
MySQL Verification Team
Please test 5.5.39 and comment your result here. Thanks.
[8 Oct 2014 21:05]
Christopher Schultz
Unsurprisingly, 5.5.40 offers no improvement: mysql> status -------------- mysql Ver 14.14 Distrib 5.5.40, for osx10.6 (i386) using readline 5.1 Connection id: 3 Current database: test Current user: cschultz-admin@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.40-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Insert id: 4 Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 min 18 sec Threads: 1 Questions: 26 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 28 Queries per second avg: 0.188 -------------- mysql> use test Database changed mysql> create table replicated_table (id integer auto_increment, value varchar(10), primary key (id)) engine InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> insert into replicated_table values (1, 'foo'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table values (2, 'bar'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table values (3, 'baz'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table (value) select value FROM -> replicated_table where id in (1,2,3) order by id; Query OK, 3 rows affected, 1 warning (0.01 sec) Records: 3 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. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. 1 row in set (0.00 sec) Also verified on Debian Linux: Server version: 5.5.38-0+wheezy1-log (Debian) This is not Mac OS-specific.
[2 Feb 2015 21:35]
MySQL Verification Team
Not repeatable with 5.6.23: mysql> status -------------- mysql Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using EditLine wrapper Connection id: 2 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.23 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 min 15 sec Threads: 1 Questions: 29 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.214 -------------- mysql> use test Database changed mysql> create table replicated_table (id integer auto_increment, value varchar(10), primary key (id)) engine InnoDB; Query OK, 0 rows affected (0.21 sec) mysql> insert into replicated_table values (1, 'foo'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table values (2, 'bar'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table values (3, 'baz'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table (value) select value FROM -> replicated_table where id in (1,2,3) order by id; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
[2 Feb 2015 21:56]
MySQL Verification Team
I was able to repeat when setting: log-bin=mysql-bin character-set-server=utf8 collation-server=utf8_general_ci mysql 5.5 > status -------------- c:\dbs\5.5\bin\mysql Ver 14.14 Distrib 5.5.43, for Win64 (AMD64) Connection id: 3 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.5.43-log Source distribution Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: cp850 Conn. characterset: cp850 TCP port: 3550 Uptime: 2 min 21 sec Threads: 1 Questions: 22 Slow queries: 0 Opens: 36 Flush tables: 1 Open tables: 27 Queries per second avg: 0.156 -------------- mysql 5.5 > use test Database changed mysql 5.5 > create table replicated_table (id integer auto_increment, value varchar(10), primary key (id)) engine InnoDB; Query OK, 0 rows affected (0.09 sec) mysql 5.5 > insert into replicated_table values (1, 'foo'); Query OK, 1 row affected (0.05 sec) mysql 5.5 > insert into replicated_table values (2, 'bar'); Query OK, 1 row affected (0.06 sec) mysql 5.5 > insert into replicated_table values (3, 'baz'); Query OK, 1 row affected (0.06 sec) mysql 5.5 > insert into replicated_table (value) select value FROM replicated_table where id in (1,2,3) order by id; Query OK, 3 rows affected, 1 warning (0.06 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql 5.5 > show warnings\G *************************** 1. row *************************** Level: Note Code: 1592 Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. 1 row in set (0.00 sec)
[2 Feb 2015 22:04]
MySQL Verification Team
Thank you for the feedback. Repeatable on Mac with same setting done on Windows with server 5.6.23: [20:02:25][miguel-mac:]~ miguel$ sudo /usr/local/mysql-5.6.23-osx10.8-x86_64//support-files/mysql.server start Starting MySQL .. SUCCESS! [20:02:37][miguel-mac:]~ miguel$ mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.23-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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> status -------------- mysql Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using EditLine wrapper Connection id: 1 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.23-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 15 sec Threads: 1 Questions: 9 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.600 -------------- mysql> use test Database changed mysql> create table replicated_table (id integer auto_increment, value varchar(10), primary key (id)) engine InnoDB; Query OK, 0 rows affected (0.10 sec) mysql> insert into replicated_table values (1, 'foo'); Query OK, 1 row affected (0.01 sec) mysql> insert into replicated_table values (2, 'bar'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table values (3, 'baz'); Query OK, 1 row affected (0.00 sec) mysql> insert into replicated_table (value) select value FROM replicated_table where id in (1,2,3) order by id; Query OK, 3 rows affected, 1 warning (0.01 sec) Records: 3 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. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. 1 row in set (0.00 sec)