MySQL Bugs: #71024: Safe statement marked as unsafe

Bug #71024	Safe statement marked as unsafe
Submitted:	27 Nov 2013 11:35	Modified:	29 Nov 2013 13:30
Reporter:	Paul Keenan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	MacOS (10.8)
Assigned to:		CPU Architecture:	Any

Description:
The documentation at http://dev.mysql.com/doc/refman/5.6/en/replication-features-auto-increment.html says "Statement-based replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions:"

None of the listed exceptions apply to a simple INSERT INTO ... SELECT ... ORDER BY, and the statement must by definition be 100% deterministic at all times because an ORDER BY lists all the columns in the SELECT part of the statement.

However, I still see the warning message.

The warning message agrees with the behaviour but disagrees with the current documentation.  It states that the row order of INSERT INTO ... SELECT statements cannot be predicted and are thus automatically unsafe, whereas in practice the addition of an ORDER BY should suffice, and the documentation agrees.

Can you investigate ?

Thanks,
Paul

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3167
Server version: 5.6.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (
    -> f1 INTEGER NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.58 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t2 (
    -> id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> f1 INTEGER NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.20 sec)

mysql> INSERT INTO t2 (f1) SELECT f1 FROM t1 ORDER BY f1;
Query OK, 3 rows affected, 1 warning (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | 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)

mysql> 

Suggested fix:
Statement should not produce a warning as per the current documentation.

Text for error 1592 should be brought into agreement with the current documentation.

Thank you for the bug report.

Note given the example, the INSERT ... SELECT ... ORDER BY is only deterministic because t1 only has one column. The ORDER BY is for a non-unique column, so two rows can have the same value in which case the order of those rows is not known. With just that one column, the order doesn't matter, but that is not the general case.

The INSERT also results in a warning if t1.f1 is turned into a primary key.

    ALTER TABLE t1 ADD PRIMARY KEY (f1);

Of course the issue then becomes more tricky as whether the auto-increment on t2 is deterministic also depends on the value of innodb_autoinc_lock_mode.

Thanks for taking the time to look into this.

I meant to make the wording a little clearer on the bug report.  It's certainly the case that whether the statement is deterministic depends on what's mentioned in the ORDER BY and the contents of the table.

However, in the case where the ORDER BY contains all of the columns in the SELECT statement, then it's guaranteed to be deterministic.  I thought that's what MySQL must be doing behind the scenes when I read the documentation "Statement-based replication of AUTO_INCREMENT [...] is done correctly, subject to [...]".  The same page also mentions (although under the ALTER TABLE example) "Important - To guarantee the same ordering on both master and slave, the ORDER BY clause must name all columns of t1."  This confirmed my belief that this optimisation would cause statement based replication to work.

FYI, I've been trying to use MIXED mode replication for my production database, but am having separate issues there where statements executed quickly on the master never complete on the slave depsite identical setups (h/w, s/w, tables & data).  I therefore am using statement based, which I know will work for my queries because I can see that the statement is deterministic.  I get the warnings but my reading of the documentation suggested that MySQL shouldn't produce them in this case.

If it can't be fixed easily, I would suggest another course of action would be to reword the documentation so that it's clear that any INSERT INTO ... SELECT will be marked as Unsafe for replication.  I'd obviously prefer if the statement wasn't marked as Unsafe in cases like the example given.

I'd also suggest changing the wording in error 1592 regardless of the outcome.  It's not accurate to say the the order cannot be predicted.  Perhaps change "cannot" to "cannot always".

Cheers,
Paul