Bug #51638 SBR logging warning messages for primary key updates with a LIMIT of 1
Submitted: 2 Mar 2010 11:38 Modified: 19 Jul 2012 16:36
Reporter: Roger Nay Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.45 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[2 Mar 2010 11:38] Roger Nay
Description:
If a primary key is used with a LIMIT 1 statement and state based replication is enabled the following warning message is logged.

100302 10:57:59 [Warning] Statement may not be safe to log in statement format. Statement: UPDATE `app`.`test_table` SET `value`='4713250' WHERE `key`='unique_value' LIMIT 1

How to repeat:
* enable statement based logging.
* create a table with a unique primary key.
* update a row based on primary key with LIMIT 1.
* view the log.

Suggested fix:
Recognize that only 1 row will be updated and don't log the warning. 

The LIMIT 1 addition seems to generate the warning in the log rather than the results from the statement.
[2 Mar 2010 12:55] Simon Mudd
Actually the LIMIT clause can't cause a problem.

LIMIT X behaves as follows:

X=0: will not change anything on either the MASTER or the SLAVE.
X=1: will only change the MASTER and SLAVE based on the primary key.
X>1: while in theory it makes a difference since we are doing A PK UPDATE / DELETE only 1 row can be affected. So the behaviour is the same as X=1.
[6 Jul 2010 1:09] Luis Soares
See also: BUG#42415.
[11 Dec 2010 4:17] Bugs System
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/126560

3513 He Zhenxing	2010-12-11
      BUG#51638 SBR logging warning messages for primary key updates with a LIMIT of 1
      
      UPDATE/DELETE/INSERT..SELECT with LIMIT were marked unsafe
      unconditionally, but if there is a where condition or order by clause
      that can make the result row unique or ordered, then they are
      safe with LIMIT clause.
      
      This patch fixed the the problem of UPDATE/DELETE and INSERT..SELECT
      from single table for the following simple cases:
       - LIMIT 0 will always be safe
       - there is a WHERE condition that can only result in one row,
         for example the condition requires the primary key or a non-
         null unique key to be some constant value.
       - ORDER BY primary key or non-null unique key, and all fields
         length of the key are not longer than MAX_SORT_LENGTH.
      
      The problem of INSERT...SELECT from JOINed tables with LIMIT are
      not fixed in this patch, it will be fixed by patch for BUG#42415.
[11 Dec 2010 4:33] Bugs System
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/126561

3513 He Zhenxing	2010-12-11
      BUG#51638 SBR logging warning messages for primary key updates with a LIMIT of 1
      
      UPDATE/DELETE/INSERT..SELECT with LIMIT were marked unsafe
      unconditionally, but if there is a where condition or order by clause
      that can make the result row unique or ordered, then they are
      safe with LIMIT clause.
      
      This patch fixed the the problem of UPDATE/DELETE and INSERT..SELECT
      from single table for the following simple cases:
       - LIMIT 0 will always be safe
       - there is a WHERE condition that can only result in one row,
         for example the condition requires the primary key or a non-
         null unique key to be some constant value.
       - ORDER BY primary key or non-null unique key, and all fields
         length of the key are not longer than MAX_SORT_LENGTH.
      
      The problem of INSERT...SELECT from JOINed tables with LIMIT are
      not fixed in this patch, it will be fixed by patch for BUG#42415.
[19 Jul 2012 16:36] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[19 Jul 2012 16:38] Jon Stephens
Fixed in 5.5+.

Documented in the 5.5.27 and 5.6.7 changelogs as follows:

        Statements such as UPDATE ... WHERE primary_key_column=1 LIMIT 1
        are flagged as unsafe for statement-based logging, despite the
        fact that such statements are actually safe. In cases where a
        great many such statements were run, this could lead to disk
        space becoming exhausted do to the number of such false warnings
        being logged. To prevent this from happening, a warning
        suppression mechanism is introduced. This warning suppression
        acts as follows: Whenever the 50 most recent
        ER_BINLOG_UNSAFE_STATEMENT warnings have been generated more
        than 50 times in any 50-second period, warning suppression is
        enabled. When activated, this causes such warnings not to be
        written to the error log; instead, for each 50 warnings of this
        type, a note is written to the error log stating The last
        warning was repeated N times in last S seconds. This continues
        as long as the 50 most recent such warnings were issued in 50
        seconds or less; once the number of warnings has decreased below
        this threshold, the warnings are once again logged normally.

        The fix for this issue does not affect how these warnings are
        reported to MySQL clients; a warning is still sent to the client
        for each statement that generates the warning. This fix also
        does not make any changes in how the safety of any statement for
        statement-based logging is determined.

Also noted this change in "Determination of Safe and Unsafe Statements in Binary Logging" in the 5.5+ version of the Manual.

Closed.