OceanBase 数据库中删除二级分区表失败报错 ERROR 4225 (HY000):Partition entry not exists 的原因以及处理方法

问题现象

执行删除二级分区语句，报错分区不存在，如下示例。

ALTER TABLE table_name DROP SUBPARTITION pxxxx;
ERROR 4225 (HY000): Partition entry not exists

问题原因

使用的是模板化二级分区创建的分区表，这种情况下自定义的子分区名称和实际系统表记录不一致。

适用版本

OceanBase 数据库 V4.x 版本。

解决方法

在 sys 租户中查询 oceanbase.DBA_OB_TABLE_LOCATIONS 对应的 SUBPARTITION_NAME 字段为实际二级分区表名称。

示例如下。

1. 创建二级分区表。

   obclient [mysql]> create table t2_m_lr (
       col1 int(11) default null,
       col2 int(11) default null)
       partition by list(col1) subpartition by range(col2) subpartition template (
       subpartition mp0 values less than (100),
       subpartition mp1 values less than (200),
       subpartition mp2 values less than (300))
       (partition p0 values in (1,3),
       partition p1 values in (4,6),
       partition p2 values in (7,9));
       Query OK, 0 rows affected (0.345 sec)
   

2. 通过 DBA_OB_TABLE_LOCATIONS 视图查看表 t2_m_lr 所在的位置。

   obclient [mysql]> select * from oceanbase.DBA_OB_TABLE_LOCATIONS where table_name ='t2_m_lr';
   

   输出结果如下：

   +---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
   | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE  | SVR_IP         | SVR_PORT | ROLE   | REPLICA_TYPE | DUPLICATE_SCOPE | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING |
   +---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p0             | p0smp0            | NULL       |          NULL |    200001 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500006 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p0             | p0smp1            | NULL       |          NULL |    200002 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500007 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p0             | p0smp2            | NULL       |          NULL |    200003 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500008 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p1             | p1smp0            | NULL       |          NULL |    200004 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500009 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p1             | p1smp1            | NULL       |          NULL |    200005 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500010 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p1             | p1smp2            | NULL       |          NULL |    200006 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500011 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p2             | p2smp0            | NULL       |          NULL |    200007 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500012 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p2             | p2smp1            | NULL       |          NULL |    200008 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500013 | NULL            |          NULL | NULL     |
   | mysql         | t2_m_lr    |   500002 | USER TABLE | p2             | p2smp2            | NULL       |          NULL |    200009 |  1001 | zone1 | xxx.xx.xxx.xxx |     2882 | LEADER | FULL         | NONE            |    500014 | NULL            |          NULL | NULL     |
   +---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
   9 rows in set (0.123 sec)
   

3. 删除指定二级分区。

   obclient [mysql]> alter table t2_m_lr drop subpartition p2smp2;
   Query OK, 0 rows affected (0.164 sec)