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.
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.