Bug #50935 Record original SQL statement in RBR binlog
Submitted: 5 Feb 2010 4:12 Modified: 24 Mar 2011 15:01
Reporter: Andrew Dalgleish Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Daogang Qu CPU Architecture:Any

[5 Feb 2010 4:12] Andrew Dalgleish
Description:
To aid in debugging replication issues, it would help to have the full original SQL statement (including any embedded comments) recorded in the binlog when in RBR mode.

The reconstructed pseudo-SQL is not always sufficient to uniquely identify the source of the statement.

How to repeat:
n/a

Suggested fix:
Record the original SQL statement in the binlog.
[5 Feb 2010 5:53] MySQL Verification Team
http://askmonty.org/worklog/Server-RawIdeaBin/?tid=47 :)
[26 Mar 2010 14:46] Valeriy Kravchuk
Thank you for the feature request.
[13 May 2010 4:55] 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/108213

3185 Dao-Gang.Qu@sun.com	2010-05-13
      WL #4033 Support for informational log events
      Bug #50935 Record original SQL statement in RBR binlog
      
      Adding a new ignorable event, and its incremental sub-hierarchy,
      which have as default that if their type code are not recognized,
      these events are just ignored and replication keeps going as usual.
      
      Record original SQL statement in RBR binlog by ignorable log event,
      which can be displayed with its row event as comment by 
      "SHOW BINLOG EVENTS" and 'MYSQLBINLOG DUMP'. 
     @ mysql-test/extra/binlog_tests/binlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated due to the patch of WL#4033.
     @ mysql-test/extra/rpl_tests/rpl_log.test
        Updated due to the patch of WL#4033.
     @ mysql-test/include/show_binlog_events.inc
        Updated due to the patch of WL#4033.
     @ mysql-test/include/show_binlog_events2.inc
        Updated due to the patch of WL#4033.
     @ mysql-test/r/ctype_cp932_binlog_stm.result
        Updated due to the patch of WL#4033.
     @ mysql-test/r/flush_block_commit_notembedded.result
        Updated due to the patch of WL#4033.
     @ mysql-test/r/multi_update.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/t/binlog_incident.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/t/binlog_killed_simulate.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_deadlock_innodb.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_heartbeat.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_ignorable_event.result
        Test result for wl#4033 and Bug#50935.
     @ mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_conflicts.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_drop.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_log.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_log_innodb.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_slave_skip.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_stm_log.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_ignorable_event.test
        Added test to verify if the original SQL statement is displayed
        with its row event as comment in RBR by SHOW BINLOG EVENTS.
     @ mysql-test/suite/rpl/t/rpl_row_drop.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_row_flsh_tbls.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_stm_flsh_tbls.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_multi.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl_ndb/r/rpl_truncate_7ndb.result
        Updated due to the patch of WL#4033.
     @ mysql-test/t/ctype_cp932_binlog_stm.test
        Updated due to the patch of WL#4033.
     @ mysql-test/t/mysqlbinlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/t/mysqlbinlog2.test
        Updated due to the patch of WL#4033.
     @ sql/log.cc
        Added code to write comment log event to binlog.
     @ sql/log_event.cc
        Added code to create an object of 'Ignorable_log_event' for unrecognized
        sub-class. So that SLAVE SQL THREAD can ignore an unrecognized one.
     @ sql/log_event.h
        Added Ignorable_log_event class and its sub-class 'Comment_log_event' 
        to send over ignorable data to the slave.
     @ sql/mysqld.cc
        Added --binlog_ignorable_event option for mysqld to
        indicate if allow writing ignorable log event into
        binary log. The option is off by default.
     @ sql/set_var.cc
        Added code to add sys_binlog_ignorable_event for
        setting system var 'binlog_ignorable_event'.
     @ sql/slave.cc
        Added code to handle recognized sub-class of Ignorable_log_event
        according to its logic and treat unrecognized sub-class as its
        base class 'Ignorable_log_event' and ignore it after update
        the positions of the relay log.
     @ sql/sql_class.h
        Added code to define system var 'binlog_ignorable_event'
[19 May 2010 9:46] Daogang Qu
Build in WL#4033.
[28 May 2010 10:24] 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/109461

3185 Dao-Gang.Qu@sun.com	2010-05-28
      WL #4033 Support for informational log events
      Record original SQL statement in RBR binlog (Bug #50935)
      
      Adding a new ignorable event, and its incremental sub-hierarchy,
      which have as default that if their type code are not recognized,
      these events are just ignored and replication keeps going as usual.
      
      Record original SQL statement in RBR binlog by ignorable log event,
      which can be displayed with its row event as comment by 
      "SHOW BINLOG EVENTS" and 'MYSQLBINLOG DUMP'. 
     @ mysql-test/extra/binlog_tests/binlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated due to the patch of WL#4033.
     @ mysql-test/extra/rpl_tests/rpl_log.test
        Updated due to the patch of WL#4033.
     @ mysql-test/include/show_binlog_events.inc
        Updated due to the patch of WL#4033.
     @ mysql-test/include/show_binlog_events2.inc
        Updated due to the patch of WL#4033.
     @ mysql-test/r/ctype_cp932_binlog_stm.result
        Updated due to the patch of WL#4033.
     @ mysql-test/r/flush_block_commit_notembedded.result
        Updated due to the patch of WL#4033.
     @ mysql-test/r/multi_update.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/t/binlog_incident.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/binlog/t/binlog_killed_simulate.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_deadlock_innodb.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_heartbeat.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_ignorable_event.result
        Test result for wl#4033 and Bug#50935.
     @ mysql-test/suite/rpl/r/rpl_known_bugs_detection.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_basic_11bugs.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_conflicts.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_drop.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_log.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_row_log_innodb.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_slave_skip.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/r/rpl_stm_log.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_ignorable_event.test
        Added test to verify if the original SQL statement is displayed
        with its row event as comment in RBR by SHOW BINLOG EVENTS.
     @ mysql-test/suite/rpl/t/rpl_row_drop.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_row_flsh_tbls.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl/t/rpl_stm_flsh_tbls.test
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_multi.result
        Updated due to the patch of WL#4033.
     @ mysql-test/suite/rpl_ndb/r/rpl_truncate_7ndb.result
        Updated due to the patch of WL#4033.
     @ mysql-test/t/ctype_cp932_binlog_stm.test
        Updated due to the patch of WL#4033.
     @ mysql-test/t/mysqlbinlog.test
        Updated due to the patch of WL#4033.
     @ mysql-test/t/mysqlbinlog2.test
        Updated due to the patch of WL#4033.
     @ sql/log.cc
        Added code to write Rows_query log event to binlog before table map.
     @ sql/log_event.cc
        Added code to create an object of 'Ignorable_log_event' for unrecognized
        sub-class. So that SLAVE SQL THREAD can ignore an unrecognized one.
     @ sql/log_event.h
        Added Ignorable_log_event class and its sub-class 'Rows_query_log_event' 
        to send over ignorable data to the slave.
     @ sql/mysqld.cc
        Added --binlog-rows-query-log-events option for mysqld to
        indicate if allow writing Rows_query log event into
        binary log. The option is off by default.
     @ sql/set_var.cc
        Added code to add sys_binlog_rows_query_log_events for
        setting system var 'binlog_rows_query_log_events'.
     @ sql/slave.cc
        Added code to handle recognized sub-class of Ignorable_log_event
        according to its logic and treat unrecognized sub-class as its
        base class 'Ignorable_log_event' and ignore it after update
        the positions of the relay log.
     @ sql/sql_class.h
        Added code to define system var 'binlog_rows_query_log_events'
     @ sql/sql_insert.cc
        Added code to write Rows_query log event into binlog for 'insert delayed ......'
[2 Jul 2010 2:41] Daogang Qu
It's fixed in WL#4033.
[9 Sep 2010 7:44] Simon Mudd
Patch approved but I don't see this appear in any mysqld version. Is this going to be rolled out anywhere, or has this been done already? If so in which version.

I'm running 5.1.47 on some boxes and had a replication failure which just showed this:

100909  9:03:13 [Warning] Slave SQL: Could not execute Update_rows event on table eventstats_201008.UfiDemand_20100831; Can't find record in 'UfiDemand_20100831', Er
ror_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDe
mand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't fin
d record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_cod
e: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20
100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find record in 'UfiDemand_20100831', Error_code: 1032; Can't find recor
d in 'UfiDemand_20100831', Error_code: 1032; Can't find rec

So no "original SQL" on that version.

In fact I don't really care if we get the original SQL or the "pseudo SQL" that is the result of the RBR replication, but seeing more information than the line above would help diagnose problems more quickly.
[3 Nov 2010 9:49] Luis Soares
Hi Simon, 
  The reason why the link does not resolve, it's because it
points at our internal build server. Nevertheless, the pushed
patch was also Cc'ed to commits@:

  - http://lists.mysql.com/commits/117283

  The patch will be released in one of our future releases.

  With respect to your note, please, read in WL#4033,
  section: "High Level Architecture":

"
  SERVER OPTION: --binlog-rows-query-log-events
----------------------------------------------------------------------------
Tells the master to write Rows_query_log events to the binary log.

    * Variable Name: binlog_rows_query_log_events
    * Scope: Global & Session
    * Access Type: Dynamic
    * Data Type: bool
    * Default Value: OFF

NOTE. Session values allows to comment only some selected statements:
    ...
    SET SESSION binlog_rows_query_log_events=ON;
    ... statements to be commented ...
    SET SESSION binlog_rows_query_log_events=OFF;
    ... statements not to be commented ...
"

  Thank you for your feedback.
[24 Mar 2011 15:01] Jon Stephens
Already documented in the 5.6.2 changelog as part of Wl#4033/WL#5404 (qq.v.). Closed.