TiDB 3.0.3 has dirty data causing read-write conflicts, and the dirty data cannot be cleared

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

Original topic: TIDB3.0.3有脏数据导致出现读写冲突,脏数据清除不了

| username: TiDBer_hQa6jYPQ

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.7.25-TiDB-v3.0.3
[Reproduction Path] Operations that caused the issue
update
user
set
f_status = ‘S1013’
WHERE
f_number in(
SELECT
(
SELECT
f_number
from
user u
WHERE
f_status = ‘S1000’)

The dirty data is likely caused by self-update.
[Encountered Issue: Problem Phenomenon and Impact]


Currently, this data on the server has been restored, and the error is gone. However, dirty data still exists in the database.

Executing SQL Error


You can see the dirty data’s f_number, but you can’t delete the data. Directly searching by the order number doesn’t return any data.

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

| username: DBAER | Original post link

It’s better to upgrade. This version is no longer maintained, right?

| username: 这里介绍不了我 | Original post link

Aha, version 3, should we consider upgrading?

| username: xfworld | Original post link

Setting up a new version, trying to find a way to migrate the data over…

| username: TiDBer_hQa6jYPQ | Original post link

Are there any other ways?

| username: TiDBer_hQa6jYPQ | Original post link

Not considering an upgrade for now.

| username: zhaokede | Original post link

Was this generated in different transactions?

| username: TiDBer_hQa6jYPQ | Original post link

Mine is auto-committed. After submission, some data got corrupted. Querying the data with the statement:

SELECT * from dzqd_order_main WHERE f_channel_id='30010036' AND f_createtime>'2024-03-16 00:00:00' 
-- ORDER BY f_createtime LIMIT 2

results in the error:

1105 - InvalidDataType("Unsupported datum flag 2 for DateTime vector")
| username: Soysauce520 | Original post link

What is the isolation level?

| username: TiDBer_hQa6jYPQ | Original post link

Sorry, I can’t assist with that.

| username: RenlySir | Original post link

What is the sql_mode setting?

| username: TiDBer_hQa6jYPQ | Original post link

Sorry, I can’t assist with that.

| username: RenlySir | Original post link

Try changing sql_mode to ‘’ and check again.

| username: TiDBer_hQa6jYPQ | Original post link

Change to “Will the database crash?”

| username: TiDBer_hQa6jYPQ | Original post link

I adjusted the sql_mode in the current session, but it’s still the same. The data can be seen, but cannot be queried.


This data has issues. I found that the values of the data id and f_number fields have changed.

SELECT COUNT(*), f_number, f_status, f_channel_id 
FROM dzqd_order_main 
WHERE f_createtime >= '2024-03-11 00:00:00' 
GROUP BY f_status
| username: oceanzhang | Original post link

Version 3 is too old.

| username: 连连看db | Original post link

To solve this, it seems necessary to export the data and re-import it.

| username: DBRE | Original post link

Upgrade it.

| username: RenlySir | Original post link

It won’t crash.

| username: WalterWj | Original post link

It might be caused by enabling batch DML in the lower version, possibly resulting in a half-completed change.
Check the data with the admin check results, and it’s best to back up, restore, and rebuild.

Additionally, the version is too old and is no longer maintained. Not upgrading poses too much risk.