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:
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

[28 Jun 2007 14:49] Martin Friebe
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?
[28 Jun 2007 15:00] MySQL Verification Team
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
[10 Oct 2013 16:48] Paul DuBois
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.