首页 > 数据库 >关于SQL_Errno:1677导致主从复制中断的思考和实践【转】

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

时间:2024-11-07 17:21:22浏览次数:1  
标签:conversions 主库 主从复制 1677 slave Errno SQL 从库 type

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

1) 数据库版本:MySQL5.7.19

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

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

3)错误重现:

     在主库上,建表如下:

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

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

1 alter table conversions modify id bigint not null;

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

2、解决方案

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

1 2 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'

 View Code

     4-3、slave_type_conversions=''

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

 5、支持哪些类型转换?

       5.1) 支持整型 TINYINTSMALLINTMEDIUMINTINT, and BIGINT之间任意两者转换

       5.2) 支持浮点数 DECIMALFLOATDOUBLE, and NUMERIC之间任意两者转换

       5.3) 支持 CHARVARCHAR, and TEXT之间任意两者转换

       5.4) 支持BINARYVARBINARY, 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

转自

【FAQ系列】关于SQL_Errno:1677导致主从复制中断的思考和实践 - 茁壮的小草 - 博客园
https://www.cnblogs.com/mysql-dba/p/8820798.html

mysql主从错误1677_MySQL slave error 1677-CSDN博客
https://blog.csdn.net/weixin_42569429/article/details/113282869

 

标签:conversions,主库,主从复制,1677,slave,Errno,SQL,从库,type
From: https://www.cnblogs.com/paul8339/p/18533152

相关文章

  • My_SQL day2
    知识点一:mysq查询方法    排序    限制    去重    模糊查询    范围查询    聚合函数    分组知识点二:连接查询    1.内连接    2.外连接知识点一:mysql查询方法isnull      ......
  • posgresql数据库高可用方案-patroni
    一、简介pg常用高可用方案有repmgr,patroni等,本文介绍patroni方案。Patroni,是专门为PostgreSQL数据库设计的一款以Python语言实现的高可用软件。其使用外部共享存储软件(kubernetes、etcd、etcd3、zookeeper、aws等)来存储patroni监控到的pg集群状态信息,实现PostgreSQL集群的自......
  • 基于Centos7.X部署mysql8.4集群(主备)
    1、目标    将在主库的数据变更同步到从库,从而保证主库和从库的数据一致。2、原理    2.1主库会把数据变更记录在二进制日志文件Binlog中    2.2从库连接主库,读取binlog日志,并写入自身中继日志relaylog    2.3slave重做中继日志,将改变......
  • 【Hive SQL】如何判断一个字段是否包含某个特定的值
    在HiveSQL中,如果你需要判断一个字段是否包含某个特定的值,你可以使用 LIKE 或RLIKE 关键字来进行字符串匹配。此外,Hive也支持一些字符串函数,如 INSTR 和LOCATE,这些都可以用于不同的需求场景。以下是一些常见的方法来判断一个字段是否包含某个值:数据准备——创建表格......
  • 揭秘MySQL事务的四大特性与隔离级别:从理论到实战,让你的数据操作无懈可击!
    MySQL事务的四大特性与隔离级别是数据库管理中非常重要的概念,它们确保了数据在并发操作下的一致性、完整性和可靠性。下面将详细介绍这四大特性及其隔离级别,并附上代码示例和详细解释。一、MySQL事务的四大特性原子性(Atomicity):定义:事务的所有操作要么全部完成,要么全部不完......
  • 高级 SQL 技巧详解
    文章目录高级SQL技巧详解一、引言二、窗口函数1、窗口函数的使用1.1、RANK()函数示例1.2、常用窗口函数三、公共表表达式(CTE)2、CTE的使用2.1、CTE示例四、索引优化3、索引的创建与优化3.1、创建索引3.2、索引类型与注意事项五、事务管理4、事务的基本操作4.1......
  • Mysql官网下载Windows、Linux各个版本
    下载软件官网地址:https://www.mysql.com/百度网盘分享的文件:Mysql链接:https://pan.baidu.com/s/1nT3oI-hXiQvZqdhi2Wv17g 提取码:8l4r 百度网盘只提供了Windows版本的,其他版本可以官网下载进入官网后,点击-看下图(说一万句不如一张图来得实在)往下滑屏幕,在点击在点击......
  • 在Debian12上安装mysql 8.0
    从deb安装默认是区分大小写的一、安装过程请参考:https://www.cnblogs.com/sunshine-sm/p/18030729设置为不区分大小写,请参考https://www.cnblogs.com/binli33/p/18531992二、常用命令mysql-uroot-papt-get--purgeautoremovemysql-serversystemctlstart......
  • 【Mysql】Mysql 有几级缓存?每一级缓存,具体是什么?
     Mysql有几级缓存?每一级缓存,具体是什么?转载:======https://mp.weixin.qq.com/s/fyMiy4G1Fa7laUSbydiHsA本文目录- 一、MySQL缓存机制概述- 二、MySQL整体架构  -1.解析器(Parser)  -2.优化器(Optimizer)  -3.执行器(Executor)  -三个核心组件之间的交互......
  • Mysql8.0设置大小写不敏感解决方案[转]
    【摘要】初始化Mysql8.0,修改Mysql8.0大小写敏感问题(默认敏感,改为不敏感)。在Debian12下验证通过。一、备份Mysql数据库重新初始化mysql,相当于“恢复出厂设置”,所以一定要备份好重要的数据库,就是data目录下面的那些数据库文件;二、停止Mysql服务在修改配置之前,请先把mysq......