MySQL Bugs: #47557: mysqlbinlog -v should output 'limit 1' for update and delete row events

Bug #47557	mysqlbinlog -v should output 'limit 1' for update and delete row events
Submitted:	23 Sep 2009 15:50	Modified:	27 Jan 2011 20:00
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	mysqlbinlog, RBL

Description:
When you execute mysqlbinlog -v, it decodes row events to statements. write_rows_event becomes INSERT, update_rows_event becomes UPDATE, and delete_rows_event becomes DELETE.

While an update/delete row event only modifies or deletes as many rows as are listed in the event, an UPDATE or DELETE statement updates or deletes all matching rows in the table. So it the UPDATE or DELETE statement generated by mysqlbinlog will update or delete too much, in case the table contains several identical rows.

How to repeat:
======== begin test case ========
source include/have_log_bin.inc;
source include/have_binlog_format_row.inc;

create table t1 (a int);
insert into t1 values (1), (1);
update t1 set a=3 where a = 1 limit 1;
delete from t1 where a = 3 limit 1;

--let $MYSQLD_DATADIR= `SELECT @@datadir`
--exec $MYSQL_BINLOG -F -v $MYSQLD_DATADIR/master-bin.000001
======== end test case ========

The output from mysqlbinlog for the update and delete statements contains:

### UPDATE test.t1
### WHERE
###   @1=1
### SET
###   @1=3

and

### DELETE FROM test.t1
### WHERE
###   @1=3

, respectively.

Suggested fix:
Add LIMIT 1 to the end of UPDATE and DELETE statements generated for row events by mysqlbinlog -v.

This is not a bug and there is no need to append 'LIMIT 1' to
mysqlbinlog -v output.

The purpose of the -v option is to display *comments* explaining
what the row events do: it was never meant to output the
correct SQL statements out of the rows events.

The display format chosen for structuring such comments was a
pseudo-SQL notation, in which SQL-like statements are
reconstructed out of the rows events. 

This is explained in the manual:

"To see the row events as comments in the form of “pseudo-SQL”
statements, run mysqlbinlog with the --verbose or -v option. The
output will contain lines beginning with ###:"

See: http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog-row-events.html

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/129067

3522 Luis Soares	2011-01-18
      BUG#47557: mysqlbinlog -v should output 'limit 1' for update and 
                 delete row events
      
      mysqlbinlog -v shows pseudo-SQL statements, reconstructed out of
      the rows, as comments. However, --verbose description mentions that
      it reconstructs SQL statements, when in fact these are SQL-like
      statements and not, 100% valid, SQL instructions.
      
      We fix this by replacing "SQL" with "pseudo-SQL" in mysqlbinlog 
      help text (pseudo-SQL is actually the term used in the user
      manual).

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/129076

3522 Luis Soares	2011-01-18
      BUG#47557: mysqlbinlog -v should output 'limit 1' for update and 
                 delete row events
      
      mysqlbinlog -v shows pseudo-SQL statements, reconstructed out of
      the rows, as comments. However, --verbose description mentions that
      it reconstructs SQL statements, when in fact these are SQL-like
      statements and not, 100% valid, SQL instructions.
      
      We fix this by replacing "SQL" with "pseudo-SQL" in mysqlbinlog 
      help text (pseudo-SQL is actually the term used in the user
      manual).

Queued in mysql-trunk.

Pushed into mysql-trunk 5.6.2 (revid:luis.soares@oracle.com-20110118123551-wv9zw6gtdxqyv51w) (version source revid:luis.soares@oracle.com-20110118123551-wv9zw6gtdxqyv51w) (merge vers: 5.6.2) (pib:24)

Documented in the 5.6.2 changelog as follows:

        The --help text for mysqlbinlog now indicates that the --verbose
        (-v) option outputs pseudo-SQL that is not necessarily valid SQL
        and cannot be guaranteed to work verbatim in MySQL clients.

Closed.