MySQL Bugs: #29422: mysqlbinlog incorectly quotes user variable named <backtick>

Bug #29422	mysqlbinlog incorectly quotes user variable named <backtick>
Submitted:	28 Jun 2007 14:49	Modified:	10 Oct 2013 16:48
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	mysqlbinlog, qc

Description:
mysql allows quoting of user_variable-names with any kind of quote

set @a= 22;
select @a, @"a", @'a', @`a`;
+------+------+------+------+
| @a   | @"a" | @'a' | @`a` |
+------+------+------+------+
|   22 |   22 |   22 |   22 |
+------+------+------+------+

if you use the backtick as name for a user variable, mysqlbinlog will be broken.

 insert into some_table select @"`";

 mysqlbinlog /path/bin.000001
#7628 15:9:28 server id 1  end_log_pos 131      User_var
SET @```:=NULL/*!*/;
....
insert into t1 select @"`"/*!*/;

This does not apply to replication, as the variables are not stored in actual sql syntax. A replication slave will work.

But the binlog will be useless for restoring data to a specific point in time.

So the Problem is the mysqlbinlog command line tool, that does generate invalid sql.

How to repeat:
drop table if exists t1;
create table t1 (a int);
set @"`"=99;
insert into t1 select @"`";
insert into t1 select @````;

# on a shell run
mysqlbinlog /path/to/binlog/log.00000x

and find this (for both inserts):
SET @```:=99/*!*/;

Suggested fix:
Fix quoting in the mysqlbinlog cmd line tool

btw, I was unable to find a "client" category in the dropdown for categories. So I had to report it under server?

Thank you for the bug report. Verified as described.

# at 278
#7628 11:56:34 server id 1  end_log_pos 320     User_var
SET @```:=99/*!*/;
# at 320

Noted in 5.1.67, 5.5.29, 5.6.8 changelogs.

Backtick (`) characters were not always handled correctly in
internally generated SQL statements, which could sometimes lead to
errors on replication slaves or cause failure of restore operations
from binary log files.