MySQL Bugs: #42415: UPDATE with LIMIT clause unsafe for statement format even when ORDER BY PK

Bug #42415	UPDATE with LIMIT clause unsafe for statement format even when ORDER BY PK
Submitted:	28 Jan 2009 13:06	Modified:	24 Jun 2009 17:41
Reporter:	Vemund Ã˜stgaard
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Any
Assigned to:	Zhenxing He	Target Version:	5.1+
Triage:	Triaged: D2 (Serious) / R4 (High) / E4 (High)

Description:
The documentation states that any UPDATE statement with LIMIT clause is considered unsafe
since the order of the rows affected is not defined:
http://dev.mysql.com/doc/refman/5.1/en/replication-features-limit.html

However, if "ORDER BY PK" is used, the order of rows is defined and such a statement
could be logged in statement format without any warning. 

How to repeat:
mysql> CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY(a,b));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE SQL SECURITY INVOKER VIEW v (x,y) AS SELECT a, b FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE v SET x=x+1 ORDER BY y,x LIMIT 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

Yes, and not only if the Primary Key field(s) are in ORDER BY, but also if PK is in WHERE
clause, then an additional LIMIT does not make the statement insecure.
Some applications like phpMyAdmin adds a "LIMIT 1" to each statement event if the PK is
in WHERE clause and generates a lot of warnings.

On the replication architecture team meeting today, 
the following was decided:
   
   DECISION:
   - For BUG#42415, we will change the warning message and make sure
     the manual has correct text about a) what safe means, b) when the
     warning is issued.
   - After this, the bug will be closed as "Won't fix"
   
   DECISION:
   - The warning message "Statement is not safe to log in statement
     format." should be changed to "The statement may not be safe to
     log in statement format".
   - Document in the manual that:
     "Note that the decision on whether a statement is safe is
     heuristic. This means that the server will switch (if format is
     MIXED) to row-based logging for statements that it cannot
     guarantee that they are safe to log in statement-based format."

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73556

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74458

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74544

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74576

2895 He Zhenxing	2009-05-20
      Post fix of result file after push of BUG#42415

Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source
revid:matthias.leich@sun.com-20090520195829-2q0xhtjqt4gc8htf) (merge vers: 5.1.36)
(pib:6)

Documented bugfix in the 5.1.36 changelog as follows:

        The warning "Statement is not safe to log in statement format",
        issued in situations when it cannot be determined whether a
        statement can be written reliably to the binary log, has been
        changed to "Statement may not be safe to log in statement 
        format".

Also updated "Binary Logging Formats" with info contained in comments from Lars and
others.

Set NDI status, waiting for post-5.1 push.

Commit with the documentation changes is here:http://lists.mysql.com/commits/75164

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version
source revid:matthias.leich@sun.com-20090520192627-94166m3dcjv48a6g) (merge vers:
6.0.12-alpha) (pib:11)

Bugfix also documented in the 5.4.4 changelog/5.4 Manual. Closed.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l)
(version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers:
5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc)
(version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers:
5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4)
(version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers:
5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr)
(version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers:
5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.