ITPub博客

首页 > 数据库 > MySQL > error 1270 mysql主从

error 1270 mysql主从

原创 MySQL 作者:psufnxk2000 时间:2017-04-07 12:14:14 0 删除 编辑
作者:河南-老宋(志强)
链接: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 相互交流

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-2136800/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    618335