MySQL Bugs: #51638: SBR logging warning messages for primary key updates with a LIMIT of 1

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 David 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

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.

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.

See also: BUG#42415.

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.

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.

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

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.