Error When Forcing Primary Key Index

Translated
1

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

Original topic: 强制走主键索引报错

| username: 答辩潮人

[TiDB Usage Environment] Production Environment
[TiDB Version] v4.0.13
[Encountered Problem: Problem Phenomenon and Impact]

There is a set of standby databases with data consistent with the primary database. Executing the same SQL on the standby database did not report any errors.

2
| username: 答辩潮人 | Original post link

I hope everyone can provide troubleshooting ideas.

3
| username: 答辩潮人 | Original post link

To add, it’s individual tables that can’t use the primary key index, not all tables.

4
| username: 答辩潮人 | Original post link

The image you provided is not visible. Please provide the text you need translated.

5
| username: songxuecheng | Original post link

The image you provided is not visible. Please provide the text you need translated.

6
| username: 答辩潮人 | Original post link

There is still an error. Also, as I mentioned in the question, there is a backup set where the data is consistent, but there is no such error.

7
| username: Billmay表妹 | Original post link

Please provide the text you would like to translate.

8
| username: tidb菜鸟一只 | Original post link

Try removing force index (primary).

9
| username: Billmay表妹 | Original post link

This situation may be caused by the following reasons:

  1. Forcing the use of the primary key index: When you use FORCE INDEX or other methods to force the use of the primary key index in an SQL statement, TiDB may check this operation to ensure that using the primary key index is reasonable. If the check fails, it may cause an error.
  2. Inconsistent configuration between the replica and the primary: There may be differences in the configuration between the replica and the primary, such as index settings, parameter configurations, etc. These differences may cause errors when forcing the use of the primary key index on the replica.

To resolve this issue, you can try the following steps:

  1. Check the configuration of the replica: Ensure that the configuration files (such as tidb.toml) of the replica and the primary are consistent, especially the configuration items related to indexes. Compare the configuration files of the primary and the replica to see if there are any differences.
  2. Check the version of the replica: Ensure that the TiDB versions of the replica and the primary are consistent. If the versions are inconsistent, it may cause errors when the replica executes certain operations.
  3. Check the data consistency of the replica: Use TiDB tools (such as pt-table-checksum or sync_diff_inspector) to check the data consistency between the replica and the primary. Ensure that the data on the replica is completely consistent with the primary.
10
| username: 答辩潮人 | Original post link

ERROR 1815 (HY000): Internal : Can’t find a proper physical plan for this query

11
| username: 答辩潮人 | Original post link

No error reported.

12
| username: 答辩潮人 | Original post link

The current situation is that the primary database has reported an error, but the secondary database has not.

13
| username: 大飞哥online | Original post link

Try using a different index to see if the same error occurs.

14
| username: wakaka | Original post link

Is there a TiFlash replica? Try removing this TiFlash replica.

15
| username: 路在何chu | Original post link

Collect statistical information and take a look.

16
| username: Billmay表妹 | Original post link

Based on the information you provided, an error ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query occurred when executing a query.

This error usually indicates that TiDB cannot find a suitable physical execution plan for the query. A physical execution plan refers to the specific execution method chosen by TiDB when executing a query, including which indexes to use, which join methods to employ, etc.

This issue may be caused by the following reasons:

  1. Query complexity is too high: If the query involves multiple tables, multiple join conditions, or complex filtering conditions, TiDB may have difficulty selecting a physical execution plan. In this case, you can try to optimize the query, such as by adding indexes, splitting the query, or adjusting the query conditions to reduce the query complexity.

  2. Inaccurate statistics: TiDB uses statistics to estimate the cost of a query and choose the optimal physical execution plan. If the statistics are inaccurate or outdated, TiDB may choose an inappropriate execution plan. You can try updating the statistics by using the ANALYZE TABLE command to collect the latest statistics.

  3. TiDB version issues: Some TiDB versions may have bugs or limitations in execution plan selection. You can try upgrading to the latest TiDB version to get better execution plan selection.

To resolve this issue, you can try the following steps:

  1. Optimize the query: Check the query statement and try to optimize the query complexity, such as by adding indexes, splitting the query, or adjusting the query conditions.

  2. Update statistics: Use the ANALYZE TABLE command to update the table’s statistics to ensure TiDB uses the latest statistics for execution plan selection.

  3. Upgrade TiDB version: If you are using an older version of TiDB, try upgrading to the latest version to get better execution plan selection and bug fixes.

If the problem persists, it is recommended to provide more context information, specific query statements, and table structures for more detailed analysis and answers.

17
| username: 答辩潮人 | Original post link

Changing the index won’t work.

18
| username: 答辩潮人 | Original post link

There is no TiFlash.

19
| username: 大飞哥online | Original post link

Is the table a clustered table or a non-clustered table? Please provide the complete table structure.

20
| username: Kongdom | Original post link

Sure, please provide the text you need translated.