error 1270 mysql主从

现象：

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 相互交流