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

开心大河马 · 2023 年8 月 8 日 07:18

【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】
使用auto random的主键，以及库级别默认使用placement rule，表交换分区就报错。
交换分区时，为什么还报错，定义不一致？


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 '主键',
  `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表' /*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 '主键',
  `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 '创建时间',
  `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表' /*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','系统'
);

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','系统'
);

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','系统'
);

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','系统'
);



交换分区时报错：
ALTER TABLE dba_t1 EXCHANGE PARTITION P202303 WITH TABLE dba_t1_p202303;
ERROR 1736 (HY000): Tables have different definitions

tidb菜鸟一只 · 2023 年8 月 8 日 07:35

应该和这个
使用PLACEMENT POLICY后，exchange partition执行失败后，影响了非分区表的插入 - 产品缺陷 - TiDB 的问答社区 (asktug.com)
是一个问题吧。

开心大河马 · 2023 年8 月 8 日 07:38

我试试看下。

开心大河马 · 2023 年8 月 8 日 07:41

我只是把分区表的老的分区交到普通表，普通表作为历史表使用，不在做任何操作，只有查询。

tidb菜鸟一只 · 2023 年8 月 8 日 07:50

嗯，上次我试了，带了placemant rule的分区表和普通表无法做exchange partition操作，
alter table t1_partition partition p202307 placement policy rule1;
alter table t1_partition exchange partition p202307 with table t1;
可以通过上面的方式跳过检查

开心大河马 · 2023 年8 月 8 日 07:58

虽然实际查询显示placement是对的，但是show create table里面的分区是没有placement policy的信息，给分区设置一次policyment后会刷新分区，可以看到分区具体配置了。

用你的方法的确是可以的，感谢。



>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 BY RANGE COLUMNS(`create_time`)
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01') ,
 PARTITION `P202304` VALUES LESS THAN ('2023-05-01'),


具体再次置换：
先设置表分区级别的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        |

再次查看分区表结构的定义：可以看到结构刷新了。
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 |
+---------+------+---------------------------------------------------------------------------------------+

原分区表插入不受影响：
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','系统'
);

普通表查看也没有问题。

system · 2023 年10 月 7 日 07:58

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。