MySQL replication illegal mix of collations

最新推荐文章于 2025-09-06 09:33:29 发布

转载最新推荐文章于 2025-09-06 09:33:29 发布 · 85 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jackhub/p/3909539.html

文章标签：

#数据库

本文讨论了一起MySQL复制过程中遇到的错误，分析了错误原因并提供了解决方案。主要关注在复制操作中，不同字符集之间的冲突导致的非法混合错误，以及如何通过修改SQL语句和数据类型来避免这些问题。

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: 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)

注意：这里的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)