After Using PLACEMENT POLICY, Exchange Partition Execution Failure Affects Inserts into Non-Partitioned Tables

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

Original topic: 使用PLACEMENT POLICY后,exchange partition执行失败后,影响了非分区表的插入

| username: dba-kit

Background: I hope to convert a non-partitioned table into a partitioned table through the exchange partition operation. However, during the operation, I found that the exchange partition operation fails and affects the writing to the non-partitioned table. The error message is ERROR 1736 (HY000): Tables have different definitions. After that, when inserting data into the non-partitioned table that exceeds the corresponding partition definition, it reports ERROR 1748 (HY000): Found a row not matching the given partition set.

The operation log is as follows and can be stably reproduced:

mysql>
mysql> CREATE TABLE `t1` (
    ->   `date` date NOT NULL ,
    ->   `name` varchar(10)  NOT NULL,
    ->   UNIQUE KEY `uk_1` (`date`,`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.51 sec)

mysql> CREATE TABLE `t1_partition` (
    ->   `date` date NOT NULL ,
    ->   `name` varchar(10)  NOT NULL,
    ->   UNIQUE KEY `uk_1` (`date`,`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    -> PARTITION BY RANGE COLUMNS(`date`)
    -> (PARTITION `p202307` VALUES LESS THAN ('2023-08-01'),
    ->  PARTITION `p202308` VALUES LESS THAN ('2023-09-01'),
    ->  PARTITION `p202309` VALUES LESS THAN ('2023-10-01'),
    ->  PARTITION `p202310` VALUES LESS THAN ('2023-11-01'),
    ->  PARTITION `p202311` VALUES LESS THAN ('2023-12-01'),
    ->  PARTITION `p202312` VALUES LESS THAN ('2024-01-01'),
    ->  PARTITION `pfuture` VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into t1(date, name) values ("2023-07-02","0000"), ("2023-07-03","0000"), ("2023-07-04","0000"), ("2023-07-05","0000");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t1_partition exchange partition p202307 with table t1;
ERROR 1736 (HY000): Tables have different definitions
mysql> insert into t1(date, name) values ("2023-07-06","0000");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(date, name) values ("2023-08-06","0000");
ERROR 1748 (HY000): Found a row not matching the given partition set
mysql> insert into t1(date, name) values ("2023-07-07","0000");
Query OK, 1 row affected (0.00 sec)
| username: dba-kit | Original post link

The SQL execution is reproduced as follows:

CREATE TABLE `t1` (
  `date` date NOT NULL,
  `name` varchar(10) NOT NULL,
  UNIQUE KEY `uk_1` (`date`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t1_partition` (
  `date` date NOT NULL,
  `name` varchar(10) NOT NULL,
  UNIQUE KEY `uk_1` (`date`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE COLUMNS(`date`)
(PARTITION `p202307` VALUES LESS THAN ('2023-08-01'),
 PARTITION `p202308` VALUES LESS THAN ('2023-09-01'),
 PARTITION `p202309` VALUES LESS THAN ('2023-10-01'),
 PARTITION `p202310` VALUES LESS THAN ('2023-11-01'),
 PARTITION `p202311` VALUES LESS THAN ('2023-12-01'),
 PARTITION `p202312` VALUES LESS THAN ('2024-01-01'),
 PARTITION `pfuture` VALUES LESS THAN (MAXVALUE));

insert into t1(date, name) values ("2023-07-02","0000"), ("2023-07-03","0000"), ("2023-07-04","0000"), ("2023-07-05","0000");

-- Error occurred here
alter table t1_partition exchange partition p202307 with table t1;

insert into t1(date, name) values ("2023-07-06","0000");
insert into t1(date, name) values ("2023-08-06","0000");

-- The following was not executed
rename table t1 to t1_bak;
rename table t1_partition to t1;
| username: tidb菜鸟一只 | Original post link

Version 6.6.0 did not reproduce…
Version 5.4.3 also did not reproduce…

| username: dba-kit | Original post link

After the problem occurs, even using another partition table with LESS THAN (MAXVALUE) for exchange will continue to report an error.

mysql> CREATE TABLE `t1_partition_all` (
    ->   `date` date NOT NULL ,
    ->   `name` varchar(10)  NOT NULL,
    ->   UNIQUE KEY `uk_1` (`date`,`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    -> PARTITION BY RANGE COLUMNS(`date`)
    -> (PARTITION `pfuture` VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected (0.54 sec)

mysql> alter table t1_partition_all exchange partition pfuture with table t1;
ERROR 1736 (HY000): Tables have different definitions
mysql> insert into t1(date, name) values ("2023-07-08","0000");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1(date, name) values ("2023-08-08","0000");
ERROR 1748 (HY000): Found a row not matching the given partition set
mysql>
| username: dba-kit | Original post link

Uh, it didn’t reproduce in 7.1 either.

| username: 有猫万事足 | Original post link

7.1.1 did not reproduce

| username: dba-kit | Original post link

It’s strange. I created a new 6.5.3 cluster without any errors, but in the problematic cluster, the issue can be consistently reproduced. Even if I create other tables with different names, it still reports an error.

| username: dba-kit | Original post link

Got it, it was caused by PLACEMENT POLICY. After adding PLACEMENT POLICY, it can be stably reproduced in both 6.5.3 and 7.1.

CREATE PLACEMENT POLICY rule1 FOLLOWERS=4;
CREATE TABLE `t1` (
  `date` date NOT NULL,
  `name` varchar(10) NOT NULL,
  UNIQUE KEY `uk_1` (`date`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PLACEMENT POLICY=`rule1`;

CREATE TABLE `t1_partition` (
  `date` date NOT NULL,
  `name` varchar(10) NOT NULL,
  UNIQUE KEY `uk_1` (`date`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PLACEMENT POLICY=`rule1`
PARTITION BY RANGE COLUMNS(`date`)
(PARTITION `p202307` VALUES LESS THAN ('2023-08-01'),
 PARTITION `p202308` VALUES LESS THAN ('2023-09-01'),
 PARTITION `p202309` VALUES LESS THAN ('2023-10-01'),
 PARTITION `p202310` VALUES LESS THAN ('2023-11-01'),
 PARTITION `p202311` VALUES LESS THAN ('2023-12-01'),
 PARTITION `p202312` VALUES LESS THAN ('2024-01-01'),
 PARTITION `pfuture` VALUES LESS THAN (MAXVALUE));

insert into t1(date, name) values ("2023-07-02","0000"), ("2023-07-03","0000"), ("2023-07-04","0000"), ("2023-07-05","0000");

-- Error occurs here
alter table t1_partition exchange partition p202307 with table t1;

insert into t1(date, name) values ("2023-07-06","0000");
insert into t1(date, name) values ("2023-08-06","0000");
| username: 有猫万事足 | Original post link

7.1.1 This issue has been reproduced.

ERROR 1736 (HY000): Tables have different definitions

| username: dba-kit | Original post link

Executing this SQL will also report ERROR 1748 (HY000): Found a row not matching the given partition set, right?

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

Does configuring a placement rule on a table mean that exchange partition operations cannot be performed?

| username: dba-kit | Original post link

The failure to exchange has little impact, but non-partitioned tables cannot write beyond the partition definition limit, which is very troublesome, very troublesome…

| username: dba-kit | Original post link

I remember executing it successfully in version 6.5.2. I wonder if it is a bug introduced later.

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

6.5.2, exchange partition reported an error, but the insertion was not affected.

| username: dba-kit | Original post link

Try inserting the second record for 2023-08-06.

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

Uh, 2020308 cannot be inserted…

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

Judging by the error message, it seems to treat t1 as a partitioned table? But t1 doesn’t have a partition greater than 20230801.

| username: dba-kit | Original post link

Yes, although the exchange was not successful, the metadata has already been partially changed and it is already considered a partition of a certain table, so it will be checked.

| username: chao | Original post link

It looks like a DDL bug. I have opened an issue: exchange partition will fail when table has placement policy and then insert will fail forever · Issue #45791 · pingcap/tidb · GitHub

| username: chao | Original post link

It seems there are two issues: one is that the placement rule check was not done properly. The second is that due to the first issue, the meta in the intermediate state did not roll back after the DDL failed.