7.1 Using Placement Policy, Modified Table Index, Partition Exchange Error - ERROR 1731 (HY000): Non matching attribute 'index'

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

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

| username: 开心大河马

[TiDB Usage Environment] Production Environment
[TiDB Version]
V7.1.0
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
The table is an auto_random table with historical data, and the index of the original partition table was modified before dumping.

CREATE TABLE `t_dba_t2` (
  `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`),
  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 table' /*T![placement] PLACEMENT POLICY=`storeonssd` */
PARTITION BY RANGE COLUMNS(`create_time`)
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01'), -- Sync old historical data
 PARTITION `P202304` VALUES LESS THAN ('2023-05-01'), -- Sync old historical data
 PARTITION `P202305` VALUES LESS THAN ('2023-06-01'), -- Sync old historical data
 PARTITION `P202306` VALUES LESS THAN ('2023-07-01'), -- Sync old historical data + random new data
 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));

Index adjusted in between:

 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;

Based on the original table t_dba_t2, removing the partition and AUTO_RANDOM_BASE starting value, the ordinary table t_dba_t2_p202303 was created:
CREATE TABLE t_dba_t2_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 ‘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 table’ /*T![placement] PLACEMENT POLICY=storeonssd */;

Currently, partition exchange reports an error:
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:
ERROR 1731 (HY000): Non matching attribute ‘index: idx_cargo_type_time’ between partition and table

Can any expert take a look at why this still reports an error? It doesn’t report an error when simulated in the test area.

| username: h5n1 | Original post link

It should be a bug, affected by the placement rule

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

Does the t_dba_t2 table have the idx_cargo_type_time index?
Or are the indexes on partitioned and non-partitioned tables now consistent?

| username: redgame | Original post link

Check if the index attributes in the partition definition and table definition are consistent.

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

From the code, it doesn’t seem to be much related to PLACEMENT POLICY. The error occurs when comparing the indexes of the two tables. The indexes of the two tables should be able to match.

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

I created a new regular table based on the original table by removing the partitions, and the indexes are the same.

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

However, the SQL you posted above still has the idx_cargo_no_opt_type index on the t_dba_t2_p202303 table.

This index should have already been dropped on the source table t_dba_t2 and changed to idx_cargo_type_time, right?

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

However, your error indicates that the idx_cargo_type_time index on the table and the partitioned table is inconsistent. Please check again.

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

I just added it, it should be a mistake, what I meant was that I copied the original table at that time and then did the partition exchange.

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

Previously, I did not strictly follow the production steps to reproduce the issue, which resulted in it not appearing. The main problem is that the field values of the index view are different, which theoretically should not be an issue and is likely a product bug. The solution is to ensure that the INDEX_ID field values of information_schema.tidb_indexes are also the same.

For specific simulation and handling methods, see the description below. The issue can be reproduced and resolved in the test environment, and it can also be used in actual production.

1. Re-simulate

**Create a new table with a partition and placement:**
CREATE TABLE `t_dba_t2` (
`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`),
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 table' 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));

**Insert historical data with IDs:**
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','System');

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

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


**Insert data with random auto-increment IDs:**
Insert data for April, including both auto-increment and random values,
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','System');

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

**Adjust the original table index:** Add one and delete one.
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;

Insert April data again:
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','System');

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

**Create a regular table t_dba_t2_p202303 based on the original table t_dba_t2**, 
Remove the partition and AUTO_RANDOM_BASE starting value attributes:

CREATE TABLE t_dba_t2_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 '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 table' PLACEMENT POLICY=storeonssd;

Partition exchange error:
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. View the specific attributes of the table index:
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;

You can see that the newly added index only has different INDEX_ID field values for idx_cargo_type_time.

3. Solution

**Recreate the regular table**
drop table t_dba_t2_p202303;

CREATE TABLE t_dba_t2_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 '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`),    -- Add this temporarily
KEY idx_cargo_type_time (cargo_no,opt_type,opt_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='xx table' PLACEMENT POLICY=storeonssd;

**Then delete the temporarily added index:** 
Ensure the index order field values are the same:
alter table t_dba_t2_p202303 drop index idx_cargo_no_opt_type;

Check the index order again, and they are identical:
+--------------+------------------+------------------------------+--------------+-----------------+------------+------------+-----------+----------+
| 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 |
+--------------+------------------+------------------------------+--------------+-----------------+------------+------------+-----------+----------+

**Then exchange the partition again:**
(PARTITION `P202303` VALUES LESS THAN ('2023-04-01'), no partition definition

ALTER TABLE t_dba_t2 EXCHANGE PARTITION P202303 WITH TABLE t_dba_t2_p202303;
ERROR 1736 (HY000): Tables have different definitions
This is a placement bug,
Adjust the partition placement,
alter table t_dba_t2 PARTITION P202303 placement policy storeonssd;

**Finally, exchange the partition again,**
It works:
ALTER TABLE t_dba_t2 EXCHANGE PARTITION P202303 WITH TABLE t_dba_t2_p202303;
| username: system | Original post link

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