GreatSQL 从库报错 13146：字符集不一致问题处理
作者：GreatSQL
- 2025-06-18 福建
本文字数：10590 字
阅读完需：约 35 分钟
GreatSQL 从库报错 13146：字符集不一致问题处理
1.问题概述
需要将数据反向同步到源端，在使用 SELECT INTO OUTFILE 和 LOAD DATA 的方式进行数据恢复后配置同步，从库发生报错 13146 数据类型转换失败，导致同步异常；通过对比表结构和列的字符集，发现主从库相关表、列字符集设置不一致，修改为一致后，同步正常。
2.问题复现
本次测试基于 GreatSQL 8.0.32
2.1 初始化 2 个单机实例
略
2.2 主库创建测试表
greatsql> CREATE TABLE `smbms_address` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `contact` varchar(15) DEFAULT NULL COMMENT '联系人姓名', `addressDesc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '收货地址明细', `postCode` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮编', `tel` varchar(20) DEFAULT NULL COMMENT '联系人电话', `createdBy` bigint DEFAULT NULL COMMENT '创建者', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint DEFAULT NULL COMMENT '修改者', `modifyDate` datetime DEFAULT NULL COMMENT '修改时间', `userId` bigint DEFAULT NULL COMMENT '用户ID', PRIMARY KEY (`id`)); greatsql> INSERT INTO `smbms_address`(`id`,`contact`,`addressDesc`,`postCode`,`tel`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`,`userId`) values (1,'小丽','北京市','100010','13689999',1,'2016-04-13 10:09:00',NULL,NULL,201),(2,'小张','北京市','100000','185672312',1,'2016-04-13 01:10:32',NULL,NULL,201);
复制代码
2.3 查看数据
greatsql> SELECT * FROM smbms_address;+----+---------+-------------+----------+-----------+-----------+---------------------+----------+------------+--------+| id | contact | addressDesc | postCode | tel | createdBy | creationDate | modifyBy | modifyDate | userId |+----+---------+-------------+----------+-----------+-----------+---------------------+----------+------------+--------+| 1 | 小丽 | 北京市 | 100010 | 13689999 | 1 | 2016-04-13 10:09:00 | NULL | NULL | 201 || 2 | 小张 | 北京市 | 100000 | 185672312 | 1 | 2016-04-13 01:10:32 | NULL | NULL | 201 |+----+---------+-------------+----------+-----------+-----------+---------------------+----------+------------+--------+2 rows in set (0.00 sec)
复制代码
2.4 主库导出数据
greatsql> SELECT * FROM test01.smbms_address INTO OUTFILE '/data/smbms_address.txt' FIELDS TERMINATED BY '_~' ENCLOSED BY '"';
复制代码
2.5 从库创建表
greatsql> CREATE TABLE `smbms_address` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `contact` varchar(15) DEFAULT NULL COMMENT '联系人姓名', `addressDesc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '收货地址明细', `postCode` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮编', `tel` varchar(20) DEFAULT NULL COMMENT '联系人电话', `createdBy` bigint DEFAULT NULL COMMENT '创建者', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint DEFAULT NULL COMMENT '修改者', `modifyDate` datetime DEFAULT NULL COMMENT '修改时间', `userId` bigint DEFAULT NULL COMMENT '用户ID', PRIMARY KEY (`id`));
复制代码
2.6 从库导入数据
greatsql> LOAD DATA INFILE '/data/smbms_address.txt' INTO TABLE test01.smbms_address FIELDS TERMINATED BY '_~' ENCLOSED BY '"';
复制代码
2.7 从库查询数据
greatsql> SELECT * FROM smbms_address;+----+---------+-------------+----------+-----------+-----------+---------------------+----------+------------+--------+| id | contact | addressDesc | postCode | tel | createdBy | creationDate | modifyBy | modifyDate | userId |+----+---------+-------------+----------+-----------+-----------+---------------------+----------+------------+--------+| 1 | 小丽 | 北京市 | 100010 | 13689999 | 1 | 2016-04-13 10:09:00 | NULL | NULL | 201 || 2 | 小张 | 北京市 | 100000 | 185672312 | 1 | 2016-04-13 01:10:32 | NULL | NULL | 201 |+----+---------+-------------+----------+-----------+-----------+---------------------+----------+------------+--------+2 rows in set (0.00 sec)
复制代码
2.8 从库建立复制
#主库查看当前gtid和pos位点信息greatsql> SHOW MASTER STATUS;+---------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+------------------------------------------+| binlog.000001 | 1693 | | | 28093c86-5631-11ef-87f4-00163eab83df:1-2 |+---------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)
#从库执行greatsql> RESET MASTER;Query OK, 0 rows affected (0.04 sec)
greatsql>RESET SLAVE ALL;Query OK, 0 rows affected, 1 warning (0.03 sec)
greatsql> SET GLOBAL GTID_PURGED='28093c86-5631-11ef-87f4-00163eab83df:1-2';Query OK, 0 rows affected (0.00 sec)
greatsql> CHANGE MASTER TO MASTER_HOST = '172.17.140.13',MASTER_USER = 'replabc',MASTER_PASSWORD = '!QAZ2WSX',MASTER_PORT = 5506, MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1693;Query OK, 0 rows affected, 8 warnings (0.05 sec)
greatsql> START SLAVE;Query OK, 0 rows affected, 1 warning (0.04 sec)
greatsql> SHOW SLAVE STATUS \G*************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.17.140.13 Master_User: replabc Master_Port: 5506 Connect_Retry: 60 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 1693 Relay_Log_File: gip-relay-bin.000002 Relay_Log_Pos: 323 Relay_Master_Log_File: binlog.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1693 Relay_Log_Space: 531 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: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 135506 Master_UUID: 28093c86-5631-11ef-87f4-00163eab83df Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 28093c86-5631-11ef-87f4-00163eab83df:1-2,cea38b81-6b2a-11ef-926f-00163e8c8b06:1-2 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.01 sec)
复制代码
2.9 主库插入新数据
greatsql> INSERT INTO smbms_address values(3,'小小','北京市','100021','133876742',1,'2016-04-13 00:00:05',NULL,NULL,201);
复制代码
2.10 从库查看复制状态
greatsql> SHOW SLAVE STATUS \G*************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.17.140.13 Master_User: replabc Master_Port: 5506 Connect_Retry: 60 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 2213 Relay_Log_File: gip-relay-bin.000002 Relay_Log_Pos: 323 Relay_Master_Log_File: binlog.000001 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: 13146 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '28093c86-5631-11ef-87f4-00163eab83df:3' at master log binlog.000001, end_log_pos 2182. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 1693 Relay_Log_Space: 1051 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 13146 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '28093c86-5631-11ef-87f4-00163eab83df:3' at master log binlog.000001, end_log_pos 2182. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 135506 Master_UUID: 28093c86-5631-11ef-87f4-00163eab83df Master_Info_File: mysql.slave_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: 240929 15:32:26 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 28093c86-5631-11ef-87f4-00163eab83df:3 Executed_Gtid_Set: 28093c86-5631-11ef-87f4-00163eab83df:1-2,cea38b81-6b2a-11ef-926f-00163e8c8b06:1-3 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
greatsql> SELECT * FROM PERFORMANCE_SCHEMA.REPLICATION_APPLIER_STATUS_BY_WORKER LIMIT 1\G*************************** 1. row *************************** CHANNEL_NAME: WORKER_ID: 1 THREAD_ID: NULL SERVICE_STATE: OFF LAST_ERROR_NUMBER: 13146 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '28093c86-5631-11ef-87f4-00163eab83df:3' at master log binlog.000001, end_log_pos 2182; Column 1 of table 'test01.smbms_address' cannot be converted from type 'varchar(45(bytes))' to type 'varchar(60(bytes) utf8mb4)' LAST_ERROR_TIMESTAMP: 2024-09-29 15:32:26.598104
复制代码
根据 performance_schema.replication_applier_status_by_worker表中的详细错误信息可以发现从库回放时数据类型发生转换，导致同步报错。涉及到的表是test01.smbms_address，其中的第一列在主库和从库之间数据类型不匹配。主库上该列被定义为 varchar(45 bytes)，而从库上同一列被定义为 varchar(60 bytes) utf8mb4。
2.11 对比表结构
主库查看：
greatsql> SHOW CREATE TABLE smbms_address \G*************************** 1. row *************************** Table: smbms_addressCreate Table: CREATE TABLE `smbms_address` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `contact` varchar(15) DEFAULT NULL COMMENT '联系人姓名', `addressDesc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '收货地址明细', `postCode` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮编', `tel` varchar(20) DEFAULT NULL COMMENT '联系人电话', `createdBy` bigint DEFAULT NULL COMMENT '创建者', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint DEFAULT NULL COMMENT '修改者', `modifyDate` datetime DEFAULT NULL COMMENT '修改时间', `userId` bigint DEFAULT NULL COMMENT '用户ID', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)
复制代码
从库查看：
greatsql> SHOW CREATE TABLE smbms_address \G*************************** 1. row *************************** Table: smbms_addressCreate Table: CREATE TABLE `smbms_address` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `contact` varchar(15) DEFAULT NULL COMMENT '联系人姓名', `addressDesc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '收货地址明细', `postCode` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮编', `tel` varchar(20) DEFAULT NULL COMMENT '联系人电话', `createdBy` bigint DEFAULT NULL COMMENT '创建者', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint DEFAULT NULL COMMENT '修改者', `modifyDate` datetime DEFAULT NULL COMMENT '修改时间', `userId` bigint DEFAULT NULL COMMENT '用户ID', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
复制代码
可以看到上述 smbms_address表的字符集两边不一致，主库为 utf8mb3，从库为 utf8mb4，那么所属列的字符集是否一致呢？
2.12 确认表字段相关字符集和排序规则
主库查看：
greatsql> SELECT table_schema,table_name,column_name,character_set_name,COLLATION_NAME,COLUMN_TYPE from information_schema.columns where table_schema = 'test01' and table_name = 'smbms_address';+--------------+---------------+--------------+--------------------+--------------------+-------------+| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE |+--------------+---------------+--------------+--------------------+--------------------+-------------+| test01 | smbms_address | addressDesc | utf8mb3 | utf8mb3_general_ci | varchar(50) || test01 | smbms_address | contact | utf8mb3 | utf8mb3_general_ci | varchar(15) || test01 | smbms_address | createdBy | NULL | NULL | bigint || test01 | smbms_address | creationDate | NULL | NULL | datetime || test01 | smbms_address | id | NULL | NULL | bigint || test01 | smbms_address | modifyBy | NULL | NULL | bigint || test01 | smbms_address | modifyDate | NULL | NULL | datetime || test01 | smbms_address | postCode | utf8mb3 | utf8mb3_general_ci | varchar(15) || test01 | smbms_address | tel | utf8mb3 | utf8mb3_general_ci | varchar(20) || test01 | smbms_address | userId | NULL | NULL | bigint |+--------------+---------------+--------------+--------------------+--------------------+-------------+10 rows in set (0.01 sec)
复制代码
从库查看：
greatsql> SELECT table_schema,table_name,column_name,character_set_name,COLLATION_NAME,COLUMN_TYPE from information_schema.columns where table_schema = 'test01' and table_name = 'smbms_address';+--------------+---------------+--------------+--------------------+--------------------+-------------+| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE |+--------------+---------------+--------------+--------------------+--------------------+-------------+| test01 | smbms_address | addressDesc | utf8mb3 | utf8mb3_general_ci | varchar(50) || test01 | smbms_address | contact | utf8mb4 | utf8mb4_0900_ai_ci | varchar(15) || test01 | smbms_address | createdBy | NULL | NULL | bigint || test01 | smbms_address | creationDate | NULL | NULL | datetime || test01 | smbms_address | id | NULL | NULL | bigint || test01 | smbms_address | modifyBy | NULL | NULL | bigint || test01 | smbms_address | modifyDate | NULL | NULL | datetime || test01 | smbms_address | postCode | utf8mb3 | utf8mb3_general_ci | varchar(15) || test01 | smbms_address | tel | utf8mb4 | utf8mb4_0900_ai_ci | varchar(20) || test01 | smbms_address | userId | NULL | NULL | bigint |+--------------+---------------+--------------+--------------------+--------------------+-------------+10 rows in set (0.01 sec)
复制代码
根据 information_schema.columns表中相关信息，可以看到 contact 列、tel 列的字符集都为 utf8mb4，排序规则为默认的 utf8mb4_0900_ai_ci；为什么建表时没有指定列所使用的字符集，但还是使用了表的字符集和排序规则？
MySQL 手册介绍
通过以下方式选择列的字符集和排序规则：
如果建表时指定了列的字符集和排序规则，则使用指定的字符集和排序规则；
为列指定了字符集，但没有指定排序规则，则使用该字符集默认的排序规则，可使用 show character set 语句或查询 character sets 表；
为列指定了排序规则，但没有指定字符集。列具有排序规则，字符集则是与排序规则相关联的字符集；
没有为列指定字符集或排序规则，因此使用表的默认字符集和排序规则。
这段描述可以解释为什么在创建表时，如果没有明确指定列的字符集，则会使用该表或数据库的默认字符集。这意味着，如果为 VARCHAR 类型的列没有指定字符集，它将继承表或数据库层面定义的字符集。
2.13 修复从库
采用重建表结构恢复数据后再重新配置同步的方式
1.关闭复制同步
greatsql> STOP SLAVE;Query OK, 0 rows affected, 1 warning (0.03 sec)
复制代码
2.删除表，并新建表
greatsql> DROP TABLE `smbms_address`;Query OK, 0 rows affected, 1 warning (0.03 sec)
greatsql> CREATE TABLE `smbms_address` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `contact` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '联系人姓名', `addressDesc` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '收货地址明细', `postCode` varchar(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '邮编', `tel` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '联系人电话', `createdBy` bigint DEFAULT NULL COMMENT '创建者', `creationDate` datetime DEFAULT NULL COMMENT '创建时间', `modifyBy` bigint DEFAULT NULL COMMENT '修改者', `modifyDate` datetime DEFAULT NULL COMMENT '修改时间', `userId` bigint DEFAULT NULL COMMENT '用户ID', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
复制代码
3.重新导入数据
greatsql> LOAD DATA INFILE '/data/smbms_address.txt' INTO TABLE test01.smbms_address FIELDS TERMINATED BY '_~' ENCLOSED BY '"';
复制代码
4.设置 gtid,配置同步
greatsql> SHOW MASTER STATUS;+---------------+----------+--------------+------------------+------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+------------------------------------------+| binlog.000001 | 1693 | | | 28093c86-5631-11ef-87f4-00163eab83df:1-2 |+---------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)
greatsql> RESET MASTER;greatsql> RESET SLAVE; greatsql> SET GLOBAL GTID_PURGED='28093c86-5631-11ef-87f4-00163eab83df:1-2';greatsql> CHANGE MASTER TO MASTER_HOST = '172.17.140.13',MASTER_USER = 'replabc',MASTER_PASSWORD = '!QAZ2WSX',MASTER_PORT = 5506, MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1693;Query OK, 0 rows affected, 8 warnings (0.05 sec)
复制代码
5.启动同步
greatsql> START SLAVE;greatsql> SHOW SLAVE STATUS \G*************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.17.140.13 Master_User: replabc Master_Port: 5506 Connect_Retry: 60 Master_Log_File: binlog.000001 Read_Master_Log_Pos: 8659 Relay_Log_File: gip-relay-bin.000005 Relay_Log_Pos: 3721 Relay_Master_Log_File: binlog.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes......1 row in set, 1 warning (0.01 sec)
复制代码
3.总结
在主从复制中，必须保证主库和从库的表结构属性相一致，若表或列的字符集设置不一致，则会抛出异常，导致同步中断。这也是本次同步报错的原因。
若对字符串类型的列存储的数据有特殊要求时，可显示的为列指定字符集。
划线
评论
复制
发布于: 刚刚阅读数: 5
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/3ad07ea024e80e5c41657413d】。文章转载请联系作者。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论