Bug #62201 mysqlbinlog creates invalid sql when using --verbose option
Submitted: 18 Aug 2011 14:43 Modified: 17 Sep 2012 15:37
Reporter: charl retief Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.5.15, 5.1.60 OS:Linux (Ubuntu Server 10.04)
Assigned to: CPU Architecture:Any

[18 Aug 2011 14:43] charl retief 
Description:
When using the mysqlbinlog program with --verbose option invalid SQL can be produced when comments appear inside BINLOG '...' statements. One way this can happen is when a function updates data and this function call is used to do a INSERT ... SELECT statement.

I have created a sample database to illustrate a test case where this problem occurs. (see testcase.sql)
I have also attached the full output from: mysqlbinlog --database="test123" --verbose mysql-bin.000001 > binlog_output.sql

Below is an extract of the invalid sql statement created:

BINLOG '
9x1NThMBAAAAMgAAAMINAAAAACUAAAAAAAEAB3Rlc3QxMjMABnRhYmxlMQABDwIKAAE=
9x1NThMBAAAAMgAAAPQNAAAAACYAAAAAAAEAB3Rlc3QxMjMABnRhYmxlMgABDwIKAAE=
9x1NThgBAAAAKwAAAB8OAAAAACUAAAAAAAAAAf///gNhYmP+BmFiY2FiYw==
### UPDATE test123.table1
### WHERE
###   @1='abc'
### SET
###   @1='abcabc'
9x1NThcBAAAAJQAAAEQOAAAAACYAAAAAAAEAAf/+BmFiY2FiYw==
'/*!*/;
### INSERT INTO test123.table2
### SET
###   @1='abcabc'

How to repeat:
1) mysql < testcase.sql
2) mysqlbinlog --database="test123" --verbose mysql-bin.000001 > test.sql
3) mysql < test.sql
[18 Aug 2011 14:44] charl retief 
Test case

Attachment: testcase.sql (text/x-sql), 608 bytes.
[18 Aug 2011 14:45] charl retief 
broken sql output from running mysqlbinlog --verbose

Attachment: binlog_output.sql (text/x-sql), 3.46 KiB.
[18 Aug 2011 17:58] Sveta Smirnova 
Thank you for the report.

Verified as described.
[17 Sep 2012 15:37] 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 major MySQL release, currently tagged 5.7.0. Not fixed in 5.6 or earlier.

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