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:
None 
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
Description:
We issue an INSERT ... SELECT .. ORDER BY [pk] which should be a safe statement and we get an "Unsafe statement" warning from MySQL.

How to repeat:

Here is an example:

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for osx10.6 (i386) using readline 5.1

Connection id:		156
Current database:	test
Current user:		[...]
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.30-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:			23 days 2 hours 55 min 24 sec

Threads: 4  Questions: 33225  Slow queries: 0  Opens: 219  Flush tables: 1  Open tables: 189  Queries per second avg: 0.016
--------------

mysql> create table replicated_table (id integer auto_increment, value varchar(10), primary key (id)) engine InnoDB;
Query OK, 0 rows affected (0.04 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.00 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)

The INSERT ... SELECT ... ORDER BY query should be deterministic in its selection of rows: the SELECT has been ORDERed BY the PK of the table and therefore has a predictable order on both the master and the slave.
[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)