mysql 1270_error 1270 mysql主从

作者：河南-老宋(志强)

链接：https://zhuanlan.zhihu.com/p/26224566

来源：知乎

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

现象：

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

Master_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: 0

1 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`()

BEGIN

DECLARE i INT DEFAULT 180;# can not be 0

WHILE i<400

DO

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