7.1使用了placement policy,修改过表的索引,交换分区报错-ERROR 1731 (HY000): Non matching attribute 'index:

开心大河马 · 2023 年8 月 24 日 08:34

【 TiDB 使用环境】生产环境
【 TiDB 版本】
V7.1.0
【复现路径】
【遇到的问题：问题现象及影响】
表是auto_random以及带有历史数据的表，转储之前改动过原分区表的索引。

CREATE TABLE `t_dba_t2` (
  `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`),
  KEY `idx_opttime` (`opt_time`),
  KEY `idx_cargo_shipment_code_time` (`cargo_no`,`shipment_no`,`opt_parent_code`,`opt_time`),
  KEY `idx_cargo_no_opt_type` (`cargo_no`,`opt_type`)
) 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'), --同步老的历史数据+random新产生的数据
 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));

中间调整过索引：

 alter table t_dba_t2 add index `idx_cargo_type_time` (`cargo_no`,`opt_type`,`opt_time`);
 alter table t_dba_t2 drop index idx_cargo_no_opt_type;

根据原表 t_dba_t2 ，移除分区以及AUTO_RANDOM_BASE起始值，建立的普通表t_dba_t2_p202303：
CREATE TABLE t_dba_t2_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 ‘xxxx’,
opt_time datetime(3) NOT NULL COMMENT ‘xxxx’,
device_type varchar(50) NOT NULL COMMENT ‘xxxx’,
PRIMARY KEY (id,create_time),
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 COMMENT=‘xx表’ /*T![placement] PLACEMENT POLICY=storeonssd */;

目前交换分区报错：
use dbxxx;
alter table t_dba_t2 PARTITION P202303 placement policy storeonssd;
ALTER TABLE t_dba_t2 EXCHANGE PARTITION P202303 WITH TABLE t_dba_t2_p202303;

报错：
ERROR 1731 (HY000): Non matching attribute ‘index: idx_cargo_type_time’ between partition and table

有大神给看看，这种怎么还报错，测试区再次模拟，但是不报错。

h5n1 · 2023 年8 月 24 日 08:37

应该是bug ,placement rule 影响的

https://github.com/pingcap/tidb/issues/45791

tidb菜鸟一只 · 2023 年8 月 24 日 08:44

t_dba_t2 表上有idx_cargo_type_time这个索引吗？
或者说分区表和非分区表上的索引现在一致吗？

redgame · 2023 年8 月 27 日 09:25

查一下分区定义和表定义中的索引属性一致

有猫万事足 · 2023 年8 月 27 日 12:28

https://github.com/pingcap/tidb/blob/v7.1.0/ddl/ddl_api.go#L4503C1-L4503C1

从代码上看和PLACEMENT POLICY关系不大，是在对比两个表的索引的时候报错了。
两个表的索引应该要能对应上才可以。

开心大河马 · 2023 年8 月 28 日 01:59

我是根据原表去除分区建立的新普通表，索引是一样的。

有猫万事足 · 2023 年8 月 28 日 03:24

不过，你上面贴出来的sql t_dba_t2_p202303 这个表上还是有 idx_cargo_no_opt_type这个索引。

这个索引在源表t_dba_t2上应该是已经被drop掉了,变成idx_cargo_type_time 了吧？

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

但是你的报错是表和分区表上idx_cargo_type_time这个索引不一致，你再确认下

开心大河马 · 2023 年8 月 28 日 08:56

我是刚补的，应该是贴错了，我的意思就是当时拷贝的原表，然后做的交换分区，

开心大河马 · 2023 年9 月 6 日 07:46

之前没有严格按照生产的步骤来复现，导致没有出现。
主要是索引视图的字段值不一样，这个按理不应该是有问题的，应该产品的bug。
处理方法就是保证information_schema.tidb_indexes的INDEX_ID字段值也要一样。

具体模拟和处理方法见下面说明。
测试环境复现以及解决是可以的，生产实际使用也是可以的。

1.重新模拟模拟

**新建表带有placement的分区表：**
CREATE TABLE `t_dba_t2` (
`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`),
KEY `idx_opttime` (`opt_time`),
KEY `idx_cargo_shipment_code_time` (`cargo_no`,`shipment_no`,`opt_parent_code`,`opt_time`),
KEY `idx_cargo_no_opt_type` (`cargo_no`,`opt_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_RANDOM_BASE=870001 COMMENT='xx表' 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));

**插入自带id的历史数据：**
insert into t_dba_t2 (ID,shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values (1,'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-03-05 17:01:30.952','2023-03-05 21:35:36.898','系统');

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

insert into t_dba_t2 (ID,shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values(5,'S262314402894','WAYBILL','D262314402894','S262314402894','NULL','EXP','W011302020515','2023-04-30 17:31:30.952','2023-04-30 21:45:36.898','系统');


**插入设置值之后的随机自增ID的值：**
现插入4月的数据,4月的包含了自增的和随机的，
insert into t_dba_t2 (ID,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 t_dba_t2 (ID,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 t_dba_t2 add index `idx_cargo_type_time` (`cargo_no`,`opt_type`,`opt_time`);
 alter table t_dba_t2 drop index idx_cargo_no_opt_type;

再次插入4月的数据:
insert into t_dba_t2 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values('S262314402895','WAYBILL','D262314402895','S262314402895','NULL','EXP','W011302020515','2023-04-30 10:31:30.952','2023-04-30 11:45:36.898','系统');

insert into t_dba_t2 (shipment_no,shipment_type,cargo_no,serial_no, cargo_type, opt_parent_code, opt_type,create_time,opt_time,device_type)
values('S262314402896','WAYBILL','D262314402896','S262314402896','NULL','EXP','W011302020516','2023-04-30 12:31:30.952','2023-04-30 14:45:36.898','系统');

**根据原表 t_dba_t2 建立的普通表t_dba_t2_p202303**，
移除分区以及AUTO_RANDOM_BASE起始值的属性：

CREATE TABLE t_dba_t2_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 'xxxx',
opt_time datetime(3) NOT NULL COMMENT 'xxxx',
device_type varchar(50) NOT NULL COMMENT 'xxxx',
PRIMARY KEY (id,create_time),
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 COMMENT='xx表' PLACEMENT POLICY=storeonssd;

交换分区报错：
ALTER TABLE t_dba_t2 EXCHANGE PARTITION P202303 WITH TABLE t_dba_t2_p202303;
**ERROR 1731 (HY000):** Non matching attribute 'index: idx_cargo_type_time' between partition and table

2.查看表索引的具体属性：
select TABLE_SCHEMA,TABLE_NAME,KEY_NAME,SEQ_IN_INDEX,COLUMN_NAME,NON_UNIQUE,IS_VISIBLE,CLUSTERED,INDEX_ID from tidb_indexes where TABLE_NAME in (‘t_dba_t2’,‘t_dba_t2_p202303’) order by TABLE_SCHEMA,TABLE_NAME,KEY_NAME,SEQ_IN_INDEX;

可以看到新增的索引，只有idx_cargo_type_time的INDEX_ID字段值不一样。

3.解决方案


**重建普通表**
drop table t_dba_t2_p202303;

CREATE TABLE t_dba_t2_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 'xxxx',
opt_time datetime(3) NOT NULL COMMENT 'xxxx',
device_type varchar(50) NOT NULL COMMENT 'xxxx',
PRIMARY KEY (id,create_time),
KEY idx_opttime (opt_time),
KEY idx_cargo_shipment_code_time (cargo_no,shipment_no,opt_parent_code,opt_time),
KEY `idx_cargo_no_opt_type` (`cargo_no`,`opt_type`),    -- 先多加着
KEY idx_cargo_type_time (cargo_no,opt_type,opt_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='xx表' PLACEMENT POLICY=storeonssd;

**然后删除那个多加的索引：** 
让索引顺序字段的值一样：
alter table t_dba_t2_p202303 drop index idx_cargo_no_opt_type;

再次查看索引顺序就一模一样了：
+--------------+------------------+------------------------------+--------------+-----------------+------------+------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME       | KEY_NAME                     | SEQ_IN_INDEX | COLUMN_NAME     | NON_UNIQUE | IS_VISIBLE | CLUSTERED | INDEX_ID |
+--------------+------------------+------------------------------+--------------+-----------------+------------+------------+-----------+----------+
| test         | t_dba_t2         | PRIMARY                      |            1 | id              |          0 | YES        | YES       |        1 |
| test         | t_dba_t2         | PRIMARY                      |            2 | create_time     |          0 | YES        | YES       |        1 |
| test         | t_dba_t2         | idx_cargo_shipment_code_time |            1 | cargo_no        |          1 | YES        | NO        |        3 |
| test         | t_dba_t2         | idx_cargo_shipment_code_time |            2 | shipment_no     |          1 | YES        | NO        |        3 |
| test         | t_dba_t2         | idx_cargo_shipment_code_time |            3 | opt_parent_code |          1 | YES        | NO        |        3 |
| test         | t_dba_t2         | idx_cargo_shipment_code_time |            4 | opt_time        |          1 | YES        | NO        |        3 |
| test         | t_dba_t2         | idx_cargo_type_time          |            1 | cargo_no        |          1 | YES        | NO        |        5 |
| test         | t_dba_t2         | idx_cargo_type_time          |            2 | opt_type        |          1 | YES        | NO        |        5 |
| test         | t_dba_t2         | idx_cargo_type_time          |            3 | opt_time        |          1 | YES        | NO        |        5 |
| test         | t_dba_t2         | idx_opttime                  |            1 | opt_time        |          1 | YES        | NO        |        2 |
| test         | t_dba_t2_p202303 | PRIMARY                      |            1 | id              |          0 | YES        | YES       |        1 |
| test         | t_dba_t2_p202303 | PRIMARY                      |            2 | create_time     |          0 | YES        | YES       |        1 |
| test         | t_dba_t2_p202303 | idx_cargo_shipment_code_time |            1 | cargo_no        |          1 | YES        | NO        |        3 |
| test         | t_dba_t2_p202303 | idx_cargo_shipment_code_time |            2 | shipment_no     |          1 | YES        | NO        |        3 |
| test         | t_dba_t2_p202303 | idx_cargo_shipment_code_time |            3 | opt_parent_code |          1 | YES        | NO        |        3 |
| test         | t_dba_t2_p202303 | idx_cargo_shipment_code_time |            4 | opt_time        |          1 | YES        | NO        |        3 |
| test         | t_dba_t2_p202303 | idx_cargo_type_time          |            1 | cargo_no        |          1 | YES        | NO        |        5 |
| test         | t_dba_t2_p202303 | idx_cargo_type_time          |            2 | opt_type        |          1 | YES        | NO        |        5 |
| test         | t_dba_t2_p202303 | idx_cargo_type_time          |            3 | opt_time        |          1 | YES        | NO        |        5 |
| test         | t_dba_t2_p202303 | idx_opttime                  |            1 | opt_time        |          1 | YES        | NO        |        2 |
+--------------+------------------+------------------------------+--------------+-----------------+------------+------------+-----------+----------+



**然后再次交换分区：**
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01'), 没有分区定义

ALTER TABLE t_dba_t2 EXCHANGE PARTITION P202303 WITH TABLE t_dba_t2_p202303;
ERROR 1736 (HY000): Tables have different definitions
这个是placement的bug,
调整下分区的placement，
alter table t_dba_t2 PARTITION P202303 placement policy storeonssd;

**最后在交换分区，**
发现就可以了：
ALTER TABLE t_dba_t2 EXCHANGE PARTITION P202303 WITH TABLE t_dba_t2_p202303;

system · 2023 年11 月 5 日 07:46

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