[MYSQL] load data 导致主从不一致 (无主键, 有double类型) 逻辑备份恢复导致数据不一致!

导读

之前遇到主从同步报错 1032. 在测试环境搭建一个库恢复数据到报错时间点, 然后该库回放BINLOG失败.

就认为主库无法回放自己的BINLOG, 其实是不对的, 因为使用的逻辑备份恢复. 会存在数据的不一致…

相关文章: https://www.modb.pro/db/1788489371636289536

这次终于找到原因了, 也能复现. 也提了BUG (感觉也不能算BUG) : https://bugs.mysql.com/bug.php?id=114962

测试了. 5.7.41 & 5.7.44 & 8.0.28 均能稳定复现.

复现过程

数据准备

主库准备测试数据:

SHELL> cat /home/mysql/aa.txt
ABCDEFGH,-0
ABCDEFGH,-0.0
ABCDEFGH,0.1
ABCDEFGH,-1.1


主库创建一个没得主键的表, 含有double类型.
mysql --local-infile

mysql> set global local_infile=ON;
mysql> create table db1.t20240511 (name char(10), id double);


然后主库导入含有 -0 的数据

load data local infile '/home/mysql/aa.txt' ignore into table db1.t20240511
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, id);


使用mysqldump 导出数据, 并导入到从库

#master
mysqldump -A > xxx.sql

#slave
mysql change master to xxx;


此时主库数据:

从库数据:

发现主从数据已经不一致了.

故障模拟

主库删除含有 -0 的行. 你就会在从库收到如下报错

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e0fd21e2-b69d-11ee-b935-000c29909eff:907119' at master log mysql-bin.000176, end_log_pos 1263. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

原因分析

出现上述现象的原因为: LOAD DATA 能够导入 -0 的double数据. 但是INSERT之类的DML语句, 会把-0当作0

也就导致了 导入从库的时候, -0 的double数据 就已经变为了 0. 但是 binlog里面的double类型是支持 -0 的 (ibd里面也是支持-0的.)

解决办法

1. 不使用 load data加载数据.
   不使用load data, 而是使用DML语句, 则 不存在-0 的情况

2. 使用主键
   使用主键的话, 则主从环境不会校验非主键字段, 也就不会报错 1032. 也不影响DML操作, 但数据终究不一致.

3. 业务不要使用 +0 -0 这种
   让业务那边不要使用-0, +0, 从根上解决问题.

4. 使用物理备份方式搭建主从.
   如果是在load data之前搭建的主从, 则不影响, 物理备份恢复方式也不影响. (因为ibd文件也是能区分-0的, 就DML不能, 特性?)