pingcap / pingcap/tidb

load data commit forced to quit

Open
#55,204 2 comments 0 reactions 0 assignees View on GitHub
affects-6.1 affects-6.5 affects-7.1 affects-7.5 affects-8.1 severity/moderate type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

this sql has 2 identical column named 'group_type':
``` sql
LOAD DATA LOCAL INFILE '/tmp/sql.csv'
IGNORE INTO TABLE t_related_group_account
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(group_id,group_key,group_type,account_id,account_type,
market_id,customer_id,one_pass_account,customer_name,
open_date,certificate_type,certificate_number,branch_id,
customer_contact,broker_name,broker_contact,branch_name,
accountsJson,data_status,business_type,permission_id,
group_type,add_time,add_user,update_time,update_user);

ERROR 1105 (HY000): commit forced to quit
```

The attachment is here:
[sql.csv](https://github.com/user-attachments/files/16504426/sql.csv)

table schema:
``` sql
CREATE TABLE `t_related_group_account` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`group_id` int(11) NOT NULL COMMENT '监控名单组Id',
`group_key` varchar(250) COLLATE utf8_general_ci DEFAULT NULL COMMENT '多账户客户的时候使用的key',
`account_id` varchar(150) COLLATE utf8_general_ci DEFAULT NULL COMMENT '账户账户',
`account_type` int(11) DEFAULT '0' COMMENT '账户类型,详见AccountType',
`one_pass_account` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '一码通账户',
`market_id` smallint(6) DEFAULT NULL COMMENT '交易所:101沪市,102深市',
`customer_id` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '客户编码',
`customer_name` varchar(250) COLLATE utf8_general_ci DEFAULT NULL COMMENT '客户名称',
`open_date` date DEFAULT NULL COMMENT '开户日期',
`certificate_type` tinyint(1) DEFAULT NULL COMMENT '证件类型:0居民身份证,1军官证,2港澳台通行证,4护照,5其他证件',
`certificate_number` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '证件号码',
`branch_id` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '营业部编码',
`customer_contact` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '客户联系方式',
`broker_name` varchar(250) COLLATE utf8_general_ci DEFAULT NULL COMMENT '客户经理名称',
`broker_contact` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '客户经理联系方式',
`branch_name` varchar(250) COLLATE utf8_general_ci DEFAULT NULL COMMENT '营业部名称',
`accountsJson` json DEFAULT NULL COMMENT '账户信息',
`data_status` int(1) DEFAULT '0' COMMENT '数据状态:-2:未更新,-1:删除,0:新增,1:已更新',
`business_type` int(11) DEFAULT '1' COMMENT '账户类型,具体枚举类型见t_dict表的BusinessType枚举',
`permission_id` varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT '权限Id',
`group_type` int(1) DEFAULT NULL COMMENT '分类:0多账户客户,1关联账户组',
`add_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '新增时间',
`add_user` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '新增人',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`update_user` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ;
```

tidb.log:
```
[2024/08/05 10:51:36.031 +08:00] [Error] [load_data.go:392] ["commit forced to quit, possible preparation failed"] [conn=5344086106851770779]
[2024/08/05 10:51:36.033 +08:00] [Info] [conn.go:1787] ["draining finished for error"] [conn=5344086106851770779] [error="commit forced to quit"]
[2024/08/05 10:51:36.033 +08:00] [Info] [conn.go:1197] ["command dispatched failed"] [conn=5344086106851770779] [connInfo="id:5344086106851770779, addr:10.129.1.200:52660 status:0, collation:utf8_general_ci, user:archforce"] [command=Query] [status="inTxn:0, autocommit:0"] [sql="LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE t_related_group_account FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'(group_id,group_key,group_type,account_id,account_type,market_id,customer_id,one_pass_account,customer_name,open_date,certificate_type,certificate_number,branch_id,customer_contact,broker_name,broker_contact,branch_name,accountsJson,data_status,business_type,permission_id,group_type,add_time,add_user,update_time,update_user)"] [txn_mode=PESSIMISTIC] [timestamp=451628577129758869] [err="commit forced to quit\ngithub.com/pingcap/tidb/executor.(*LoadDataInfo).CommitWork\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/load_data.go:391\ngithub.com/pingcap/tidb/server.(*clientConn).handleLoadData\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1767\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuerySpecial\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2217\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2196\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1988\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1419\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1168\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:692\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_arm64.s:1172"]
```

### 2. What did you expect to see? (Required)
This should not cause an error, the data will be successfully imported, just like in MySQL 5.7.
tidb v6.5.9 sql_mode is the same as mysql :
```
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
```

### 3. What did you see instead (Required)
I saw the error: ERROR 1105 (HY000): commit forced to quit.

The error should be "Column 'group_type' specified twice",
not "commit forced to quit".
There might be an issue somewhere in the process.

### 4. What is your TiDB version? (Required)
v6.5.9

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.