前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 案例:同步中断与SQL线程类型转换

MySQL 案例:同步中断与SQL线程类型转换

原创
作者头像
王文安@DBA
发布2020-09-21 19:25:37
1.2K0
发布2020-09-21 19:25:37
举报
文章被收录于专栏:腾讯云数据库专家服务

问题描述

MySQL 同步时遇到 SQL 线程,显示的错误信息类似于:

代码语言:txt
复制
Column 0 of table 'test.char_utf8mb4' cannot be converted from type 'varchar(64(bytes))' to type 'varchar(48(bytes) utf8)'

解决方案

最稳妥的方案是通过备份恢复重新建立从库;当然,修改slave_type_conversions参数也可以恢复同步:

set global slave_type_conversions = 'ALL_LOSSY,ALL_NON_LOSSY'

但是必须注意的是,这种设置可能会因为数据类型转换丢失数据

问题还原

要还原描述中的场景比较简单,在主库和从库分别建立如下表:

代码语言:txt
复制
Master:
CREATE TABLE `char_utf8mb4` (
  `name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Slave:
CREATE TABLE `char_utf8mb4` (
  `name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后创建新的同步,并向主库写入一行数据:

代码语言:txt
复制
insert into char_utf8mb4 values('hello');

原理简析

MySQL 在同步的时候默认要求主库和从库的列属性完全一致,不仅是数据类型,数据长度,也包含字符集设置。如果发现不一致的时候,就会抛出如描述中一样的错误信息,不过 MySQL 可以通过参数设置来允许 SQL 线程来进行一些类型转换,参考官方文档的描述:

Controls the type conversion mode in effect on the replica when using row-based replication. In MySQL 5.7.2 and higher, its value is a comma-delimited set of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED. Set this variable to an empty string to disallow type conversions between the source and the replica. Setting this variable takes effect for all replication channels immediately, including running channels.

详细的内容推荐阅读官方文档,简而言之,通过设置slave_type_conversions这个参数,可以控制 SQL 线程支持哪些类型的转换。几个参数的效果如下表:

参数

效果

ALL_LOSSY

仅允许有损转换,比如 bigint 到 int,该模式不允许 int 到 bigint 的转换

ALL_NON_LOSSY

仅允许无损转换,比如 int 到 bigint

ALL_LOSSY,ALL_NON_LOSSY

同时允许有损和无损转换

空值

不允许任何类型的转换

因此如问题还原场景中的例子,如果设置了slave_type_conversions = 'ALL_LOSSY,ALL_NON_LOSSY',那么同步就会恢复,且会发生有损转换,因为 utf8 只是 utf8mb4 的子集

扩展一下

通常情况下,遇到这个问题的时候,通过对比主库和从库出问题的表,就可以发现问题了,但是偶尔还会有一些比较特殊的例子。

回想一下 MySQL 同步时的要求:包含字符集的设置也要一致。实际上的效果就是:如果因为某种原因,源表被写入了其他字符集的数据,或者从库在同步的时候,SQL 线程使用和表字符集不一样的设置,那么也会遇到类似的问题

这种类型的问题,一般会发现主库和从库的表完全一致,字符集设置也完全一样,但是 binlog 中可能会发现:

binlog内容
binlog内容

从库在同步的时候,修改了字符集的设置

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题描述
  • 解决方案
  • 问题还原
  • 原理简析
  • 扩展一下
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档