After upgrading TiDB from 5.2 to 5.4.3, the select query results are occasionally incorrect, but they become correct after a few minutes

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

Original topic: tidb 5.2升级到5.4.3之后select查询结果返回偶然有误,过几分钟之后再查询就正确了。

| username: Hacker_PraUOJON

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots / Logs / Monitoring】

Complete reproduced issue:

| username: zhanggame1 | Original post link

Still don’t understand where the error is, under what circumstances did it occur, and was the data being queried updated when the error occurred?

| username: TiDBer_Lp0MzSRu | Original post link

When querying by ID, the passed parameter is id=588472198, but the query result’s id is 588472197.

| username: TiDBer_q0H7v4fx | Original post link

Yes, this situation doesn’t happen every time. It occurs occasionally, and after a few minutes, querying by ID returns to normal.

| username: redgame | Original post link

That’s so weird… Can you provide a picture for us to take a look?

| username: TiDBer_q0H7v4fx | Original post link

The data above is printed out by the underlying MyBatis, including the executed SQL, parameters, and returned fields and data. It is the raw data.

| username: zhanggame1 | Original post link

Is the id an int data type and also a primary key?
It feels very strange, could there be an issue with the index?

| username: Hacker_PraUOJON | Original post link

PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
is the primary key.

| username: Anna | Original post link

:rofl: Is it really that amazing?

| username: Anna | Original post link

Is it possible that manually inserted sequences have duplicated with automatically generated ones?

| username: zhanggame1 | Original post link

The ID is the primary key, how can it be duplicated?

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

Admin, check the table ledger_cash_transactions to see if there is any inconsistency between the table and the index.

| username: Hacker_PraUOJON | Original post link

We also used the flashback method to check historical data, and it was correct.

10:28 AM

If it is wrong, it means it can be stably restored.
mysql> admin check table ledger_cash_transactions;
Query OK, 0 rows affected (14 min 43.26 sec)

If the consistency check is passed, an empty result is returned. Otherwise, an error message is returned indicating that the data is inconsistent.

| username: Hacker_PraUOJON | Original post link

We also used the flashback method to check historical data, and it was correct. The phenomenon is occasional and relatively difficult to handle.

set @@tidb_snapshot="2023-06-18 14:35:00";

use alpha_ledger;
select id from ledger_cash_transactions WHERE ( id in ( 588472057 ) );

When querying by ID, the input parameter is id=588472198, but the query result id is 588472197.

Check if the table and index are inconsistent.

mysql> admin check table ledger_cash_transactions;
Query OK, 0 rows affected (14 min 43.26 sec)

If the consistency check is passed, an empty result is returned. Otherwise, an error message is returned indicating that the data is inconsistent.