After using placement policy in 7.1, partition exchange error ERROR 1736 (HY000): Tables have different definitions

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

Original topic: 7.1使用了placement policy后,交换分区报错ERROR 1736 (HY000): Tables have different definitions

| username: 开心大河马

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path]
Using an auto-random primary key and a database-level default placement rule, an error occurs when swapping partitions.
Why does it still report an error when swapping partitions, stating that the definitions are inconsistent?

CREATE DATABASE `dbxxx` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ /*T![placement] PLACEMENT POLICY=`storeonssd` */;

use dbxxx;

CREATE TABLE `dba_t1` (
  `id` bigint(20) unsigned NOT NULL  AUTO_RANDOM(5) COMMENT 'Primary Key',
  `shipment_no` varchar(50) NOT NULL COMMENT 'xxxx',
  `shipment_type` varchar(20) NOT NULL COMMENT 'xxxx',
  `cargo_no` varchar(50) NOT NULL COMMENT 'xxxx',
  `serial_no` varchar(50) DEFAULT NULL COMMENT 'xxxx',
  `cargo_type` varchar(20) NOT NULL COMMENT 'xxxx',
  `opt_type` varchar(50) NOT NULL COMMENT 'xxxx',
  `opt_parent_code` varchar(50) NOT NULL COMMENT 'xxxx',
  `create_time` datetime(3) NOT NULL COMMENT 'xxxx',
  `opt_time` datetime(3) NOT NULL COMMENT 'xxxx',
  `device_type` varchar(50) NOT NULL COMMENT 'xxxx',
  PRIMARY KEY (`id`,`create_time`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_opttime` (`opt_time`),
  KEY `idx_cargo_shipment_code_time` (`cargo_no`,`shipment_no`,`opt_parent_code`,`opt_time`),
  KEY `idx_cargo_type_time` (`cargo_no`,`opt_type`,`opt_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin  AUTO_RANDOM_BASE=870001 COMMENT='xx table' /*T![placement] PLACEMENT POLICY=`storeonssd` */
PARTITION BY RANGE COLUMNS(`create_time`)
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01'),
 PARTITION `P202304` VALUES LESS THAN ('2023-05-01'),
 PARTITION `P202305` VALUES LESS THAN ('2023-06-01'),
 PARTITION `P202306` VALUES LESS THAN ('2023-07-01'),
 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 `P202401` VALUES LESS THAN ('2024-02-01'),
 PARTITION `P202402` VALUES LESS THAN ('2024-03-01'),
 PARTITION `P202403` VALUES LESS THAN ('2024-04-01'),
 PARTITION `P202404` VALUES LESS THAN ('2024-05-01'),
 PARTITION `P202405` VALUES LESS THAN ('2024-06-01'),
 PARTITION `P202406` VALUES LESS THAN ('2024-07-01'),
 PARTITION `P202407` VALUES LESS THAN ('2024-08-01'),
 PARTITION `P202408` VALUES LESS THAN ('2024-09-01'),
 PARTITION `P202409` VALUES LESS THAN ('2024-10-01'),
 PARTITION `P202410` VALUES LESS THAN ('2024-11-01'),
 PARTITION `P202411` VALUES LESS THAN ('2024-12-01'),
 PARTITION `P202412` VALUES LESS THAN ('2025-01-01'),
 PARTITION `PMAX` VALUES LESS THAN (MAXVALUE));

CREATE TABLE `dba_t1_p202303`(
  `id` bigint(20) unsigned NOT NULL  AUTO_RANDOM(5) COMMENT 'Primary Key',
  `shipment_no` varchar(50) NOT NULL COMMENT 'xxxx',
  `shipment_type` varchar(20) NOT NULL COMMENT 'xxxx',
  `cargo_no` varchar(50) NOT NULL COMMENT 'xxxx',
  `serial_no` varchar(50) DEFAULT NULL COMMENT 'xxxx',
  `cargo_type` varchar(20) NOT NULL COMMENT 'xxxx',
  `opt_type` varchar(50) NOT NULL COMMENT 'xxxx',
  `opt_parent_code` varchar(50) NOT NULL COMMENT 'xxxx',
  `create_time` datetime(3) NOT NULL COMMENT 'Creation Time',
  `opt_time` datetime(3) NOT NULL COMMENT 'xxxx',
  `device_type` varchar(50) NOT NULL COMMENT 'xxxx',
  PRIMARY KEY (`id`,`create_time`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_opttime` (`opt_time`),
  KEY `idx_cargo_shipment_code_time` (`cargo_no`,`shipment_no`,`opt_parent_code`,`opt_time`),
  KEY `idx_cargo_type_time` (`cargo_no`,`opt_type`,`opt_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin  AUTO_RANDOM_BASE=870001 COMMENT='xx table' /*T![placement] PLACEMENT POLICY=`storeonssd` */;

insert into dba_t1 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values
(
'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-03-05 17:01:30.952','2023-03-05 21:35:36.898','System'
);

insert into dba_t1 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values
(
'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-03-30 17:01:30.952','2023-03-30 21:35:36.898','System'
);

insert into dba_t1 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values
(
'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-04-05 17:01:30.952','2023-04-30 21:35:36.898','System'
);

insert into dba_t1 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values
(
'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-04-30 17:01:30.952','2023-04-30 21:35:36.898','System'
);

Error when swapping partitions:
ALTER TABLE dba_t1 EXCHANGE PARTITION P202303 WITH TABLE dba_t1_p202303;
ERROR 1736 (HY000): Tables have different definitions
| username: tidb菜鸟一只 | Original post link

It should be the same issue as this one:
After using PLACEMENT POLICY, the execution of exchange partition failed, affecting the insertion of non-partitioned tables - Product Defects - TiDB Q&A Community (asktug.com).

| username: 开心大河马 | Original post link

I’ll give it a try.

| username: 开心大河马 | Original post link

I just handed over the old partitions of the partitioned table to a regular table, which is used as a historical table. No further operations will be performed, only queries.

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

Well, last time I tried, partitioned tables with placement rules and regular tables couldn’t perform the exchange partition operation.

alter table t1_partition partition p202307 placement policy rule1;
alter table t1_partition exchange partition p202307 with table t1;

You can bypass the check using the above method.

| username: 开心大河马 | Original post link

Although the actual query shows that the placement is correct, the partition information in the show create table does not include the placement policy. Setting the policy for the partition once will refresh the partition, and you can see the specific configuration of the partition.

Your method indeed works, thank you.

>show placement;
+------------------------------------------------------------------+---------------------------+------------------+
| Target                                                           | Placement                 | Scheduling_State |
+------------------------------------------------------------------+---------------------------+------------------+
| POLICY storeonsas                                                | CONSTRAINTS="[+disk=sas]" | NULL             |
| POLICY storeonssd                                                | CONSTRAINTS="[+disk=ssd]" | NULL             |
| DATABASE dbxxx                                                   | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1                                               | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202303                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202304                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202305                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202306                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202307                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202308                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202309                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202310                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202311                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202312                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202401                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202402                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202403                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202404                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202405                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202406                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202407                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202408                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202409                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202410                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202411                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202412                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION PMAX                                | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1_p202303                                       | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |

Partition table structure definition:
PARTITION BY RANGE COLUMNS(`create_time`)
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01') ,
 PARTITION `P202304` VALUES LESS THAN ('2023-05-01'),

Specific replacement again:
First set the table partition level placement policy:
> alter table dba_t1 partition P202303 placement policy storeonssd;
Query OK, 0 rows affected (0.28 sec)

> show placement;
+------------------------------------------------------------------+---------------------------+------------------+
| Target                                                           | Placement                 | Scheduling_State |
+------------------------------------------------------------------+---------------------------+------------------+
| POLICY storeonsas                                                | CONSTRAINTS="[+disk=sas]" | NULL             |
| POLICY storeonssd                                                | CONSTRAINTS="[+disk=ssd]" | NULL             |
| DATABASE dbxxx                                                   | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1                                               | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1 PARTITION P202303                             | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |
| TABLE dbxxx.dba_t1_p202303                                       | CONSTRAINTS="[+disk=ssd]" | SCHEDULED        |

Check the partition table structure definition again: you can see the structure has been refreshed.
PARTITION BY RANGE COLUMNS(`create_time`)
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01') **/*T![placement] PLACEMENT POLICY=`storeonssd` */,**
 PARTITION `P202304` VALUES LESS THAN ('2023-05-01'),
 PARTITION `P202305` VALUES LESS THAN ('2023-06-01'),

> alter table dba_t1 exchange partition P202303 with table dba_t1_p202303;
Query OK, 0 rows affected, 1 warning (0.33 sec)

> show warnings;
+---------+------+---------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                               |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1105 | after the exchange, please analyze related table of the exchange to update statistics |
+---------+------+---------------------------------------------------------------------------------------+

The original partition table insertion is not affected:
insert into dba_t1 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values
(
'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-04-15 17:01:30.952','2023-04-15 21:35:36.898','系统'
);

insert into dba_t1 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values
(
'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-04-20 17:01:30.952','2023-04-20 21:35:36.898','系统'
);

There is no problem with viewing the ordinary table.
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.