replication

MySQL distributed message system

April 12, 2016 Architect, Architecture, hardware, MYSQL, replication, software No comments

Based on messages, we create mysql replication platforms , using async message to build strong distributed subscription system.

read this PDF : http://www.vmcd.org/docs/MySQL_async_message.pdf

MySQL checksum

August 19, 2015 MYSQL, replication, system No comments

how to use percona-tookit to verify mysql data

Read this PDF : mysql_checksum

MariaDB : Bug when add index on Partition table

April 23, 2015 Internals, MYSQL, replication No comments

Version: 5.5.5-10.0.10-MariaDB-log MariaDB Server

When I use mariadb as multi-source slave DB I met a strange problem . Creating index on a vary large partition table , Mariadb do — copy to tmp table on the slave side. It takes a long time and still not finish over 9 hours.

Primary : MySQL 5.6.16 —

add index on a partition table:

CREATE TABLE `track_raw_wap211_log` (
 `table_id` varchar(100) DEFAULT NULL,
 `page_id` varchar(100) DEFAULT NULL,
 `banner_id` varchar(100) DEFAULT NULL,
 `button_id` varchar(100) DEFAULT NULL,
 `test_id` varchar(100) DEFAULT NULL,
 `classfication` varchar(100) DEFAULT NULL,
 `request_refer` varchar(100) DEFAULT NULL,
 `request_url` text,
 `title` varchar(100) DEFAULT NULL,
 `user_id` varchar(100) DEFAULT NULL,
 `language` varchar(100) DEFAULT NULL,
 `event` varchar(100) DEFAULT NULL,
 `event_desc` varchar(100) DEFAULT NULL,
 `event_type` varchar(100) DEFAULT NULL,
 `log_version` varchar(100) DEFAULT NULL,
 `project` varchar(100) DEFAULT NULL,
 `log_time` varchar(100) DEFAULT NULL,
 `unicookie` varchar(100) DEFAULT NULL,
 `session_id` varchar(100) DEFAULT NULL,
 `session_duration` varchar(100) DEFAULT NULL,
 `resolution` varchar(100) DEFAULT NULL,
 `channel` varchar(100) DEFAULT NULL,
 `refer` text,
 `user_agent` text,
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `httplogtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `httpforwardip` varchar(100) DEFAULT NULL,
 `country` varchar(100) DEFAULT NULL,
 `province` varchar(100) DEFAULT NULL,
 `city` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `mapweidu` varchar(100) DEFAULT NULL,
 `mapjingdu` varchar(100) DEFAULT NULL,
 `sync_mysqlid` bigint(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`sync_mysqlid`,`create_time`),
 KEY `ix_create_time` (`create_time`),
 KEY `ix_httplogtime` (`httplogtime`),
 KEY `ix_httplogtime_userid` (`httplogtime`,`user_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=23217118 DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_time`))
 (PARTITION p20150318 VALUES LESS THAN (1426694400) ENGINE = InnoDB,
 PARTITION p20150319 VALUES LESS THAN (1426780800) ENGINE = InnoDB,
 PARTITION p20150320 VALUES LESS THAN (1426867200) ENGINE = InnoDB,
 PARTITION p20150321 VALUES LESS THAN (1426953600) ENGINE = InnoDB,
 PARTITION p20150322 VALUES LESS THAN (1427040000) ENGINE = InnoDB,
 PARTITION p20150323 VALUES LESS THAN (1427126400) ENGINE = InnoDB,
 PARTITION p20150324 VALUES LESS THAN (1427212800) ENGINE = InnoDB,
 PARTITION p20150325 VALUES LESS THAN (1427299200) ENGINE = InnoDB,
 PARTITION p20150326 VALUES LESS THAN (1427385600) ENGINE = InnoDB,
 PARTITION p20150327 VALUES LESS THAN (1427472000) ENGINE = InnoDB,
 PARTITION p20150328 VALUES LESS THAN (1427558400) ENGINE = InnoDB,
 PARTITION p20150329 VALUES LESS THAN (1427644800) ENGINE = InnoDB,
 PARTITION p20150330 VALUES LESS THAN (1427731200) ENGINE = InnoDB,
 PARTITION p20150331 VALUES LESS THAN (1427817600) ENGINE = InnoDB,
 PARTITION p20150401 VALUES LESS THAN (1427904000) ENGINE = InnoDB,
 PARTITION p20150402 VALUES LESS THAN (1427990400) ENGINE = InnoDB,
 PARTITION p20150403 VALUES LESS THAN (1428076800) ENGINE = InnoDB,
 PARTITION p20150404 VALUES LESS THAN (1428163200) ENGINE = InnoDB,
 PARTITION p20150405 VALUES LESS THAN (1428249600) ENGINE = InnoDB,
 PARTITION p20150406 VALUES LESS THAN (1428336000) ENGINE = InnoDB,
 PARTITION p20150407 VALUES LESS THAN (1428422400) ENGINE = InnoDB,
 PARTITION p20150408 VALUES LESS THAN (1428508800) ENGINE = InnoDB,
 PARTITION p20150409 VALUES LESS THAN (1428595200) ENGINE = InnoDB,
 PARTITION p20150410 VALUES LESS THAN (1428681600) ENGINE = InnoDB,
 PARTITION p20150411 VALUES LESS THAN (1428768000) ENGINE = InnoDB,
 PARTITION p20150412 VALUES LESS THAN (1428854400) ENGINE = InnoDB,
 PARTITION p20150413 VALUES LESS THAN (1428940800) ENGINE = InnoDB,
 PARTITION p20150414 VALUES LESS THAN (1429027200) ENGINE = InnoDB,
 PARTITION p20150415 VALUES LESS THAN (1429113600) ENGINE = InnoDB,
 PARTITION p20150416 VALUES LESS THAN (1429200000) ENGINE = InnoDB,
 PARTITION p20150417 VALUES LESS THAN (1429286400) ENGINE = InnoDB,
 PARTITION p20150418 VALUES LESS THAN (1429372800) ENGINE = InnoDB,
 PARTITION p20150419 VALUES LESS THAN (1429459200) ENGINE = InnoDB,
 PARTITION p20150420 VALUES LESS THAN (1429545600) ENGINE = InnoDB,
 PARTITION p20150421 VALUES LESS THAN (1429632000) ENGINE = InnoDB,
 PARTITION p20150422 VALUES LESS THAN (1429718400) ENGINE = InnoDB,
 PARTITION p20150423 VALUES LESS THAN (1429804800) ENGINE = InnoDB,
 PARTITION p20150424 VALUES LESS THAN (1429891200) ENGINE = InnoDB,
 PARTITION p20150425 VALUES LESS THAN (1429977600) ENGINE = InnoDB,
 PARTITION p20150426 VALUES LESS THAN (1430064000) ENGINE = InnoDB,
 PARTITION p20150427 VALUES LESS THAN (1430150400) ENGINE = InnoDB,
 PARTITION p20150428 VALUES LESS THAN (1430236800) ENGINE = InnoDB,
 PARTITION p20150429 VALUES LESS THAN (1430323200) ENGINE = InnoDB,
 PARTITION p20150430 VALUES LESS THAN (1430409600) ENGINE = InnoDB,
 PARTITION p20150501 VALUES LESS THAN (1430496000) ENGINE = InnoDB,
 PARTITION p20150502 VALUES LESS THAN (1430582400) ENGINE = InnoDB,
 PARTITION p20150503 VALUES LESS THAN (1430668800) ENGINE = InnoDB,
 PARTITION p20150504 VALUES LESS THAN (1430755200) ENGINE = InnoDB,
 PARTITION p20150505 VALUES LESS THAN (1430841600) ENGINE = InnoDB,
 PARTITION p20150506 VALUES LESS THAN (1430928000) ENGINE = InnoDB,
 PARTITION p20150507 VALUES LESS THAN (1431014400) ENGINE = InnoDB,
 PARTITION p20150508 VALUES LESS THAN (1431100800) ENGINE = InnoDB,
 PARTITION p20150509 VALUES LESS THAN (1431187200) ENGINE = InnoDB,
 PARTITION p20150510 VALUES LESS THAN (1431273600) ENGINE = InnoDB,
 PARTITION p20150511 VALUES LESS THAN (1431360000) ENGINE = InnoDB) */ 

create index idx_tmp on track_raw_wap211_log (log_time);

Slave: Mariadb – 5.5.5-10.0.15-MariaDB-log MariaDB Server

21407 | root | localhost | jkgj_log | Query | 34 | copy to tmp table | create index idx_tmp on track_raw_wap211_log (log_time) | 0.000 |  

drop index command still has this problem.

——————————————————————

When I repeat these steps on MariaDB – 10.0.16 everything is OK.

This bug has been reported to MariaDB:

https://mariadb.atlassian.net/browse/MDEV-8038

MySQL message subscription system

December 9, 2014 Architect, Architecture, MYSQL, replication, software No comments

Introducing MQ system based on MySQL (canal + roma)
Using this system to subscribe message between different platforms.

View this PDF roma_system
Also download this PDF from slideshare

Good news for MySQL HA solutions

June 9, 2014 Architecture, MYSQL, replication No comments


Oracle Clusterware is portable cluster software that allows clustering of independent servers so that they cooperate as a single system. Oracle Clusterware was first released with Oracle Database 10g Release 1 as the required cluster technology for the Oracle multi-instance database,  Oracle Real Application Clusters (RAC).

Oracle Clusterware 12c Release 1 is the integrated foundation for Oracle Real Application Clusters (RAC) and the High Availability (HA) and resource management framework for all applications on any major platform.  Oracle Clusterware 12c builds on the innovative technology introduced with Oracle Clusterware 11g by providing comprehensive multi-tiered HA and resource management for consolidated environments. The idea is to leverage Oracle Clusterware in the cloud to provide enterprise-class resiliency where required and dynamic, online allocation of compute resources where needed, when needed.


NEW in Oracle Clusterware 12c

  •  Oracle Flex Cluster 12c: a new clustering solution for agile, large-scale deployments
  •  Server Categorization: support for heterogeneous server pool management
  •  Cluster Configuration Policy Sets: automated management of business critical workloads
  •  Utility Cluster Concept: centralized, data center management cluster
  •  Oracle Generic Agent: a simple way to integrate applications into Oracle Clusterware
  •  Oracle Grid Infrastructure Generic standalone and Bundled Agents: 
    • Currently available for Siebel, Oracle GoldenGate, Peoplesoft, Apache and MySQL
    • Currently supported on AIX, Solaris and Linux

From MySQL Blog

MySQL has an extensive range of high-availability solutions to suit many different use cases and deployment needs.  This list spans from the time-tested – yet continuously-improved – MySQL replication to the just-released MySQL Fabric, giving users many certified solutions for highly available MySQL deployments.  The list is growing yet again, with Oracle Clusterware adding support for MySQL.
Oracle’s Clusterware product is the foundation for the Oracle RAC, and has been battle-tested for high availability support for Oracle database, as well as other Oracle applications.  This technology is now available as part of the MySQL Enterprise subscription, and – like all Oracle commercial products – is freely available for evaluation purposes.  This post will explain Oracle Clusterware architecture and the benefits to MySQL users, and will be followed by a later post focusing on how to deploy Clusterware agents with MySQL.

Link:http://www.oracle.com/technetwork/database/database-technologies/clusterware/overview/index.html

news in mysqlbinlog – Back Up Master Binary Log Files

May 13, 2014 Architecture, MYSQL, replication No comments

news in mysqlbinlog – Back Up Master Binary Log Files

从mysql5.6开始 mysqlbinlog开始支持远程读取master主机的binlog写入本地,极大的加强了binlog的备份策略,由于在mysql cluster复制环境中,binlog的存在极大的决定的数据恢复的完整性,所以binlog的备份显得特别重要。在诸多HA方案中,例如MHA,使用主库的binlog去恢复主备库之间的数据差,在主库物理机器down机无法重启的情况下,binlog的备份可以直接用来recover slave.所以这一特性提升mysql 容灾级别,使得mysql的灾备方案显得不是那么的单调唯一。

使用”–raw”,”–read-from-remote-server” 选项可以直接控制读取方式与读取server,可以采用管理机器统一读取多master binlog。
Facebook 采用类似semi-sync的方式重构了mysqlbinlog用来替代semi-sync方式的slave机器,达到多份复制的目的。

"We extended mysqlbinlog to speak Semisync protocol. The reason of the enhancement is that we wanted to use "semisync mysqlbinlog" as a replacement of local semisync slaves. We usually run slaves on remote datacenters, and we don't always need local slaves to serve read requests / redundancy. On the other hand, as described at above "Requirements for Semisync Deployment" section, in practice at least two local semisync readers are needed to make 
semisync work. We didn't like to run additional two dedicated slaves per master just for semisync. So we invented semisync mysqlbinlog and use it instead of semisync slaves, as shown in the below figure."

我们采用mysqlbinlog的这种方式备份多台master的binlog.配合MHA的异地binlog复制,以达到最小的数据丢失。

[root@pajk-super-master /usr/local/dbadmin/backup]
#nohup python binlog_backup_main.py &
#ps -ef | grep -i daemon
dbus      1056     1  0 May06 ?        00:00:00 dbus-daemon --system
root     24010 32696  0 10:58 pts/0    00:00:00 binlog_backup_daemon all    
root     24319 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.115':'3306' 
root     24330 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.116':'3306' 
root     24341 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.117':'3306' 

[root@pajk-super-master /usr/local/dbadmin/backup]
#ls -ltr /tmp/backup/binlog_backup/10.0.128.115.3306/
total 250908
-rw-r--r-- 1 root root     27732 May 13 10:12 mysql-bin.000001
-rw-r--r-- 1 root root   1063490 May 13 10:12 mysql-bin.000002
-rw-r--r-- 1 root root       126 May 13 10:12 mysql-bin.000003
-rw-r--r-- 1 root root       143 May 13 10:12 mysql-bin.000005
-rw-r--r-- 1 root root     14000 May 13 10:12 mysql-bin.000004
-rw-r--r-- 1 root root     64918 May 13 10:12 mysql-bin.000006
-rw-r--r-- 1 root root   1216094 May 13 10:12 mysql-bin.000007
-rw-r--r-- 1 root root       143 May 13 10:12 mysql-bin.000008
-rw-r--r-- 1 root root 183388823 May 13 10:12 mysql-bin.000009
-rw-r--r-- 1 root root  20839355 May 13 10:12 mysql-bin.000010
-rw-r--r-- 1 root root  50039255 May 13 10:12 mysql-bin.000011
-rw-r--r-- 1 root root    250816 May 13 11:00 mysql-bin.000012

同时MHA 0.56 开始支持从binlog server上恢复日志:

Binlog server
Starting from MHA version 0.56, MHA supports new section [binlogN]. In binlog section, you can define mysqlbinlog streaming servers. When MHA does GTID based failover, MHA checks binlog servers, and if binlog servers are ahead of other slaves, MHA applies differential binlog events to the new master before recovery. When MHA does non-GTID based (traditional) failover, MHA ignores binlog servers.
Below is an example configuration.
  manager_host$ cat /etc/app1.cnf 
  [server default]
  # mysql user and password
  user=root
  password=mysqlpass
  # working directory on the manager
  manager_workdir=/var/log/masterha/app1
  # manager log file
  manager_log=/var/log/masterha/app1/app1.log
  # working directory on MySQL servers
  remote_workdir=/var/log/masterha/app1
  
  [server1]
  hostname=host1
  [server2]
  hostname=host2  
  [server3]
  hostname=host3
  [binlog1]
  hostname=binlog_host1
  [binlog2]
  hostname=binlog_host2 

REF:semi-synchronous-replication-at-facebook
https://code.google.com/p/mysql-master-ha/wiki/Configuration#Binlog_server

Multi-Master-Rep in MySQL DB

May 12, 2014 Architecture, MYSQL, replication No comments

Multi-master-rep

two masters:

-- 192.168.126.128 mysql 5.6.12
-- 192.168.126.129 mysql 5.6.12
slave :

-- 192.168.126.130 MariaDB 10.0
from one master machine create new db and insert one record:

23:44:55 (none)> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      544 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.07 sec)

23:45:51 (none)> create database mariatest1;
Query OK, 1 row affected (0.21 sec)

23:50:14 (none)> use mariatest1;
Database changed
23:52:16 mariatest1> create table liuyang (id bigint);
Query OK, 0 rows affected (1.42 sec)

23:52:30 mariatest1> 
23:52:31 mariatest1> insert into liuyang values (1);
Query OK, 1 row affected (0.17 sec)

23:52:37 mariatest1> commit;
Query OK, 0 rows affected (0.01 sec)
from secoend master machine  create new db and insert one record:

23:47:23 (none)> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 550
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

23:47:29 (none)> create database mariatest2;
Query OK, 1 row affected (0.13 sec)

23:50:27 (none)> use mariatest2;
Database changed
23:52:48 mariatest2> create table liuyang (id int);
Query OK, 0 rows affected (0.65 sec)

23:53:43 mariatest2> insert into liuyang values (1);
Query OK, 1 row affected (0.12 sec)

23:53:55 mariatest2> commit;
Query OK, 0 rows affected (0.00 sec)
change Mariadb slave connect to these two masters:

MariaDB [(none)]> SET @@default_master_connection='t1';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CHANGE MASTER 't1' TO MASTER_HOST = '192.168.126.128', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000005',MASTER_LOG_POS = 550;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SET @@default_master_connection='t2';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CHANGE MASTER 't2' TO MASTER_HOST = '192.168.126.129', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000004',MASTER_LOG_POS = 544;
Query OK, 0 rows affected (0.01 sec)
[mysql@mariadb ~]$ mysql  --socket=/mysql3333/mysql/data/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.0.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> START ALL SLAVES;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> show slave status
    -> \G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> show slave status \G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> show slave 'T1' status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.128
                  Master_User: repluser
                  Master_Port: 3333
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 550
               Relay_Log_File: GSS-01-relay-bin-t1.000002
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 550
              Relay_Log_Space: 708
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 6
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> show slave 'T2' status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.129
                  Master_User: repluser
                  Master_Port: 3333
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 544
               Relay_Log_File: GSS-01-relay-bin-t2.000002
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 544
              Relay_Log_Space: 708
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mariatest1         |
| mariatest2         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.06 sec)

MariaDB [(none)]> 
MariaDB [(none)]> select * from mariatest1.liuyang;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [(none)]> select * from mariatest2.liuyang;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

REF:https://mariadb.com/kb/en/change-master-to/
https://mariadb.com/kb/en/multi-source-replication/
https://mariadb.com/kb/zh-cn/installing-mariadb-binary-tarballs/

MySQL replication case 一则

September 10, 2013 MYSQL, replication No comments

最近同事处理了一则mysql复制错误.发出来参考下

MYSQL同步出错,报错信息如下:

Last_Errno: 1267
Last_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'' on query. Default database: 'inshop_app'. Query: 'INSERT INTO inshop_app.app_sms_order_info (ORDER_CODE, ORDER_TIME, ORDER_AMOUNT, ORDER_CONTENT, BUY_NUM, ORDER_STATUS, MERCHANT_ID, CREATE_TIME, UPDATE_TIME, APP_TYPE, pay_time, remark) 
        VALUES( NAME_CONST('my_order_code',_utf8'SBY130830010708_F0' COLLATE 'utf8_general_ci'),NOW(),'0','1次', NAME_CONST('my_sms_num',1125000),'1', NAME_CONST('my_merchant_id',10708),NOW(),NOW(),'2',NOW(),CONCAT ('钻展赠送:', NAME_CONST('my_sms_num',1125000)))'

出错原因分析:

此SQL在Master上执行时是这样的

INSERT INTO inshop_app.app_sms_order_info (ORDER_CODE, ORDER_TIME, ORDER_AMOUNT, ORDER_CONTENT, BUY_NUM, ORDER_STATUS, MERCHANT_ID, CREATE_TIME, UPDATE_TIME, APP_TYPE, pay_time, remark) 
        VALUES( 'SBY130830010708_F0',NOW(),'0','1次', 1125000,'1', 10708,NOW(),NOW(),'2',NOW(),CONCAT ('钻展赠送:', 1125000))

该SQL本身是没问题的,执行成功,但是MYSQL在记录BINLOG的时候,会对常量用NAME_CONST()函数进行“标识”
同步的报错就出现在这个地方

CONCAT ('钻展赠送:', NAME_CONST('my_sms_num',1125000))

其中,’钻展赠送:’是UTF8字符集,NAME_CONST(‘my_sms_num’,1125000)得到的数值型常量被自动转型为LATIN1字符集,外层的CONCAT()函数不支持二种不同字符集进行连接,于是报错

以下测试可验证此分析:

无NAME_CONST()函数标识常量时,即如同在Master上执行时,成功

09:29:06 inshop_app> select concat('钻展赠送',123);
+----------------------------+
| concat('钻展赠送',123)     |
+----------------------------+
| 钻展赠送123                |
+----------------------------+
1 row in set (0.00 sec)

有NAME_CONST()函数标识常量时,即如同在Slave上执行时,失败

09:25:17 inshop_app> select concat('钻展赠送',name_const('colname',123));
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'

报错与同步是一样的错误

什么情况下MySQL会自动加上NAME_CONST函数

测试1: 直接insert

11:27:32 test> insert into lengzhenguo_mha(c3,c4) values(1,'a'),('2','b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

BINLOG中的内容

#130909 11:28:35 server id 2009  end_log_pos 469        Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1378697315/*!*/;
insert into lengzhenguo_mha(c3,c4) values(1,'a'),('2','b')
/*!*/;

测试2: 简单的存储过程

13:16:42 test> create procedure p_test()
    -> begin
    -> insert into lengzhenguo_mha(c3,c4) values(10,'abc'),('20','xyz');
    -> commit;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

13:17:38 test> call p_test();
Query OK, 0 rows affected (0.00 sec)

BINLOG中的内容

#130909 13:18:21 server id 2009  end_log_pos 328        Query   thread_id=12    exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1378703901/*!*/;
insert into lengzhenguo_mha(c3,c4) values(10,'abc'),('20','xyz')
/*!*/;

测试3:带参数的存储过程 类似bind value

13:22:43 test> CREATE procedure p_test_2 (i bigint, j varchar(30))
    -> begin
    ->  insert into lengzhenguo_mha(c3,c4) values(i,j);
    -> commit;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

13:23:16 test> call p_test_2(100,'dba');
Query OK, 0 rows affected (0.00 sec)

13:25:10 test> call p_test_2('500','dba');
Query OK, 0 rows affected (0.00 sec)

BINLOG中的内容
#130909 13:23:32 server id 2009 end_log_pos 612 Query thread_id=12 exec_time=0 error_code=0

SET TIMESTAMP=1378704212/*!*/;
insert into lengzhenguo_mha(c3,c4) values( NAME_CONST('i',100), NAME_CONST('j',_latin1'dba' COLLATE 'latin1_swedish_ci'))
/*!*/;
#130909 13:25:15 server id 2009  end_log_pos 1226       Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1378704315/*!*/;
insert into lengzhenguo_mha(c3,c4) values( NAME_CONST('i',500), NAME_CONST('j',_latin1'dba' COLLATE 'latin1_swedish_ci'))
/*!*/;

注意:’500’在写入Binlog时,已经被转换成数值型了

目前已知的解决方法:

方法1:不要直接使用数值,直接给予字符串,建议使用此方法

09:25:27 inshop_app> select concat('钻展赠送',name_const('colname','123'));
+----------------------------------------------------+
| concat('钻展赠送',name_const('colname','123'))     |
+----------------------------------------------------+
| 钻展赠送123                                        |
+----------------------------------------------------+
1 row in set (0.00 sec)

方法2:先进行类型转换

09:56:32 inshop_app> select concat('钻展赠送',convert(name_const('colname',123) using utf8));
+----------------------------------------------------------------------+
| concat('钻展赠送',convert(name_const('colname',123) using utf8))     |
+----------------------------------------------------------------------+
| 钻展赠送123                                                          |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)