Bug #73091 A character set error may interrupt replication
Submitted: 24 Jun 2014 11:12 Modified: 15 Jul 2014 16:20
Reporter: Hao Liu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.5.39, 5.6.21, 5.1.74 OS:Any
Assigned to: CPU Architecture:Any

[24 Jun 2014 11:12] Hao Liu
Description:
Two statements may interrupt replication. Two statements are below:

CREATE TABLE `za` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_code` varchar(255) DEFAULT NULL COMMENT '订单号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk ;

create table test.zb AS 
select 
id 订单日,
order_code 卖家NICK
from test.za;

Look at some variables:

root@(none) 07:07:17>show variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | gbk                         |
| character_set_connection | gbk                         |
| character_set_database   | gbk                         |
| character_set_filesystem | binary                      |
| character_set_results    | gbk                         |
| character_set_server     | gbk                         |
| character_set_system     | utf8                        |
| character_sets_dir       | /u01/my3706/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

root@(none) 07:07:55>show global variables like '%char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | gbk                         |
| character_set_connection | gbk                         |
| character_set_database   | gbk                         |
| character_set_filesystem | binary                      |
| character_set_results    | gbk                         |
| character_set_server     | gbk                         |
| character_set_system     | utf8                        |
| character_sets_dir       | /u01/my3706/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

root@(none) 07:08:33>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

How to repeat:
See the descriptions. After executing the two statements, you can see the output of 'show slave status' like this:

root@(none) 03:45:21>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mya152054.sqa.cm6
                  Master_User: repl
                  Master_Port: 3706
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000129
          Read_Master_Log_Pos: 2144
               Relay_Log_File: relaylog.000018
                Relay_Log_Pos: 1951
        Relay_Master_Log_File: mysql-bin.000129
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1064
                   Last_Error: Error '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 '鍗栧?NICK` varchar(255) DEFAULT NULL COMMENT '璁㈠崟鍙?
) DEFAULT CHARSET=' at line 2' on query. Default database: 'test'. Query: 'CREATE TABLE `zb` (
  `订单日` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '主键',
  `卖家NICK` varchar(255) DEFAULT NULL COMMENT '订单号'
) DEFAULT CHARSET=gbk'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1805
              Relay_Log_Space: 2482
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1064
               Last_SQL_Error: Error '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 '鍗栧?NICK` varchar(255) DEFAULT NULL COMMENT '璁㈠崟鍙?
) DEFAULT CHARSET=' at line 2' on query. Default database: 'test'. Query: 'CREATE TABLE `zb` (
  `订单日` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '主键',
  `卖家NICK` varchar(255) DEFAULT NULL COMMENT '订单号'
) DEFAULT CHARSET=gbk'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3135638954
1 row in set (0.00 sec)

You can also look at the output of binlog with mysqlbinlog.

Suggested fix:
look at the function: select_create::binlog_show_create_table

I think it is wrong to use system_charset_info everywhere.
[15 Jul 2014 16:20] Sveta Smirnova
Thank you for the report.

Verified as described.

Workaround: use statement-based replication.
[15 Jul 2014 16:21] Sveta Smirnova
test case for MTR

Attachment: rpl_bug73091.test (application/octet-stream, text), 425 bytes.

[15 Jul 2014 16:21] Sveta Smirnova
option file for master

Attachment: rpl_bug73091-master.opt (application/octet-stream, text), 27 bytes.

[15 Jul 2014 16:22] Sveta Smirnova
option file for slave

Attachment: rpl_bug73091-slave.opt (application/octet-stream, text), 27 bytes.