[Urgent] Duplicate Data Found in Index Queries for Some Tables, Primary Key Query Returns Single Record

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

Original topic: 【紧急】部分表建立索引查询数据有重复,主键查询是一条数据

| username: TiDBer_hMqJwmpc

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.1
[Encountered Problem: Phenomenon and Impact]
After creating indexes on some tables, querying through the index returns duplicate results, while querying by primary key returns only one record. Deleting the index still results in duplicate queries.

Index Query

Primary Key Query

Even deleting and recreating the index doesn’t work.

| username: 像风一样的男子 | Original post link

Can the same effect be achieved by using SQL statements without tools?

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

ADMIN CHECK INDEX tbl_name idx_name;
Let’s check the consistency of the table and index.

| username: 芮芮是产品 | Original post link

Index issue reconstruction

| username: TiDBer_hMqJwmpc | Original post link

The query results are also duplicated.

| username: TiDBer_hMqJwmpc | Original post link

Deleting the table and re-importing it, then rebuilding the index, still yields the same result.

| username: TiDBer_hMqJwmpc | Original post link

The execution result is fine.

| username: h5n1 | Original post link

Check the output of admin show ddl jobs to see if any of the indexes have the word “ingest”.

| username: TiDBer_hMqJwmpc | Original post link

Indexes are created after importing the data.

| username: TiDBer_hMqJwmpc | Original post link

Delete Index

| username: kkpeter | Original post link

We encountered this before as well. Create a new table, change the table name, and then import the data!

| username: TiDBer_hMqJwmpc | Original post link

There is no other way except this method.

| username: kkpeter | Original post link

I didn’t solve it at the time, and I didn’t find a solution either.

| username: TiDBer_hMqJwmpc | Original post link

Alright, I’ll give it a try. Thanks.

| username: TiDBer_hMqJwmpc | Original post link

| username: h5n1 | Original post link

This is to rebuild the index again. If there are still issues, try setting the parameter tidb_ddl_enable_fast_reorg to false before rebuilding the index.

| username: TiDBer_hMqJwmpc | Original post link

Yes, deleting and redoing still has issues. I’ll try again.

| username: Kongdom | Original post link

You haven’t done BR recovery, right? If you are restoring to a non-empty database using BR, you need to consider whether this is the issue.

| username: Fly-bird | Original post link

Delete and create new

| username: andone | Original post link

Delete and rebuild