建立索引时 ddl jobs 中的 ROW_COUNT 超出实际数据记录数

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】
v4.0.10

【问题描述】
在为TPCH 生成的LINEITEM表的l_shipdate列建立索引时,使用 admin show ddl jobs 查看任务,发现该任务的ROW_COUNT 超过实际记录数,最终导致索引构建不成功
这是实际记录数
image
这是admin show ddl jobs的输出结果


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

  1. 这个表只有一个索引吗? 麻烦贴一下表结构。
  2. 麻烦看一下tidb.log中报什么错,多谢。

1.没有其他索引

表结构

索引建立语句
create index l_shipdate_idx on LINEITEM(L_SHIPDATE);

2.这是截取的部分tidb.log日志
[2021/01/27 13:36:45.697 +08:00] [INFO] [tidb.go:219] [“rollbackTxn for ddl/autocommit failed”]
[2021/01/27 13:36:45.697 +08:00] [WARN] [session.go:1123] [“run statement failed”] [conn=3] [schemaVersion=3035] [error="[ddl:8214]Cancelled DDL job"] [session="{\n “currDBName”: “tpch1”,\n “id”: 3,\n “status”: 2,\n “strictMode”: true,\n “user”: {\n “Username”: “root”,\n “Hostname”: “192.168.101.243”,\n “CurrentUser”: false,\n “AuthUsername”: “root”,\n “AuthHostname”: “%”\n }\n}"]
[2021/01/27 13:36:45.702 +08:00] [INFO] [conn.go:793] [“command dispatched failed”] [conn=3] [connInfo=“id:3, addr:192.168.101.243:14200 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“create index l_shipdate_idx on LINEITEM(L_SHIPDATE)”] [txn_mode=PESSIMISTIC] [err="[ddl:8214]Cancelled DDL job\ngithub.com/pingcap/errors.AddStack\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/juju_adaptor.go:15\ngithub.com/pingcap/tidb/ddl.(*ddl).doDDLJob\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/ddl/ddl.go:523\ngithub.com/pingcap/tidb/ddl.(*ddl).CreateIndex\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:4032\ngithub.com/pingcap/tidb/executor.(*DDLExec).executeCreateIndex\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/executor/ddl.go:206\ngithub.com/pingcap/tidb/executor.(*DDLExec).Next\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/executor/ddl.go:88\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/executor/executor.go:262\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/executor/adapter.go:520\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/executor/adapter.go:402\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/executor/adapter.go:355\ngithub.com/pingcap/tidb/session.runStmt\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/session/tidb.go:308\ngithub.com/pingcap/tidb/session.(*session).executeStatement\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/session/session.go:1120\ngithub.com/pingcap/tidb/session.(*session).execute\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/session/session.go:1236\ngithub.com/pingcap/tidb/session.(*session).Execute\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/session/session.go:1163\ngithub.com/pingcap/tidb/server.(*TiDBContext).Execute\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/server/driver_tidb.go:248\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/server/conn.go:1374\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/server/conn.go:1005\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/server/conn.go:778\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/tidb_v4.0.10/go/src/github.com/pingcap/tidb/server/server.go:439\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357"]
[2021/01/27 13:36:51.716 +08:00] [INFO] [coprocessor.go:1034] ["[TIME_COP_WAIT] resp_time:896.5365ms txnStartTS:422504250949500929 region_id:24 store_addr:192.168.103.76:20160 kv_wait_ms:895"]
[2021/01/27 13:36:56.349 +08:00] [INFO] [coprocessor.go:1034] ["[TIME_COP_WAIT] resp_time:2.590554945s txnStartTS:422504251709718535 region_id:40 store_addr:192.168.103.60:20160 kv_wait_ms:2589"]

  1. 导入时使用的 lightning local backend模式吗?使用的哪个版本的lightning?
  2. 麻烦展示下这个表 show table regions 的结果

1 使用的是lightning local backend模式,lightning版本的是v4.0.3的,导入数据时TiDB集群版本是v4.0.0,导入数据之后使用TiUP将集群升级到v4.0.10

2 show table lineitem regions 的结果如下所示
regions.txt (20.5 KB)

参考这个方法解决下 tidb 创建索引失败

好的,谢谢

我在做完
operator add merge-region 96628 98132
operator add split-region 98132 --policy=scan 操作后

使用 show table LINEITEM regions; 出现重复的region id


这个是成功了吗

你可以过一会再查看一下,看起来是同一个region id,是否还存在。