MySQL Bugs: #23619: Incorrectly escaped multibyte characters in binary log break replication

Bug #23619	Incorrectly escaped multibyte characters in binary log break replication
Submitted:	25 Oct 2006 10:28	Modified:	23 Jan 2007 18:14
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.0-bk, 5.1-bk (2006 10 24)	OS:	Any (Any)
Assigned to:	Alexander Barkov	CPU Architecture:	Any
Tags:	bfsm_2006_11_02, bfsm_2007_01_18, charsets, replication, sjis, stored procedure

Description:
If a connection, which has 'binary' character set executes a stored procedure, which writes multibyte data, incorrectly escaped entries are written to binary log, therefore causing syntax errors and replication termination. 

Example character may be character (150,92) - ending with \. 

How to repeat:
Run attached MySQL testcase. 

Suggested fix:
Escape entries properly.

testcase...

Attachment: rpl_binary_sjis.test (application/octet-stream, text), 670 bytes.

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/15077

ChangeSet@1.2297, 2006-11-09 14:27:34+04:00, bar@mysql.com +5 -0
  Bug#23619 Incorrectly escaped multibyte characters in binary log break replication
  Problem: when embedding a character string with introducer with charset X
  into a SQL query which is generally in character set Y, the string constants
  were escaped according to their own character set (i.e.X), then after reading
  such a "mixed" query from binlog, the string constants were unescaped
  using character set of the query (i.e. Y), instead of X, which gave wrong
  results or even syntax errors with tricky charsets (e.g. sjis)
  Fix: when embedding a string constant of charset X into a query of charset Y,
  the string constant is now escaped according to character Y, instead of
  its own character set X.

ok to push

I had same issue with GBK characters.
Env:
Server: 5.0.24-max-log, ENGINE=MyISAM DEFAULT CHARSET=gbk;

JDBC Driver, tried both 3.x and 5.x

Using PreparedStatement
The master can execute the insert/update without problems, but when slave reads events from binlog, it get a wrong SQL statement without correct escaping.

Test codes:
        byte[] bytes = new byte[]{(byte)0xbf,(byte)0x5c};
        String test = new String(bytes,"GBK");
or 
        String test = "診";

When inserting such a single character to master, slave will be terminated because of invalid sql.

Hugo

Hugo,

What does "SHOW VARIABLES LIKE 'character_set'" display for you on master
using the same JDBC connection?

Thanks!

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix for 5.0.30 and 5.1.14.

This bug is not fixed in the versions mentioned above due to human error.

Noted in 5.0.36, 5.1.15 changelogs.

Bug #26711 was marked as duplicate of this one

Yes, I have tested the patch and found it fixed #26711 as well. Thank you

Hi, after a more thorough test I found that the patch doesn't fix bug #26711 in some situation. Here are instructions for repeating the bug.

First, create a database as follows.

mysql> create database test charset gbk;

Note that the charset of the database must be other than latin1 to show this bug.

Then create a table and insert some data.

mysql> use test;
mysql> CREATE TABLE `test_blob` (
   `a` blob
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
mysql> insert into test_blob values(0xEE00);

And then dump the data and reload it.

$ mysqldump -T . -u root test test_blob;
$ mysql -u root test
mysql> truncate test_blob;
mysql> load data local infile '/tmp/test_blob.txt' into table test_blob;  
mysql> select hex(a) from test_blob;
+--------+
| hex(a) |
+--------+
| EE5C30 | 
+--------+
1 row in set (0.00 sec)

The key point is to set the charset of the database to gbk. If it is latin1, all will be OK.

Thank you for the additional comment.

Will mark bug #26711 as verified to let developer who will fix it decide if these 2 are same or not.

Bug #26711 contains test case form last comment.