In TiDB 6.1.2, indexes from the original table are not recognized in views, and using force index results in a "key not found" error. This works normally in TiDB Server version 5.4.3

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: heming

【TiDB Usage Environment】Production Environment / Test / Poc
Production
【TiDB Version】
6.1.2
【Reproduction Path】What operations were performed to encounter the issue
The normal task on tidb5.4.2 reported an exception after upgrading to 6.1.2. (We also started a tidbserver of version 5.4.3 and it worked fine)
【Encountered Issue: Problem Phenomenon and Impact】
Error on 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’

【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】
[2022/11/18 13:30:03.887 +08:00] [INFO] [conn.go:1149] [“command dispatched failed”] [conn=213067761280914977] [connInfo=“id:213067761280914977, 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 ‘status’ 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/errors@v0.11.5-0.20211224045212-9687c2b0f87c/normalize.go:164\ngithub.com/pingcap/tidb/planner/core.getPossibleAccessPaths\n\t/home/jenkins/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/logical_plan_builder.go:4260\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:359\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildJoin\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:684\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildResultSetNode\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:346\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildTableRefs\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:340\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:3823\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).Build\n\t/home/jenkins/agent/workspace/build-common/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/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:227\ngithub.com/pingcap/tidb/executor.(*Compiler).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/pingcap/tidb/server/driver_tidb.go:230\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/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).dispatch\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/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:559\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1571”]

2
| username: heming | Original post link

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
| username: songxuecheng | Original post link

This looks like a permissions issue.
Please share the permissions of the user used by the application.

4
| username: heming | Original post link

I can’t view images directly. Please provide the text you need translated.

5
| username: heming | Original post link

It shouldn’t be a permissions issue. TiDB 5.4.2 was fine originally. The newly added TiDB 5.4.3 is also fine.

6
| username: songxuecheng | Original post link
  1. It is suspected that the permission management in version 6.x has become stricter, so you need to check the user’s permissions.
  2. You can also try if TiDB version 6.1.1 works.
7
| username: heming | Original post link

It shouldn’t be a permission issue. The screenshots I provided show that the views were created and accessed using the user ‘tidbdba’ on 127.0.0.1, so there is no permission issue.

8
| username: songxuecheng | Original post link

I previously encountered a similar view issue, and it was also due to permission problems.

Brother, if you need help from the community, just follow the approach suggested by the person helping you, okay?

9
| username: heming | Original post link

I have the same issue in my test cluster: the newly created view prompts “key not exists”.

10
| username: songxuecheng | Original post link

I just tested it. 6.1.2 and 5.4.2 are indeed different, and this needs to be fully tested before upgrading.
If you need to solve this problem now, I suggest you use

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

Do not use force index.

11
| username: heming | Original post link

Although this way doesn’t report an error, it doesn’t use the status index.

image
image743×177 2.28 KB

12
| username: songxuecheng | Original post link

Force usage?

13
| username: heming | Original post link

It’s the same. The view loaded in 6.1.2 does not include the key.

image
image698×166 2.06 KB

14
| username: songxuecheng | Original post link

I tested it on version 6.1.2. It is using the index.

15
| username: heming | Original post link

Well, it seems that it can use the index without adding it, but adding force index results in an error.

image
image943×265 3.51 KB

16
| username: songxuecheng | Original post link

Sure. You can make modifications based on these specifics to minimize your impact. Next time, ensure thorough testing before upgrading.

A bug has been reported here. You can follow up on it later.

17
| username: heming | Original post link

Thank you. It seems that the test cases for the TiDB release are still not sufficient.

18
| username: qizheng | Original post link

There is a similar issue: TiDB cannot use indexes using view queries · Issue #38245 · pingcap/tidb · GitHub