使用loader导入数据错误

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:3.0.9
  • 【问题描述】: 使用mydumper将数据导出后,然后将数据使用loader导入至tidb集群中出现如下错误 [mysql] 2020/02/25 12:48:18 packets.go:36: unexpected EOF 2020/02/25 12:48:18 loader.go:190: [error] init cron.message_content.000000007.sql checkpoint error:initialize checkpoint: invalid connection 2020/02/25 12:48:18 loader.go:150: [fatal] restore data file (cron.message_content.000000007.sql) failed, err: initialize checkpoint: invalid connection

可以检查下下面几个方面:

  1. loader 使用的用户在下游的 TiDB 是否有权限,建议使用权限较大的用户先进行导入。
  2. 检查 loader 所在的机器能否正常链接下游的 TiDB 。
  3. 请问是否之前已经进行过导入,失败了再次导入,如果是请检查下下游 TiDB tidb_loader.checkpoint 的数据是否有异常。

加粗示例 我每loader一个库成功的时候都会删除tidb_loader.checkpoint。 但是loader最大的库的时候出现了这个错误,删除了重新loader也出错。这个库是4.1个G。 执行loader的用户是root

请问在报错后在不删除下游 checkpoint 的情况下能继续导入吗?

不能重新导入,会是loader创建连接造成连接超时了吗

麻烦给下加上 -L debug 再执行导入看下 报错过程看下。

2020/02/25 17:09:45 db.go:106: [warning] transaction execution costs 3.472862 seconds 2020/02/25 17:09:45 db.go:106: [warning] transaction execution costs 3.615345 seconds 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]UPDATE tidb_loader.checkpoint SET offset=30995885 WHERE id =‘b19bff’ AND filename=‘cron.esb_message_record.000000004.sql’; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]USE cron; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]INSERT INTO esb_message_record VALUES(‘665859668426309632’,‘606783012776009728’,‘medicalOrder.add’,‘医嘱新增’,1,‘2019-05-16 09:46:59.511’,30,NULL,‘2019-05-16 09:46:59.541’,‘2019-05-16 09:46:59.541’,‘192 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]USE cron; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]INSERT INTO esb_message_record VALUES(‘665699606553063424’,‘606783012776009728’,‘medicalOrder.add’,‘医嘱新增’,1,‘2019-04-10 15:28:33.426’,26,NULL,‘2019-04-10 15:28:33.452’,‘2019-04-10 15:28:33.452’,‘192 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]UPDATE tidb_loader.checkpoint SET offset=50945433 WHERE id =‘b19bff’ AND filename=‘cron.message_content.000000008.sql’; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]UPDATE tidb_loader.checkpoint SET offset=49926327 WHERE id =‘b19bff’ AND filename=‘cron.message_content.000000048.sql’; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]UPDATE tidb_loader.checkpoint SET offset=50946247 WHERE id =‘b19bff’ AND filename=‘cron.message_content.000000042.sql’; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]UPDATE tidb_loader.checkpoint SET offset=45961893 WHERE id =‘b19bff’ AND filename=‘cron.message_content.000000054.sql’; 2020/02/25 17:09:45 db.go:106: [warning] transaction execution costs 1.185358 seconds 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]USE cron; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]INSERT INTO message_content VALUES(‘665763220573601793’,‘665763220573601792’,‘2019-05-05 11:13:41’,’{“deptName”:“内三科”,“totalDoseValue”:1.0,“revocationReson”:"",“orderTypeCode”:“2”, 2020/02/25 17:09:45 db.go:106: [warning] transaction execution costs 1.654381 seconds 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]USE cron; 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]INSERT INTO message_content VALUES(‘667414453810778113’,‘667414453810778112’,‘2019-11-27 11:25:00’,’{“deptName”:“内二科”,“totalDoseValue”:1.0,“revocationReson”:"",“orderTypeCode”:“2”, 2020/02/25 17:09:45 db.go:117: [debug] [exec][sql]UPDATE tidb_loader.checkpoint SET offset=54903643 WHERE id =‘b19bff’ AND filename=‘cron.message_content.000000015.sql’; 2020/02/25 17:09:45 loader.go:165: [info] [loader][restore table data sql]/root/mydumper/backup20200224/cron/cron.message_content.000000061.sql[finished] 2020/02/25 17:09:45 loader.go:158: [info] [loader][restore table data sql]/root/mydumper/backup20200224/cron/cron.message_content.000000036.sql[start] 2020/02/25 17:09:45 db.go:301: [debug] sql:INSERT INTO tidb_loader.checkpoint (id, filename, cp_schema, cp_table, offset, end_pos) VALUES(?,?,?,?,?,?), id:b19bff, filename:gocent_esb.esb_message_content.000000036.sql, cp_schema:gocent_esb, cp_table:esb_message_content, offset:0, end_pos:64933592 [mysql] 2020/02/25 17:09:45 packets.go:36: unexpected EOF 2020/02/25 17:09:45 loader.go:190: [error] init cron.message_content.000000036.sql checkpoint error:initialize checkpoint: invalid connection 2020/02/25 17:09:45 loader.go:150: [fatal] restore data file (cron.message_content.000000036.sql) failed, err: initialize checkpoint: invalid connection

麻烦确认下下游 TiDB 日志({deploy_path}/log/tidb.log 以及 {deploy_path}/log/tidb_stderr.log) 有没有相关的报错信息。

测试了很久,发现出错在haproxy代理。loader使用的地址为代理地址造成连接被重置。改为直接loader向tidb后loader成功。

:+1::+1::+1: