Bug #7793 mysqlbinlog produces incorrect queries
Submitted: 11 Jan 2005 6:36 Modified: 16 Jan 2005 22:10
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.1 OS:
Assigned to: Guilhem Bichot CPU Architecture:Any

[11 Jan 2005 6:36] Sergey Petrunya
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
[11 Jan 2005 7:13] Sergey Petrunya
The queries are replicated correctly, only mysqlbinlog output is wrong. Changing category to "command line clients".
[11 Jan 2005 12:10] Hartmut Holzgraefe
i had a similar problem with binlogs containing lots of BLOB updates
[12 Jan 2005 15:44] Guilhem Bichot
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.
[16 Jan 2005 22:10] 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:

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)