主从默认字符集不一样导致的主从同步报错

前言

在使用pt-table-checksum 校验主从的过程的时候,在创建dsns 表之后,往dsns表写入从库dsn信息的时候,从库的SQL线程就挂了,报错信息如下

Column 2 of table 'test.dsns' cannot be converted from type 'varchar(765)' to type 'varchar(255)'

执行的SQL为

CREATE TABLE test.dsns (id int(11) NOT NULL AUTO_INCREMENT,  parent_id int(11) DEFAULT NULL, dsn varchar(255) NOT NULL, PRIMARY KEY (id) ) ;
insert into test.dsns values(1,1,'h=10.1.11.86,P=3308');

分析

出现报错后,我们首先分析binlog,发现binlog 里面报错的位点刚好是执行insert语句的部分,其中dsn 字段为VARSTRING(765),表示utf8的varchar(255)
binlog解析

我们查看主库的test库字符集,为

mysql> show create database test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

查看从库的test库字符集为:

mysql 10:02:55 >show create database test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
1 row in set (0.00 sec)

发现主库是utf8,从库为uft8mb4。

主库的表结构为:

mysql> show create table dsns\G
*************************** 1. row ***************************
       Table: dsns
Create Table: CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

从库的表结构:

mysql 10:00:33 >show create table dsns\G
*************************** 1. row ***************************
       Table: dsns
Create Table: CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

至此,我们发现了主从同步报错的根本原因:
1. 建表语句在没有指定字符集的时候,会根据库的默认字符集建表,所以主库的表dsns的字符集是utf8
2. 建表语句在没有指定字符集的时候,binlog里面也不会记录字符集格式,导致在从库新建表的时候根据库级别的字符集选择了utf8mb4的字符集,新增记录就报错了

小结

主从的字符集一定要保持一致,字符集的不一致会导致很多问题,如主从同步报错,隐式转化等,一定要小心。如果可以,建表语句中显示的加上默认字符集

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注