[MYSQL] mysql数据导入报错主键冲突 ERROR 1062 (23000) at line 38: Duplicate entry

导读

导了这么多数据, 还第一次遇见导入数据的时候报错主键冲突呢

ERROR 1062 (23000) at line 38: Duplicate entry '1' for key 't20240726.PRIMARY'


分析

首先怀疑是导出的.sql文件里面数据存在重复.我们可以根据主键/报错的行 找到重复的数据. 发现是唯一的.

也就是源端数据和导出的数据均没得问题, 那就是目标环境的问题咯. 但我们导出的sql文件是包含drop操作的, 即先drop,然后create,然后lock table, insert. 最后在unlock

DROP TABLE IF EXISTS `t20240726`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t20240726` (
`id` bigint NOT NULL,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t20240726`
--

LOCK TABLES `t20240726` WRITE;
/*!40000 ALTER TABLE `t20240726` DISABLE KEYS */;
INSERT INTO `t20240726` VALUES (1,'ddcw');
/*!40000 ALTER TABLE `t20240726` ENABLE KEYS */;
UNLOCK TABLES;


导入是锁表导入的啊, 理论上只有create table 到 lock table之间ms级的时间去写入数据才会导致主键冲突. 难道应用真的会这么做? 准备解析binlog的时候, 看到2分钟500M的binlog.(我们导入数据是有set session sql_log_bin=off的.) 也就是只有业务产生binlog, 也就是业务乘我们导入数据的时候拼命往数据库里面写入数据. 让数据导入产生主键冲突并中断. 解析binlog数据发现确实是存在该行数据的写入的.

复现

准备数据

create database if not exists db1;
use db1;
create table t20240726(id bigint primary key, name varchar(200));
insert into t20240726 values(1,'ddcw');


导出数据

mysqldump -h127.0.0.1 -P3314 -uroot -p123456 --databases db1 --tables t20240726 --set-gtid-purged=off > /tmp/t20240726.sql


我们使用shell模拟业务更新表

while true;do mysql -h127.0.0.1 -P3314 -p123456 db1 -e "INSERT INTO db1.t20240726 VALUES (1,'ddcw');" >/dev/null 2>&1 && echo OK;done


然后我们导入数据:

mysql -h127.0.0.1 -P3314 -p123456 db1 < /tmp/t20240726.sql


可能得多试几次才能遇到, 毕竟create table和lock table之间就一丢丢时间.

后续思考

没想到导入数据还能遇到主键冲突

数据导入的时候是否加上read_only更好. 不然这种几百GB的数据导入一半遇到这种坑又得重新导入.