Duplicate Key Error on Re-Insert After Rolling Back to Savepoint in Upgraded TiDB v6.5.2

[SharleneTseng]

Bug Report

1. Minimal reproduce step (Required)

Steps to reproduce the issue:

1. Deploy TiDB v5.2.2 using TiDB Operator.

2. Update the version in the TidbCluster configuration to upgrade TiDB to v6.5.2.

3. Run the following SQL, which results in an error:

   CREATE DATABASE IF NOT EXISTS test;
   USE test;
   CREATE TABLE IF NOT EXISTS users (
       id INT NOT NULL,
       name VARCHAR(64) NOT NULL,
       PRIMARY KEY (id)
   );
   BEGIN;
   SAVEPOINT p1;
   INSERT INTO users (id, name) VALUES (1, 'Amy');
   SELECT * FROM users; -- This will show `Amy`
   ROLLBACK TO SAVEPOINT p1;
   SELECT * FROM users; -- The result should be an empty set
   INSERT INTO users (id, name) VALUES (1, 'Amy'); -- However, it will throw an error: ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'

The upgrade was performed using TiDB Operator instead of TiUP, by updating the version in the TidbCluster configuration to v6.5.2.

This issue only occurs when upgrading from v5.2.2 to v6.5.2. If a fresh TiDB v6.5.2 instance is deployed, the issue does not occur.

I have compared the global variables settings between the new v6.5.2 deployment and the upgraded v6.5.2, and found no differences.

2. What did you expect to see? (Required)

After executing ROLLBACK TO SAVEPOINT p1, all changes made after the savepoint should be rolled back, allowing the same data to be inserted again without causing a duplicate key error.

3. What did you see instead (Required)

After executing ROLLBACK TO SAVEPOINT p1, attempting to insert the same data again results in the following error:

ERROR 1062 (23000): Duplicate entry '1' for key 'users.PRIMARY'

4. What is your TiDB version? (Required)

Upgraded TiDB version: v6.5.2

[cfzjywxk]

@crazycs520 Please take a look at the savepoint question if you have time.