Bug #36391 mysqlbinlog creates invalid charset statements
Submitted: 29 Apr 2008 6:11 Modified: 13 Feb 21:17
Reporter: Cherie Bergen
Status: Closed
Category:Client Severity:S2 (Serious)
Version:5.1.23-rc-log, 5.0, 5.1, 6.0 BK OS:Linux (Centos 5)
Assigned to: Alfranio Correia Target Version:5.1+
Triage: Triaged: D3 (Medium)

[29 Apr 2008 6:11] Cherie Bergen 
Description:
I've been testing incremental restores from binlogs on a new 5.1.23 server, and I seem to
be running into problems with /*!\C utf8 */; 

[root@xxxx mysql]# mysql -u xxxxxxx -p < /tmp/test.sql
Enter password:
ERROR 1064 (42000) at line 12: 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 '' at line
1

tmp/test.sql is bombing out at the /*!\C utf8 */; command. If I comment it out, the
script runs fine. Or if I convert it to \C utf8 ; it runs fine.

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#080423 0:30:01 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.23-rc-log
created 080423 0:30:01
# at 106
#080423 9:29:32 server id 1 end_log_pos 233 Query thread_id=154914 exec_time=0
error_code=0
use main;
SET TIMESTAMP=1208968172;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1;
SET @@session.sql_mode=0;
/*!\C utf8 */;

Binaries are mysql-5.1.23-rc-linux-x86_64-glibc23

character_set_client | utf8
character_set_connection | utf8
character_set_database | utf8
character_set_filesystem | binary
character_set_results | utf8
character_set_server | utf8
character_set_system | utf8
character_sets_dir | /usr/local/mysql-5.1.23-rc-linux-x86_64-glibc23/share/charsets/ |
+--------------------------+-----------------------------------------------------------------+

How to repeat:
create file with this in it:

SET @@session.sql_mode=0;
/*!\C utf8 */;

mysql < filename

and see the error generated.
[29 Apr 2008 15:22] Sveta Smirnova 
Thank you for the report.

I can not repeat described behavior. Please try with current version 5.1.24 and if
problem still esists provide output of \s command issued in mysql command line client.
[30 Apr 2008 5:57] Cherie Bergen 
Verified as working in 5.1.24.  Thank you!
[1 May 2008 9:36] Sveta Smirnova 
Thank you for the feedback.

Closed as "Can't repeat" regarding to last comment.
[2 May 2008 19:40] Sveta Smirnova 
Another user found problem is still repeatable if sql mode NO_BACKSLASH_ESCAPES is
specified. So set bug to "Verified"

To repeat:

$cat rpl_bug36391-master.opt
--sql_mode=NO_BACKSLASH_ESCAPES

$cat rpl_bug36391.test
--source include/master-slave.inc

drop table if exists t1;

create table t1(id int);

show tables;

show master status;

flush logs;

--exec $MYSQL_BINLOG $MYSQL_TEST_DIR/var/log/master-bin.000001 | $MYSQL test
[5 May 2008 18:24] Sveta Smirnova 
Workaround:

mysqlbinlog master-bin.000001 | sed 's/\/\*!\\C latin1 \*\/\/\*!\*\//set names latin1/g'
[5 May 2008 19:16] Omer BarNir 
workaround: set the session sqlmode to '0 if the mysql client and source the dump file
[5 May 2008 19:40] Sveta Smirnova 
SET @@session.sql_mode=0; would be overloaded by next SET @@session.sql_mode=1048576/*!*/;
call.
[6 May 2008 10:46] Sveta Smirnova 
Category changed as replication looks more appropriate for this.
[11 Aug 2008 23:36] Hartmut Holzgraefe 
Actual problem is http://bugs.mysql.com/38731 which has a proposed patch on it
[23 Jan 12:18] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63888

2752 Alfranio Correia	2009-01-23
      BUG#36391: "mysqlbinlog creates invalid charset statements"
      
      The fix for BUG#20103 "Escaping with backslash does not work as expected"
      was implemented too greedy though in that it not only changes the behavior
      of backslashes within strings but in general, so disabling command shortcuts
      like \G or \C
      (which in turn leads to Bug #36391: "mysqlbinlog creates invalid charset
statements").
      
      The fix allows the escaping with backslash to take place only inside a string,
      thus enabling the execution of command shortcuts and presevering the fix for
BUG#20103.
[23 Jan 12:21] Alfranio Correia 
The patch proposed by Hartmut Holzgraefe in BUG#38731 is ok.
[23 Jan 14:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63900

2752 Alfranio Correia	2009-01-23
      BUG#36391: "mysqlbinlog creates invalid charset statements"
      
      The fix for BUG#20103 "Escaping with backslash does not work as expected"
      was implemented too greedy though in that it not only changes the behavior
      of backslashes within strings but in general, so disabling command shortcuts
      like \G or \C 
      (which in turn leads to Bug #36391: "mysqlbinlog creates invalid charset
statements").
      
      The fix allows the escaping with backslash to take place only inside a string, 
      thus enabling the execution of command shortcuts and presevering the fix for
BUG#20103.
[27 Jan 21:50] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64203

2749 Alfranio Correia	2009-01-27
      BUG#36391: "mysqlbinlog creates invalid charset statements"
            
      The fix for BUG#20103 "Escaping with backslash does not work as expected"
      was implemented too greedy though in that it not only changes the behavior
      of backslashes within strings but in general, so disabling command shortcuts
      like \G or \C (which in turn leads to Bug #36391: "mysqlbinlog creates invalid
charset
      statements").
            
      The fix allows the escaping with backslash to take place only inside a string, 
      thus enabling the execution of command shortcuts and presevering the fix for
      BUG#20103.
[28 Jan 2:19] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64222

2983 Alfranio Correia	2009-01-28 [merge]
      BUG#36391 merge 5.1-bugteam --> 6.0-bugteam
[3 Feb 10:41] Bugs System 
Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source
revid:sergefp@mysql.com-20090128192114-jm5r3gezr59oki5x) (merge vers: 5.1.32) (pib:6)
[4 Feb 12:17] Bugs System 
Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version
source revid:alfranio.correia@sun.com-20090128011812-0sb3siu3jfn7ew25) (merge vers:
6.0.10-alpha) (pib:6)
[13 Feb 21:17] Paul DuBois 
Noted in 5.1.32, 6.0.10 changelogs.

The mysql client sometimes improperly interpreted string escape
sequences in non-string contexts.
[17 Feb 15:59] Bugs System 
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef)
(version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers:
5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 17:47] Bugs System 
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau)
(version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers:
5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 19:23] Bugs System 
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j)
(version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers:
5.1.32-ndb-6.2.17) (pib:6)