故障解析丨导入字符串NULL导致主从报错
1.背景概述
目前需要搭建一个从库,由于单表数据量较大,时间比较有限,考虑到导入导出的时间,并且GreatSQL支持并行load data的功能,能够加速数据的导入,因此决定使用 select into outfile 和 load data 的方式进行数据的迁移;
在数据导入完成后进行数据同步,从库发生报错 1032 找不到记录,但是登录从库中可以查询到此条记录,这里就很奇怪;
最后通过解析relaylog,根据relaylog中的update记录,以每个字段为查询条件进行查询,发现是由于NULL值列导致的,主库这列的值是 NULL,从库在导入后导成了字符串"NULL",因此导致回放update操作时匹配不到数据而报错1032.
2.问题复现
本次测试基于 GreatSQL 8.0.32-24
2.1 初始化2个单机实例
略
2.2 主节点创建测试表
greatsql> create database test; greatsql> use test; greatsql> create table t1 (id int, name varchar(30), age int, addr varchar(30), school varchar(30), unique key (id)) engine=innodb; greatsql> insert into t1 values (1,'小红',10,'北京','一中'), (2,'小绿',11,'北京','一中'), (3,'小黄',12,'北京',NULL), (4,'小蓝',13,'北京',NULL), (5,'小黑',14,'北京',NULL);
2.3 查看数据
greatsql> select * from t1; +----+--------+------+--------+--------+ | id | name | age | addr | school | +----+--------+------+--------+--------+ | 1 | 小红 | 10 | 北京 | 一中 | | 2 | 小绿 | 11 | 北京 | 一中 | | 3 | 小黄 | 12 | 北京 | NULL | | 4 | 小蓝 | 13 | 北京 | NULL | | 5 | 小黑 | 14 | 北京 | NULL | +----+--------+------+--------+--------+ 5 rows in set (0.00 sec)
2.4 主节点导出数据
greatsql> select * from test.t1 into outfile '/greatsql/t1.csv' FIELDS TERMINATED BY '|+|' ESCAPED BY '' LINES TERMINATED BY '/*rowsxxx*/';
2.5 查看导出的数据
$ cat t1.csv 1|+|小红|+|10|+|北京|+|一中/*rowsxxx*/2|+|小绿|+|11|+|北京|+|一中/*rowsxxx*/3|+|小黄|+|12|+|北京|+|NULL/*rowsxxx*/4|+|小蓝|+|13|+|北京|+|NULL/*rowsxxx*/5|+|小黑|+|14|+|北京|+|NULL/*rowsxxx
可以看到导出的数据中包含 NULL
2.6 从库创建表并导入数据
greatsql> create database test; use test; create table t1 (id int, name varchar(30), age int, addr varchar(30), school varchar(30), unique key (id)) engine=innodb;
导入数据
greatsql> load data infile '/greatsql/t1.csv' into table test.t1 fields terminated by '|+|' ESCAPED BY '' lines terminated by '/*rowsxxx*/';
2.7 从库查询数据
greatsql> select * from test.t1; +----+--------+------+--------+--------+ | id | name | age | addr | school | +----+--------+------+--------+--------+ | 1 | 小红 | 10 | 北京 | 一中 | | 2 | 小绿 | 11 | 北京 | 一中 | | 3 | 小黄 | 12 | 北京 | NULL | | 4 | 小蓝 | 13 | 北京 | NULL | | 5 | 小黑 | 14 | 北京 | NULL | +----+--------+------+--------+--------+ 5 rows in set (0.00 sec)
2.8 从库建立复制
greatsql> reset master; Query OK, 0 rows affected (0.04 sec) greatsql> set global gtid_purged='b94e6517-68dd-11ee-b43b-00163ecb92e3:1-5755'; Query OK, 0 rows affected (0.00 sec) greatsql> show master status; +---------------+----------+--------------+------------------+---------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+---------------------------------------------+ | binlog.000001 | 153 | | | b94e6517-68dd-11ee-b43b-00163ecb92e3:1-5755 | +---------------+----------+--------------+------------------+---------------------------------------------+ 1 row in set (0.00 sec) greatsql> change master to master_user='root',master_password='greatdb',master_host='192.168.137.162',master_port=6001,master_auto_position=1; Query OK, 0 rows affected, 7 warnings (0.02 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: 192.168.137.162 Master_User: root Master_Port: 6001 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 1861574 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 395 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ......
2.9 主库更新数据
greatsql> update test.t1 set name='小小黑' where id=5;
2.10 从库查看复制状态
greatsql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 172.17.137.162 Master_User: root Master_Port: 6001 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 1863564 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 395 Relay_Master_Log_File: binlog.000002 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: 1032 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'b94e6517-68dd-11ee-b43b-00163ecb92e3:5756' at master log binlog.000002, end_log_pos 1863537. 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 ...... 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: 1032 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'b94e6517-68dd-11ee-b43b-00163ecb92e3:5756' at master log binlog.000002, end_log_pos 1863537; Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 1863537 LAST_ERROR_TIMESTAMP: 2023-10-17 10:02:46.396166 LAST_APPLIED_TRANSACTION: LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION: b94e6517-68dd-11ee-b43b-00163ecb92e3:5756 APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2023-10-17 10:02:46.392331 APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2023-10-17 10:02:46.392331 APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2023-10-17 10:02:46.393814 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 APPLYING_TRANSACTION_RETRIES_COUNT: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0 APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 1 row in set (0.00 sec)
可以看到从库发生1032报错,找不到记录
2.11 解析从库relay log
#231017 10:02:46 server id 1 end_log_pos 1863456 Table_map: `test`.`t1` mapped to number 180 \# has_generated_invisible_primary_key=0 \# at 673 \#231017 10:02:46 server id 1 end_log_pos 1863537 Update_rows: table id 180 flags: STMT_END_F \### UPDATE `test`.`t1` \### WHERE \### @1=5 /* INT meta=0 nullable=1 is_null=0 */ \### @2='小黑' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */ \### @3=14 /* INT meta=0 nullable=1 is_null=0 */ \### @4='北京' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */ \### @5=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */ \### SET \### @1=5 /* INT meta=0 nullable=1 is_null=0 */ \### @2='小小黑' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */ \### @3=14 /* INT meta=0 nullable=1 is_null=0 */ \### @4='北京' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */ \### @5=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */ \# at 754 \#231017 10:02:46 server id 1 end_log_pos 1863564 Xid = 5940 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by greatsqlbinlog */ /*!*/; DELIMITER ; \# End of log file
可以看到update更新前的数据与从库的数据一致,那么为什么还会报错 找不到记录呢?
2.12 根据relay log中的内容去从库查询数据
greatsql> select * from test.t1 where id=5; +------+--------+------+--------+--------+ | id | name | age | addr | school | +------+--------+------+--------+--------+ | 5 | 小黑 | 14 | 北京 | NULL | +------+--------+------+--------+--------+ 1 row in set (0.01 sec) greatsql> select * from test.t1 where name='小黑'; +------+--------+------+--------+--------+ | id | name | age | addr | school | +------+--------+------+--------+--------+ | 5 | 小黑 | 14 | 北京 | NULL | +------+--------+------+--------+--------+ 1 row in set (0.01 sec) greatsql> select * from test.t1 where age=14; +------+--------+------+--------+--------+ | id | name | age | addr | school | +------+--------+------+--------+--------+ | 5 | 小黑 | 14 | 北京 | NULL | +------+--------+------+--------+--------+ 1 row in set (0.00 sec) greatsql> select * from test.t1 where addr='北京'; +------+--------+------+--------+--------+ | id | name | age | addr | school | +------+--------+------+--------+--------+ | 1 | 小红 | 10 | 北京 | 一中 | | 2 | 小绿 | 11 | 北京 | 一中 | | 3 | 小黄 | 12 | 北京 | NULL | | 4 | 小蓝 | 13 | 北京 | NULL | | 5 | 小黑 | 14 | 北京 | NULL | +------+--------+------+--------+--------+ 5 rows in set (0.00 sec) greatsql> select * from test.t1 where school is null; Empty set (0.01 sec) greatsql> select * from test.t1 where school='null'; +------+--------+------+--------+--------+ | id | name | age | addr | school | +------+--------+------+--------+--------+ | 3 | 小黄 | 12 | 北京 | NULL | | 4 | 小蓝 | 13 | 北京 | NULL | | 5 | 小黑 | 14 | 北京 | NULL | +------+--------+------+--------+--------+ 3 rows in set (0.00 sec)
可以看到,根据null值作为查询条件时,匹配不到数据; 根据字符串"null" 进行匹配是可以匹配到数据
2.13 去主库进行查询
greatsql> select * from test.t1 where school is null; +------+-----------+------+--------+--------+ | id | name | age | addr | school | +------+-----------+------+--------+--------+ | 3 | 小黄 | 12 | 北京 | NULL | | 4 | 小蓝 | 13 | 北京 | NULL | | 5 | 小小黑 | 14 | 北京 | NULL | +------+-----------+------+--------+--------+ 3 rows in set (0.00 sec) greatsql> select * from test.t1 where school='null'; Empty set (0.00 sec)
在主库查询的结果与从库相反
可以得出结论,由于从库导入的数据将NULL值列的数据导入成了字符串 NULL,因此导致主从数据出现了不一致。
2.14 修复从库
greatsql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) greatsql> update test.t1 set school=NULL where school='null'; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 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.137.162 Master_User: root Master_Port: 6001 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 1863564 Relay_Log_File: relaylog.000003 Relay_Log_Pos: 435 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ......
可以看到主从状态已经恢复正常
3.总结
1.如果FIELDS ESCAPED BY字符为空字符,则没有字符被转义,并且NULL被作为NULL输出,而不是\N;这也是导致此次主从报错的原因。
2.如果这张表使用的是主键而不是唯一索引,即使某些列被导入为字符串NULL,也不会报错。
3.如果这张表没有索引或有普通索引,则会报错。
Enjoy GreatSQL :)
关于GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
相关链接
技术交流群
微信:添加GreatSQL社区助手
好友,微信号wanlidbc
发送验证信息加群
QQ群:533341697
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区有奖建议反馈: https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情: https://greatsql.cn/thread-100-1-1.html
(对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~)
技术交流群:
微信&QQ群:
QQ群:533341697
微信群:添加GreatSQL社区助手(微信号:wanlidbc
)好友,待社区助手拉您进群。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
AutoMQ 社区双周精选第二期(11.20-12.01)
本期概要 在开源的第二个双周里,作为一个成长中的开源项目,AutoMQ做了很多的优化和重构,以下是相关重点动态的总结。 AutoMQ Kafka:写链路耗时优化、快慢读隔离、Spot 实例强制回收容灾。 AutoMQ RocketMQ:历史数据冷读优化、LogCache 读写耗时优化、发布 v0.0.3-alpha 版本、发布 Helm Chart、发布文档站。 AutoMQ Kafka 精选动态 写链路耗时优化 原来所有的写入和回调都会放到一个单线程线程池去进行处理来确保数据安全,该方式存在线程上下文切换通信、单线程处理排队两个问题。本次优化将写入流程中的数据结构改造成线程并发安全模式,使得不同 stream 之间可以并发进行写入,AutoMQ Kafka 客户端平均写入耗时下降 0.3ms。 https://github.com/AutoMQ/automq-for-rocketmq/pull/728 https://github.com/AutoMQ/automq-for-rocketmq/pull/729 https://github.com/AutoMQ/automq-for-...
- 下一篇
CnosDB 科技春晚暨CnosDB 2.4.0 Milky Way发布会
CnosDB 科技春晚暨CnosDB 2.4.0 Milky Way发布会 01「突破时空极限」——CnosDB科技春晚 主题:突破时空极限 时间:2023年12月16日(星期六) 14:00-17:00 地点:卢米埃影城(侨福芳草地店) 风格:面向年轻程序员风 爱码士们,来查收本次CnosDB科技春晚的活动信息及会议议程啦,我们会将本届春晚以及发布会定调为面向年轻程序员的风格~这样的风格会与“突破时空极限”的主题碰撞出怎样的火花呢? 想要参加的小伙伴一定要看到最后哦! 02场地剧透 上一次的实景图展示,爱码士们是不是意犹未尽呢?那么在正式选择电影院为春晚主办地的现在,就让我们先来一场Venue Tour吧 以上是前方记者CC为爱码士带来的场地剧透届时会有属于CnosDB不一样的布置哦!大家和CC一起期待一下~ 03 CnosDB 2.4.0 Milky Way功能重温 在正式发布会之前不妨一起回顾2.4.0的新功能吧~ 数据汇总不再单调,新的聚合函数登场! compact_state_agg [#1359] gauge_ag...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Red5直播服务器,属于Java语言的直播服务器
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- CentOS6,CentOS7官方镜像安装Oracle11G
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2全家桶,快速入门学习开发网站教程
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- CentOS8编译安装MySQL8.0.19