Bug #59124 Queries are binlogged with wrong charset
Submitted: 23 Dec 2010 9:56 Modified: 24 Dec 2010 0:04
Reporter: Libing Song Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1, 5.6.2 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[23 Dec 2010 9:56] Libing Song
Description:
Statements binlogged in events are usually the original query string from client, so character_set_client is always binlogged into the query events.
It guarantees the queries can be parsed correctly by slave.

However, the queries binlogged in events are sometimes not the original query string from client. They are formed by server in utf8 character set, but the 
value of character_set_client is still the original client's character set.
It makes slave not only diverging from master but abort as well.

A couple of statements are not binlogged with the original query.
CREATE TABLE ... SELECT
CREATE TABLE ... LIKE on row mode.
LOAD DATA.
CREATE [SP|SF|TRI] ...
SET PASSWORD
EXECUTE a prepared statement.
Statements need SP parameters

How to repeat:
source include/master-slave.inc;
source include/have_binlog_format_row.inc;

SET NAMES 'gbk';
--echo "中㈠"
let $table_name= `SELECT unhex('d6d0a2e5')`;

eval CREATE TABLE $table_name(c1 INT);
eval INSERT INTO $table_name VALUES(1);

show tables;
eval select * from $table_name;

sync_slave_with_master;
SET NAMES 'gbk';
show tables;
eval select * from $table_name;

connection master;
--echo # "中㈡"
let $table_name= `SELECT unhex('d6d0a2e6')`;

eval CREATE TABLE $table_name(c1 INT) SELECT 1 AS c2;

show tables;
eval select * from $table_name;

sync_slave_with_master;
show tables;
eval select * from $table_name

Suggested fix:
The query recreated by server should be converted into client's character set before it will be binlogged.
[24 Dec 2010 0:04] Sveta Smirnova
Thank you for the report.

Verified as described.