tidb6.1.2视图里不能识别原始表里的索引key， force index 报key not found。 用5.4.3的tidb Server正常

🌞 建议反馈 产品缺陷
1

【 TiDB 使用环境】生产环境 /测试/ Poc
生产
【 TiDB 版本】
6.1.2
【复现路径】做过哪些操作出现的问题
tidb5.4.2 正常的任务 升级到 6.1.2 就报异常 。 (我们另外启动了5.4.3的tidbserver 就正常的)
【遇到的问题：问题现象及影响】
tidb 6.1.2上的报错：
(user:tidbdba time: 11:06)[db: yixintui_operate]select * from tt_advertiser force index(status) limit 3 ;
ERROR 1176 (42000): Key ‘status’ doesn’t exist in table ‘tt_advertiser’
image

【资源配置】
【附件：截图/日志/监控】
[2022/11/18 13:30:03.887 +08:00] [INFO] [conn.go:1149] [“command dispatched failed”] [conn=213067761280914977] [connInfo=“id:2130677
61280914977, addr:127.0.0.1:55001 status:10, collation:utf8_general_ci, user:tidbdba”] [command=Query] [status=“inTxn:0, autocommit:
1”] [sql=“select * from tt_advertiser force index(status) limit 2”] [txn_mode=PESSIMISTIC] [timestamp=0] [err=“[planner:1176]Key ‘st
atus’ doesn’t exist in table ‘tt_advertiser’\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.
20211224045212-9687c2b0f87c/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\t/go/pkg/mod/github.com/pingcap/e
rrors@v0.11.5-0.20211224045212-9687c2b0f87c/normalize.go:164\ngithub.com/pingcap/tidb/planner/core.getPossibleAccessPaths\n\t/home/j
enkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:1206\ngithub.com/pingcap/tidb/planner
/core.(*PlanBuilder).buildDataSource\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logic
al_plan_builder.go:4260\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode\n\t/home/jenkins/agent/workspace/bui
ld-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:359\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilde
r).buildJoin\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:684\n
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode\n\t/home/jenkins/agent/workspace/build-common/go/src/github.c
om/pingcap/tidb/planner/core/logical_plan_builder.go:346\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildTableRefs\n\t/hom
e/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:340\ngithub.com/pingcap/t
idb/planner/core.(*PlanBuilder).buildSelect\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/cor
e/logical_plan_builder.go:3823\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).Build\n\t/home/jenkins/agent/workspace/build-com
mon/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:730\ngithub.com/pingcap/tidb/planner.optimize\n\t/home/jenkins/agent/
workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:354\ngithub.com/pingcap/tidb/planner.Optimize\n\t/home/jen
kins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:227\ngithub.com/pingcap/tidb/executor.(*Compile
r).Compile\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/compiler.go:81\ngithub.com/pingcap/
tidb/session.(*session).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:
1924\ngithub.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/ping
cap/tidb/server/driver_tidb.go:230\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-c
ommon/go/src/github.com/pingcap/tidb/server/conn.go:2022\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/
agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1876\ngithub.com/pingcap/tidb/server.(*clientConn).dispat
ch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1371\ngithub.com/pingcap/tidb/server.
(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1121\ngithub.com/pingc
ap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:559\nr
untime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1571”]

2

CREATE ALGORITHM=UNDEFINED DEFINER=tidbdba@127.0.0.1 SQL SECURITY DEFINER VIEW tt_advertiser (id, advertiser_id, name, description, email, contacter, phonenumber, role, status, telephone, address, license_url, license_no, license_province, license_city, company, brand, promotion_area, promotion_center_province, promotion_center_city, industry, reason, balance, create_time, second_industry_name, first_industry_name, is_child_agent, project_id, open_date, remark, platform_agency_id, source, update_user, create_user, update_time, today_granted) AS SELECT mbg_core.tt_advertiser.id AS id,mbg_core.tt_advertiser.advertiser_id AS advertiser_id,mbg_core.tt_advertiser.name AS name,mbg_core.tt_advertiser.description AS description,mbg_core.tt_advertiser.email AS email,mbg_core.tt_advertiser.contacter AS contacter,mbg_core.tt_advertiser.phonenumber AS phonenumber,mbg_core.tt_advertiser.role AS role,mbg_core.tt_advertiser.status AS status,mbg_core.tt_advertiser.telephone AS telephone,mbg_core.tt_advertiser.address AS address,mbg_core.tt_advertiser.license_url AS license_url,mbg_core.tt_advertiser.license_no AS license_no,mbg_core.tt_advertiser.license_province AS license_province,mbg_core.tt_advertiser.license_city AS license_city,mbg_core.tt_advertiser.company AS company,mbg_core.tt_advertiser.brand AS brand,mbg_core.tt_advertiser.promotion_area AS promotion_area,mbg_core.tt_advertiser.promotion_center_province AS promotion_center_province,mbg_core.tt_advertiser.promotion_center_city AS promotion_center_city,mbg_core.tt_advertiser.industry AS industry,mbg_core.tt_advertiser.reason AS reason,mbg_core.tt_advertiser.balance AS balance,mbg_core.tt_advertiser.create_time AS create_time,mbg_core.tt_advertiser.second_industry_name AS second_industry_name,mbg_core.tt_advertiser.first_industry_name AS first_industry_name,mbg_core.tt_advertiser.is_child_agent AS is_child_agent,mbg_core.tt_advertiser.project_id AS project_id,mbg_core.tt_advertiser.open_date AS open_date,mbg_core.tt_advertiser.remark AS remark,mbg_core.tt_advertiser.platform_agency_id AS platform_agency_id,mbg_core.tt_advertiser.source AS source,mbg_core.tt_advertiser.update_user AS update_user,mbg_core.tt_advertiser.create_user AS create_user,mbg_core.tt_advertiser.update_time AS update_time,mbg_core.tt_advertiser.today_granted AS today_granted FROM mbg_core.tt_advertiser

CREATE TABLE tt_advertiser (
id bigint(20) NOT NULL AUTO_INCREMENT,
…
UNIQUE KEY uq_adver_agency_id (advertiser_id,platform_agency_id),
KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

3

你这个看起来像是权限的问题。
业务使用的用户权限发一下

4

image
image799×213 8.33 KB

5

不是权限的问题吧 原来tidb 5.4.2没问题
我新加 5.4.3 的tidb也没问题

image
image950×458 7.98 KB

6
  1. 怀疑是6.x 版本权限管理这块更严格了 所以需要你用户的权限排查一下。
  2. 你也可以试一下 6.1.1的tidb是否可以。
7

不应该是权限问题 ， 我截图的都是用127.0.0.1 tidbdba这个用户创建和访问的视图 不存在权限问题 。

8

我之前遇到的类似视图问题也是因为权限的问题。

兄弟。如果需要社区帮忙呢。就按照帮忙的人的思路去操作一下 ， ok？

9

我测试集群里面 也一样有类似问题 ： 刚创建的视图 提示 key not exists
image

10

刚测试了一下。6.1.2 和 5.4.2 确实是不一样，这个需要充分测试在升级。
你现在需要解决这个问题的话。我这边建议你使用

select /*+ force_INDEX(internal_id) / * from test.v_test vt
select /+ USE_INDEX(internal_id) */ * from test.v_test1 vt

不要使用 force index

11

这样虽然不报错了，但是并没有用到 status 索引

image
image743×177 2.4 KB

12

强制使用呢

13

一样的 6.1.2加载的视图里就不带key

image
image698×166 2.14 KB

14

image
image1656×958 194 KB

我在6.1.2测试。是使用索引的。

15

恩， 不加好像也能走索引 ， 加了 force index 就报错 。

image
image943×265 3.66 KB

16

嗯。你可以根据这些具体来修改。使你们的影响最小，下次升级 需要充分测试之后在升级

https://github.com/pingcap/tidb/issues/39234
这里提了个bug. 你可以后续关注一下。

17

谢谢 。 tidb release的 测试用例 看来还是 做得不够呀。

18

有个类似的 issue：https://github.com/pingcap/tidb/issues/38245