MySQL replication Illegal mix of collations

MySQL replication case A

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

Posted by admin on September 10th, 2013

Recently, a colleague has handled a MySQL replication error. Send for reference

MySQL synchronization error, the error message is as follows:

?

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 times ', Name_const (' My_sms_num ', 1125000), ' 1 ', name_const (' my_merchant_id ', 10708), now (), now ( ), ' 2 ', now (), CONCAT (' Diamond Show Giveaway: ', Name_const (' My_sms_num ', 1125000)) '

Error reason analysis:

This is the case when SQL is executed on 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))

The SQL itself is no problem, the execution succeeds, but when MySQL logs Binlog, the constants are "identified" with the Name_const () function.
The sync error appears in this place.

?

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

Among them, ' Diamond Show Gift: ' is the UTF8 character set, Name_const (' My_sms_num ', 1125000) The resulting numeric constants are automatically converted to the LATIN1 character set, the outer concat () function does not support two different character sets to connect, so error

The following tests can verify this analysis:

When no Name_const () function identifies a constant, as if it were executed on master, a successful

?

09:29:06 inshop_app> select Concat (' Diamond Show Giveaway ', 123); +----------------------------+ | concat (' Diamond show Gift ', 123)      | +----------------------------+ | Diamond Exhibition Gift 123                 | +----------------------------+ 1 row in Set (0.00 sec)

When there is a name_const () function that identifies a constant, that is, when executed on slave, fails

?

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‘

Error and synchronization are the same errors

What happens when MySQL automatically adds the Name_const function

Test 1: Direct 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

What's in 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‘)/*!*/;

Test 2: A simple stored procedure

?

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)

What's in 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‘)/*!*/;

Test 3: Stored procedure with parameters similar to 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)

What's in Binlog
#130909 13:23:32 Server ID 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‘))/*!*/;

Note: ' 500′ has been converted to a numeric value when it is written to Binlog

Current known workarounds:

Method 1: Do not directly use numeric values, give the string directly, it is recommended to use this method

?

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

Note: Here's 123 quotes, String ~

Method 2: Type conversions First

?

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)