Bug #3875 mysqlbinlog produces wrong ouput if query uses variables containing quotes

• Submitted: 24 May 2004 17:58
• Modified: 3 Jun 2004 23:42
• Reporter: Sergey Petrunya
• Status: Closed
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 4.1
• Assigned to: Guilhem Bichot
• CPU Architecture: Any

[24 May 2004 17:58] Sergey Petrunya

Description:
mysqlbinlog produces wrong ouput if query uses variables that contain quotes.

Replication of such queries works correctly though, so the problem is within mysqlbinlog, not mysqld.

How to repeat:
Start the server with binary log turned on.
Run the following: 

create table t1(n char(30));
set @var1= "';aaa";
insert into t1 values (@var1);

Then shut the server down an run mysqlbinlog:

mysqlbinlog var/log/master-bin.000001 | grep -v "^#"

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
use test;
SET TIMESTAMP=1085304220;
create table t1(n char(30));
SET @var1:='';aaa';
           ^^^^ note this. 
SET TIMESTAMP=1085304220;
insert into t1 values (@var1);

Suggested fix:
Make mysqlbinlog properly escape quotes.

[24 May 2004 18:05] Sergey Petrunya

mysqlbinlog also incorrectly prints variables with ASCII-incompatible charsets:
Running the following:
create table t2 (c char(30)) charset=ucs2;
set @v=convert('abc' using ucs2);
insert into t2 values (@v);

In mysqlbinlog output one finds:
SET @v:='';
insert into t2 values (@v);

[24 May 2004 18:08] Sergey Petrunya

Neither does mysqlbinlog quote variable names: 
SET @`a b`='hello';
INSERT INTO t1 VALUES(@`a b

[24 May 2004 18:10] Sergey Petrunya

Neither does mysqlbinlog quote variable names: 
The  statements:
SET @`a b`='hello';
INSERT INTO t1 VALUES(@`a b`);

Produce the following in mysqlbinlog output:
SET @a b:='hello';
INSERT INTO t1 VALUES(@`a b`);

[24 May 2004 18:34] Guilhem Bichot

Yes, I have a patch for this, not pushed yet. Will be in 4.1.3.
Thank you!

[24 May 2004 18:36] Guilhem Bichot

ah, the stuff with ucs2 I don't know if I fixed it in my patch. Will have to verify.

[3 Jun 2004 23:42] Guilhem Bichot

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet@1.1843.14.1, 2004-06-03 23:17:18+02:00, [e-mail]
  Implementation of WL#1824 "Add replication of character set variables in 4.1"
If you want you can have a look at the updated user_var.test to see how ucs2 vars display now in mysqlbinlog.
And thank you a lot for reporting these bugs.