Different Query Results Between TiFlash and TiKV

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

Original topic: tiflash与tikv查询结果不同

| username: magongyong

【TiDB Usage Environment】Production Environment
【TiDB Version】6.5.5
【Reproduction Path】Operations performed that led to the issue
The same SQL query can retrieve data from TiFlash

But TiKV cannot retrieve data

【Encountered Issue: Issue Phenomenon and Impact】
The same SQL query can retrieve data from TiFlash

But TiKV cannot retrieve data


【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

| username: magongyong | Original post link

May I ask what is going on here? TiFlash queries are fine, but why didn’t TiKV return any results?

| username: magongyong | Original post link

The structure of the first table

The structure of the second table

| username: xfworld | Original post link

Show the execution plans for both SQL queries.

| username: 小于同学 | Original post link

Take a look at the execution plan.

| username: TiDBer_rvITcue9 | Original post link

Just take a look at the SQL execution plan.

| username: DBAER | Original post link

Look at the execution plan.

| username: magongyong | Original post link

I already know what the problem is; it’s an index issue. The inconsistency between the index data and the table data is caused by loading data into the table. Refer to this post: TiDB表的索引数据和主表数据不一致 - TiDB 的问答社区
I don’t know when this issue will be fixed.

| username: dba-kit | Original post link

There’s actually no good solution here. For now, rebuilding the index is relatively reliable.

| username: redgame | Original post link

After loading, the index needs to be rebuilt.

| username: TiDBer_aaO4sU46 | Original post link

Rebuild index

| username: residentevil | Original post link

Do load data operations require rebuilding indexes? :sweat_smile:

| username: magongyong | Original post link

Yes, I didn’t expect this problem to occur. Currently, it is resolved by rebuilding and performing regular index checks.

| username: dba远航 | Original post link

It feels more like a design bug in MySQL.

| username: TiDBer_rvITcue9 | Original post link

You can report the bug now.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.