现象：

mysql 5.6

主从报下面的错误：

mysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: **** Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 43347 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 1810 Relay_Master_Log_File: mysql-bin.000003 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: 1270 Last_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'test'. Query: 'INSERT INTO test (addressType, addressName) VALUES ('sh_jincheng', concat('上海市', NAME_CONST('i',180),'室'))' Skip_Counter: 0 Exec_Master_Log_Pos: 1647 Relay_Log_Space: 43710 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1270 Last_SQL_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'test'. Query: 'INSERT INTO test (addressType, addressName) VALUES ('sh_jincheng', concat('上海市', NAME_CONST('i',180),'室'))' Replicate_Ignore_Server_Ids: Master_Server_Id: 169052043 Master_UUID: a2b11ba1-1552-11e7-a33b-52540059bf44 Master_Info_File: /opt/udb/instance/mysql-5.6/2f2a1aea-b636-48b7-9006-a1af025d90d0/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 170330 22:19:10 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a2b11ba1-1552-11e7-a33b-52540059bf44:1-105 Executed_Gtid_Set: a2b11ba1-1552-11e7-a33b-52540059bf44:1-5 Auto_Position: 01 row in set (0.00 sec)

原来有一个笔记 ，里面写到：

ERROR 1267 concat name_const函数今天朋友他们有一个错误，最后是因为：MySQL> select concat('￥',NAME_CONST('tttt',1234));ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'这个类似的错误原来也见过：那是因为两表关联时，关联的字段是有索引的，但是索引不是按的相同的COLLATION，所以出现的问题，修改为一样之后，即可正常。又试了一下MySQL> select concat('a',NAME_CONST('tttt',1234));+-------------------------------------+| concat('a',NAME_CONST('tttt',1234)) |+-------------------------------------+| a1234 |+-------------------------------------+1 row in set (0.00 sec)MySQL> select concat('工',NAME_CONST('tttt',1234));ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'从这里看，用汉字的类的就不正常。MySQL> select COLLATION('工');+------------------+| COLLATION('工') |+------------------+| utf8_general_ci |+------------------+1 row in set (0.00 sec)MySQL> select COLLATION('a');+-----------------+| COLLATION('a') |+-----------------+| utf8_general_ci |+-----------------+1 row in set (0.00 sec)MySQL> select COLLATION('￥');+------------------+| COLLATION('￥') |+------------------+| utf8_general_ci |+------------------+1 row in set (0.00 sec)MySQL> select COLLATION(NAME_CONST('tttt',1234));+------------------------------------+| COLLATION(NAME_CONST('tttt',1234)) |+------------------------------------+| binary |+------------------------------------+1 row in set (0.00 sec)有点晕了，前前看 'a','工','￥' 都是一样的， 汉字的不行。看看这个函数：This function is for internal use only，可以写成别的方式 比如这里的 MySQL> select NAME_CONST('tttt',1234);+------+| tttt |+------+| 1234 |+------+1 row in set (0.00 sec)可以写为：MySQL> select 1234 as 'tttt';+------+| tttt |+------+| 1234 |+------+1 row in set (0.00 sec)一样的效果 。另：MySQL> select concat('￥',NAME_CONST('tttt','1234'));+-----------------------------------------+| concat('￥',NAME_CONST('tttt','1234')) |+-----------------------------------------+| ￥1234 |+-----------------------------------------+这样也不会报错：不知道开发到底是什么 意思 ，和开发商量 才好最后约定怎么搞。

当时朋友遇到这个问题，最后他们也没有反馈这个是怎么回事，我也就忘记了这个事情，这次是自己遇到这个问题，并且分析问题时较之前也多了一些方法，这次就从源代码看下这个问题。

看了几分钟代码，有一个怀疑点是 存储过程的调用引起的这个问题(是在subst_spvars函数中完成的，这里就不列出来代码了)，就去主库查看，果然有这个procedure,

重现方法：

在主库上建表：

主库Binlog_format=mixed;

CREATE TABLE `test` (

`addressId` int(11) NOT NULL AUTO_INCREMENT,

`addressType` varchar(30) NOT NULL,

`addressName` varchar(300) NOT NULL,

`usable` tinyint(4) NOT NULL DEFAULT '1',

PRIMARY KEY (`addressId`)

) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4

在主库上建procedure:

delimiter //CREATE PROCEDURE `test_insert`()BEGINDECLARE i INT DEFAULT 180;# can not be 0WHILE i<400DOINSERT INTO test (addressType, addressName) VALUES ('sh_jincheng', concat('上海市',i,'室'));SET i=i+1;END WHILE ;commit;END//delimiter ;

调用procedure:

mysql> call test_insert();

Query OK, 0 rows affected (0.21 sec)

从库上就会报上面的错.

解决方法：

可以把 binlog_format=row 解决这个问题

声明：源码新手，仅代表个人意见，非权威，如果误导看官，见谅。

QQ 273002188 欢迎一起学习
QQ 群 236941212
oracle,mysql,PG 相互交流