the default value of tidb_partition_prune_mode is set to dynamic

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

create table t2(c1 int primary key, c2 int, c3 int, c4 int, key k2(c2), key k3(c3)) partition by hash(c1) partitions 10;
insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
/* s1 */ set autocommit = 0;
/* s1 */ set innodb_lock_wait_timeout = 0;
/* s1 */ set tidb_txn_mode = pessimistic;

/* s2 */ set autocommit = 0;
/* s2 */ set innodb_lock_wait_timeout = 0;
/* s2 */ set tidb_txn_mode = pessimistic;

/* s3 */ set autocommit = 0;
/* s3 */ set tidb_txn_mode = optimistic;

/* s1 */ select * from t2 where c4 > 2 for update;

/* s2 */ insert into t2 values(5,5,5,5);
/* s2 */ update t2 set c4 = c4 + 1 where c3 = 3; 
/* s2 */ select c1, c3 from t2 where c3 = 4 for update nowait;

/* s3 */ update t2 set c4 = c4 * 10 where c4 = 4;
/* s1 */ commit; 
/* s3 */ commit; ---check

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

commit return err:

mysql> /* s3 */ commit;
ERROR 9007 (HY000): Write conflict, txnStartTS=426968889891487745, conflictStartTS=426968878724153348, conflictCommitTS=426968891150041089, key={tableID=53, handle=4} primary={tableID=53, handle=4} [try again later]

3. What did you see instead (Required)

commit success

mysql> /* s3 */ commit;
Query OK, 0 rows affected (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.2.0-alpha-597-g26237b35f
Edition: Community
Git Commit Hash: 26237b35f857c2388eab46f9ee3b351687143681
Git Branch: master
UTC Build Time: 2021-08-12 04:28:47
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[vivid392845427]

another test scenario that does not meet expectations：

/* s1 */ create table t2(c1 int primary key, c2 int, c3 int, c4 int, unique key uk2(c2), key k3(c3));
/* s1 */ insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
/* s1 */ set autocommit = 0;
/* s1 */ set innodb_lock_wait_timeout = 0;
/* s1 */ set tidb_txn_mode = pessimistic;

/* s3 */ set autocommit = 0;
/* s3 */ set tidb_txn_mode = optimistic;

/* s1 */ select * from t2 where c4 > 2 for update;

/* s3 */ update t2 set c4 = c4 * 10 where c4 = 4;

/* s1 */ commit; 

/* s3 */ commit; ---return error:Write conflict

do the same operations, but commit success;

/* s1 */ drop table if exists t2;
/* s1 */ create table t2(c1 int primary key, c2 int, c3 int, c4 int, unique key uk2(c2), key k3(c3));
/* s1 */ insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
/* s1 */ set autocommit = 0;
/* s1 */ set innodb_lock_wait_timeout = 0;
/* s1 */ set tidb_txn_mode = pessimistic;

/* s3 */ set autocommit = 0;
/* s3 */ set tidb_txn_mode = optimistic;

/* s1 */ select * from t2 where c4 > 2 for update;

/* s3 */ update t2 set c4 = c4 * 10 where c4 = 4;

/* s1 */ commit; 

/* s3 */ commit; ---actual success，expect return error：Write conflict

[you06]

do the same operations, but commit success;

I meet the problem with the partition table only, is the partition statement missing in the second code section?

[vivid392845427]

do the same operations, but commit success;

I meet the problem with the partition table only, is the partition statement missing in the second code section?

ordinary table excute the same operation twice, and it will be submitted successfully

[you06]

@vivid392845427 thanks, this case is confirmed.

[you06]

do the same operations, but commit success;

I meet the problem with the partition table only, is the partition statement missing in the second code section?

ordinary table excute the same operation twice, and it will be submitted successfully

In the second round, session1 executes /* s1 */ insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4); without commit, that's why no conflict is notified by session3.

[you06]

A simple reproduce.

/* init */ drop table if exists t2;
/* init */ create table t2(c1 int primary key, c2 int, c3 int, c4 int, key k2(c2), key k3(c3)) partition by hash(c1) partitions 10;
/* init */ insert into t2 values (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);

/* s3 */ set tidb_general_log = 1;
/* s3 */ set autocommit = 0;
/* s3 */ set tidb_txn_mode = optimistic;
/* s3 */ update t2 set c4 = c4 * 10 where c4 = 4;
/* s3 */ commit; 

/* s3 */ select * from t2; -- 0 rows returned
/* s3 */ select * from t2; -- 4 rows returned, the effect of update is lost

[you06]

After #26972, the default value of tidb_partition_prune_mode is changed to dynamic.

[you06]

So there are two problems in this issue.

• Why default value of tidb_partition_prune_mode is changed.
• In dynamic mode, first statement in autocommit=0 transaction can not detect partitions.

[you06]

The reason why tidb_partition_prune_mode is wrong is the usage of PD packages.

TiDB highly depends on flags when bootstrapping environment variables.

tidb/server/server.go

Line 91 in 50572f1

runInGoTest = flag.Lookup("test.v") != nil || flag.Lookup("check.v") != nil

Here tidb_partition_prune_mode is set to the test value though it's in production environment.

tidb/session/bootstrap.go

Lines 1661 to 1667 in 50572f1

	if v.Name == variable.TiDBPartitionPruneMode {
	vVal = variable.DefTiDBPartitionPruneMode
	if flag.Lookup("test.v") != nil || flag.Lookup("check.v") != nil || config.CheckTableBeforeDrop {
	// enable Dynamic Prune by default in test case.
	vVal = string(variable.Dynamic)
	}
	}

As BR moved into TiDB and BR use the packages from PD, the flags set by PD will be inherited by TiDB. Then the unexpected behavior appears.

[tisonkun]

@you06 could you cross reference to the PD issue number?

[tisonkun]

OK I can see the corresponding PR is tikv/pd#3999

[tisonkun]

@you06 can we close this issue since #27337 merged?

[you06]

@tisonkun not yet, this issue actually reports a bug of the new feature, as I mentioned here, the second problem still needs to be fixed.

So there are two problems in this issue.

* Why default value of `tidb_partition_prune_mode` is changed.

* In `dynamic` mode, first statement in `autocommit=0` transaction can not detect partitions.

[you06]

tikv/pd#3999 temporarily solves the issue of the default value, I filed up an issue for tracing a complete solution in #27351. If you're interested in this problem, check it out.

[cfzjywxk]

@you06
Shall we close this issue and open another for the dynamic mode problem?

[you06]

@you06
Shall we close this issue and open another for the dynamic mode problem?

Open a new one is a good idea, there are already many discussions about the flag problem in this issue.

[you06]

So there are two problems in this issue.

* Why default value of `tidb_partition_prune_mode` is changed.

* In `dynamic` mode, first statement in `autocommit=0` transaction can not detect partitions.

The further solution to the first problem can be discussed in #27351.

Move the second problem to #27532, will investigate in that issue.

Close this issue.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

The check.v flags inherited from PD turned TiDB's bootstrap mode to the test environment, which ships unstable features by mistake.

2. Symptom (optional)

Check the tidb_partition_prune_mode's value.

MySQL [test]> select @@tidb_partition_prune_mode;
+-----------------------------+
| @@tidb_partition_prune_mode |
+-----------------------------+
| static                      |
+-----------------------------+
1 row in set (0.001 sec)

3. All Trigger Conditions (optional)

All the users will use the unstable feature by default.

4. Workaround (optional)

5. Affected versions

unreleased

6. Fixed versions

master

[ti-srebot]

( AffectedVersions ) fields are empty.
The values in ( AffectedVersions ) fields are incorrect.