Bug #36391 mysqlbinlog creates invalid charset statements
Submitted: 29 Apr 2008 4:11 Modified: 13 Feb 2009 20:17
Reporter: Cherie Bergen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.1.23-rc-log, 5.0, 5.1, 6.0 BK OS:Linux (Centos 5)
Assigned to: Alfranio Tavares Correia Junior CPU Architecture:Any

[29 Apr 2008 4: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 13: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 3:57] Cherie Bergen 
Verified as working in 5.1.24.  Thank you!
[1 May 2008 7:36] Sveta Smirnova 
Thank you for the feedback.

Closed as "Can't repeat" regarding to last comment.
[2 May 2008 17: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 16:24] Sveta Smirnova 
Workaround:

mysqlbinlog master-bin.000001 | sed 's/\/\*!\\C latin1 \*\/\/\*!\*\//set names latin1/g'
[5 May 2008 17:16] Omer Barnir 
workaround: set the session sqlmode to '0 if the mysql client and source the dump file
[5 May 2008 17:40] Sveta Smirnova 
SET @@session.sql_mode=0; would be overloaded by next SET @@session.sql_mode=1048576/*!*/; call.
[6 May 2008 8:46] Sveta Smirnova 
Category changed as replication looks more appropriate for this.
[11 Aug 2008 21:36] Hartmut Holzgraefe 
Actual problem is http://bugs.mysql.com/38731 which has a proposed patch on it
[23 Jan 2009 11: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 2009 11:21] Alfranio Tavares Correia Junior 
The patch proposed by Hartmut Holzgraefe in BUG#38731 is ok.
[23 Jan 2009 13: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 2009 20: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 2009 1: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 2009 9: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 2009 11: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 2009 20: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 2009 14: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 2009 16: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 2009 18: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)