MySQL replication illegal mix of collations

標籤：style   http   color   使用   os   io   for   ar   

MySQL replication case 一則

轉載：http://www.vmcd.org/2013/09/mysql-replication-case-%E4%B8%80%E5%88%99/

 

Posted by admin on September 10th, 2013

最近同事處理了一則mysql複製錯誤.發出來參考下

MYSQL同步出錯，報錯資訊如下：

?

Last_Errno: 1267Last_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=0SET 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=0use `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=0SET 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)

注意：這裡的123加引號，字串~

方法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)