【FAQ系列】关于SQL_Errno:1677导致主从复制中断的思考和实践

1、简单介绍该错误发生的背景：

1）数据库版本：MySQL5.7.19

2）对一个大表修改字段类型DDL（将主键id int变为bigint），为了不影响主库业务，先在从库上执行DDL操作，然后通过主从切换完成最终的大表DDL；在从库执行完DDL后，这时发现复制中断了，报错信息：

Last_SQL_Errno: 1677
Last_SQL_Error: Column 0 of table 'darren.conversions' cannot be converted from type 'int' to type 'bigint(20)'

3）错误重现：

在主库上，建表如下：

CREATE TABLE `conversions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

　在从库上，修改字段类型，将id int 变成bigint：

alter table conversions modify id bigint not null;

最后在主库上向该表插入数据，这时从库就报1677错误了。

2、解决方案

参考MySQL文档中的方法，在从库上设置slave_type_conversions ="ALL_NON_LOSSY"，重启复制即可解决

MySQL>  set global slave_type_conversions ='ALL_NON_LOSSY';
MySQL>  stop slave;start slave;

3、关于该错误的详细介绍

【不同数据类型的主从复制】：正常情况下，主库和从库每个表的各个列数据类型都是一致的，但是MySQL并不强制这样。有些特殊情况下，是存在主从列数据类型不一致，比如上面的大表DDL操作，先在从库上修改，然后切换主从，最后再修改老主库的结构，当然，可能存在其他多种情况，不一一列举。

【属性升级和降级(attribute promotion and demotion)】：主从复制下，MySQL只支持同类小数据类型和较大类型之间的属性提升和降级，比如将主库上int在从库上转为bigint，视为属性升级，相反将bigint转为int就是属性降级。属性升降级可用于基于语句和基于行的复制格式，并且不依赖于存储引擎。但是，日志格式的选择对允许的类型转换有影响，这里主要讨论row格式。

【有损和无损转换(Lossy and non-lossy conversions)】：主从数据库同一表同一列数据类型不一致，会产生数据类型转换。为了符合和适应目标列类型，截断(或其他修改)源值，称为有损转换；不需要截断或类似的修改以适应目标列中的源列值的转换是一个非有损的转换。这两种转换模式主要由slave_type_conversions系统变量控制，该变量的值如下：

Mode	影响描述
ALL_LOSSY（有损转换）	该模式下，列类型转换允许丢失一些信息。如果只设置该模式，只允许同类的大数据类型转换为小数据类型，其他模式的转换都会发生1677错误。生产环境不建议设置该值，很容易导致主从不一致。
ALL_NON_LOSSY（无损转换）	该模式下，不会导致数据丢失和截断，因为该值只允许同类的小数据类型转换为大数据类型，其他模式的转换都会发生1677错误。生产环境建议设置该值
ALL_LOSSY,ALL_NON_LOSSY（两种情况并存）	上面两种模式都支持，生产环境不建议设置该值
[empty] （空值）	不设置任何值，表示不允许任何模式的类型转换，如果发现类型转换，都会产生1677错误，导致复制中断

4、测试

4-1、slave_type_conversions='ALL_LOSSY'

主库id是int类型，slave修改为bigint，当主库插入数据后，从库直接报错1677；当从库修改为tinyint，由于是ALL_LOSSY模式，从库数据被截断，这时主从数据是不一致的。

----------------------------------------当从库修改为bigint----------------------------------------------------------------------------


mysql> show global variables like 'slave_type_conversions';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| slave_type_conversions | ALL_LOSSY |
+------------------------+-----------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
                   Last_Errno: 1677  #直接报错了
                   Last_Error: Column 0 of table 'darren.conversions' cannot be converted from type 'int' to type 'bigint(20)'

----------------------------------------当从库修改为tinyint----------------------------------------------------------------------------
master插入11111：
mysql> insert into conversions(id,name) values(11111,'a');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from conversions;
+-------+------+
| id    | name |
+-------+------+
|     1 | a    |
| 11111 | a    |
+-------+------+

slave的值127：
mysql> select * from darren.conversions;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
| 127 | a    |  #11111被截断为127
+-----+------+

4-2、slave_type_conversions='ALL_NON_LOSSY'

slave> show global variables like 'slave_type_conversions';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_type_conversions | ALL_NON_LOSSY |
+------------------------+---------------+

slave> show create table conversions;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                     |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| conversions | CREATE TABLE `conversions` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


------------------------------------主库插入值-----------------------------
master> insert into conversions(id,name) values(2,'a'); 
Query OK, 1 row affected (0.01 sec)

master> select * from conversions;
+-------+------+
| id    | name |
+-------+------+
|     1 | a    |
|     2 | a    |
| 11111 | a    |
+-------+------+

从库报错了，因为ALL_NON_LOSSY 只支持小数据类型转为大数据类型
mysql> show slave status\G
*************************** 1. row ***************************
 Last_Errno: 1677
                   Last_Error: Column 0 of table 'darren.conversions' cannot be converted from type 'int' to type 'tinyint(4)'

View Code

4-3、slave_type_conversions=''

设置为空值，从库不支持任何的类型转换，直接报错。

5、支持哪些类型转换？

5.1）支持整型 TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT之间任意两者转换

5.2）支持浮点数 DECIMAL, FLOAT, DOUBLE, and NUMERIC之间任意两者转换

5.3）支持 CHAR, VARCHAR, and TEXT之间任意两者转换

5.4）支持BINARY, VARBINARY, and BLOB之间任意两者转换

5.5）支持bit之间转换

其他不在上述的都不支持转换。

【总结】设置空值是最严格的模式，不允许列类型不一致，如果发生不一致，从库复制就中断报错了，能够最大程度上维护主从数据一致性。

如果日常运维，如大表DDL需要在从库上进行的，可以先设置slave_type_conversions='ALL_NON_LOSSY',能够保证从库不会产生1677错误，但是对于整型字段，一定要注意主从unsigned或者signed问题，如果主库signed，从库上是unsigned，主从数据有可能不一致；当主库上unsigned，从库signed的，一定要保证从库字段足够大能存下主库字段值，否则也会导致数据不一致。

参考文档：https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

posted @ 2018-04-13 15:44 茁壮的小草阅读(3860) 评论(0) 编辑收藏举报

刷新页面返回顶部