MySQL Bugs: #7793: mysqlbinlog produces incorrect queries

Bug #7793	mysqlbinlog produces incorrect queries
Submitted:	11 Jan 2005 7:36	Modified:	16 Jan 2005 23:10
Reporter:	Sergey Petrunya
Status:	Closed
Category:	Client	Severity:	S3 (Non-critical)
Version:	4.1	OS:
Assigned to:	Guilhem Bichot	Target Version:

Description:
mysqlbinlog output contains incorrect queries.

How to repeat:
run 
./mysql-test-run t/heap.test
../client/mysqlbinlog
../client/mysqlbinlog var/log/master-bin.000001 > binloq.sql
Then start the server, start the client, do "source binloq.sql"  and get lots of errors
like this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'binary' at
line 1

The queries are replicated correctly, only mysqlbinlog output is wrong. Changing category
to "command line clients".

i had a similar problem with binlogs containing lots of BLOB updates

Thanks Sergey; the bug exists in 4.1 too but hasn't been noticed because heap.test does
less tests in 4.1 than in 5.0.
I'm fixing it now. In fact the offending query is:
SET @`char`:=_binary 0x7B COLLATE binary;
and the fix is adding backticks the "binary" which is a reserved word.
But it's a bug that @char has binary collation, I'm filing a bug report for that.

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:

Fixed in 4.1.10 and 5.0.3 in
ChangeSet@1.2117.83.1, 2005-01-12 19:42:46+01:00, guilhem@mysql.com
  Fix for BUG#7793 "mysqlbinlog produces incorrect queries":
  when printing SET @var in mysqlbinlog, backtick the collation (as BINARY is a reserved
word)