LOAD DATA LOCAL INFILE 文件入库时panic

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

  • 【TiDB 版本】:4.0.9
  • 【问题描述】:使用 LOAD DATA LOCAL INFILE将文件入库到tidb中时,如果文件行数较小,则能正常入库,当文件行数超过一定数量时(此case为425869行),发生panic,实际只入库了60000行
    ps:入库的表的主键id为auto_random

执行入库命令:
mysql -N -h ip -u u_test -p xxxxxx -P 4000 -D test_db -e “LOAD DATA local INFILE ‘/data/day/DZWH01_test/D36_DZWH0120210122.AVL’
INTO TABLE tbl_outdatadetailed_push_test
FIELDS TERMINATED BY ‘|’ ENCLOSED BY ‘\ ’
LINES TERMINATED BY ‘\ ’(`file_name`,`PROVINCE_CODE`,`EPARCHY_CODE`,`SERIAL_NUMBER`,
`CONTACT_NUMBER`,`CUST_NAME`,`TEAM_ID`,`GRADE`,`RSCV_1`,
`RSCV_2`,`RSCV_3`,`RSCV_4`);”

返回:ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

表结构(主键id为auto_random):
CREATE TABLE tbl_outdatadetailed_push_test (
id bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */ COMMENT ‘id’,
file_name varchar(100) DEFAULT NULL COMMENT ‘文件名’,
PROVINCE_CODE varchar(10) DEFAULT NULL COMMENT ‘省份编码’,
EPARCHY_CODE varchar(10) DEFAULT NULL COMMENT ‘用户号码地市编码’,
SERIAL_NUMBER varchar(20) DEFAULT NULL COMMENT ‘用户号码’,
CONTACT_NUMBER varchar(20) DEFAULT NULL COMMENT ‘联系号码’,
CUST_NAME varchar(500) DEFAULT NULL COMMENT ‘客户姓名’,
TEAM_ID varchar(50) DEFAULT NULL COMMENT ‘外呼团队’,
GRADE varchar(20) DEFAULT NULL COMMENT ‘优先级\r\ 阿拉伯28个英文字母进行排如:A/B/C/D…’,
RSCV_1 text DEFAULT NULL COMMENT ‘自定义字段1’,
RSCV_2 varchar(3000) DEFAULT NULL COMMENT ‘自定义字段2’,
RSCV_3 varchar(3000) DEFAULT NULL COMMENT ‘自定义字段3’,
RSCV_4 varchar(3000) DEFAULT NULL COMMENT ‘自定义字段4’,
PRIMARY KEY (id),
KEY inx_file_name (file_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=1359067 */

tidb报错日志如下:
[2021/01/22 16:44:53.238 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=20000]
[2021/01/22 16:44:53.398 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=40000]
[2021/01/22 16:44:53.486 +08:00] [INFO] [2pc.go:424] ["[BIG_TXN]"] [con=30107163] [“table ID”=6036] [size=5560000] [keys=40000] [puts=40000] [dels=0] [locks=0] [checks=0] [txnStartTS=422393962315120710]
[2021/01/22 16:44:53.551 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=60000]
[2021/01/22 16:44:53.642 +08:00] [INFO] [load_data.go:243] [“commit one task success”] [conn=30107163] [“commit time usage”=403.747058ms] [“keys processed”=20000] [“tasks processed”=1] [“tasks in queue”=2]
[2021/01/22 16:44:53.714 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=80000]
[2021/01/22 16:44:53.903 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=100000]
[2021/01/22 16:44:53.937 +08:00] [INFO] [2pc.go:424] ["[BIG_TXN]"] [con=30107163] [“table ID”=6036] [size=5560000] [keys=40000] [puts=40000] [dels=0] [locks=0] [checks=0] [txnStartTS=422393962459299919]
[2021/01/22 16:44:54.055 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=120000]
[2021/01/22 16:44:54.114 +08:00] [INFO] [load_data.go:243] [“commit one task success”] [conn=30107163] [“commit time usage”=471.594043ms] [“keys processed”=20000] [“tasks processed”=2] [“tasks in queue”=4]
[2021/01/22 16:44:54.206 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=140000]
[2021/01/22 16:44:54.359 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=160000]
[2021/01/22 16:44:54.370 +08:00] [INFO] [2pc.go:424] ["[BIG_TXN]"] [con=30107163] [“table ID”=6036] [size=5560000] [keys=40000] [puts=40000] [dels=0] [locks=0] [checks=0] [txnStartTS=422393962590371851]
[2021/01/22 16:44:54.506 +08:00] [INFO] [load_data.go:417] [“batch limit hit when inserting rows”] [conn=30107163] [maxBatchRows=1024] [totalRows=180000]
[2021/01/22 16:44:54.580 +08:00] [INFO] [load_data.go:243] [“commit one task success”] [conn=30107163] [“commit time usage”=466.261342ms] [“keys processed”=20000] [“tasks processed”=3] [“tasks in queue”=6]
[2021/01/22 16:44:54.592 +08:00] [ERROR] [load_data.go:216] [“CommitWork panicked”] [conn=30107163] [r={}] [stack=“github.com/pingcap/tidb/executor.(*LoadDataInfo).CommitWork.func1\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/load_data.go:218\ runtime.gopanic\ \t/usr/local/go/src/runtime/panic.go:679\ runtime.goPanicIndex\ \t/usr/local/go/src/runtime/panic.go:75\ngithub.com/pingcap/tidb/executor.getKeysNeedCheckOneRow\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/batch_checker.go:107\ github.com/pingcap/tidb/executor.getKeysNeedCheck\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/batch_checker.go:86\ github.com/pingcap/tidb/executor.(*InsertValues).batchCheckAndInsert\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/insert_common.go:936\ngithub.com/pingcap/tidb/executor.(*LoadDataInfo).CheckAndInsertOneBatch\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/load_data.go:440\ngithub.com/pingcap/tidb/executor.(*LoadDataInfo).CommitOneTask\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/load_data.go:190\ngithub.com/pingcap/tidb/executor.(*LoadDataInfo).CommitWork\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/load_data.go:238\ngithub.com/pingcap/tidb/server.(*clientConn).handleLoadData\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1273\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuerySpecial\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1420\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1404\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1005\ngithub.com/pingcap/tidb/server.(*clientConn).Run\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:778\ngithub.com/pingcap/tidb/server.(*Server).onConn\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/server.go:439”]
[2021/01/22 16:44:54.592 +08:00] [ERROR] [conn.go:1235] [“load data process stream error”] [conn=30107163] [error=“processStream forced to quit”]
[2021/01/22 16:44:54.592 +08:00] [WARN] [conn.go:765] [“read packet failed, close this connection”] [conn=30107163] [error="[server:8052]invalid sequence 209 != 0"]

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

您好,我们已经成功复现该问题,正在调查中。

@tao7
set @@tidb_dml_batch_size = 1000000;

可以先试着调大 tidb_dml_batch_size 绕过去。

2 个赞

谢谢老师,这个方案实测可行

:+1:

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。