现象：

mysql 5.6

主从报下面的错误：

mysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: ****Master_User: ucloudbackupMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 43347 Relay_Log_File: mysql-relay.000002Relay_Log_Pos: 1810Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_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: 0Exec_Master_Log_Pos: 1647Relay_Log_Space: 43710Until_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: NoLast_IO_Errno: 0Last_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: 169052043Master_UUID: a2b11ba1-1552-11e7-a33b-52540059bf44 Master_Info_File: /opt/udb/instance/mysql-5.6/2f2a1aea-b636-48b7-9006-a1af025d90d0/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Master_Retry_Count: 86400Master_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-105Executed_Gtid_Set: a2b11ba1-1552-11e7-a33b-52540059bf44:1-5Auto_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 //CREATEPROCEDURE `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 相互交流