一、问题描述

4.4.2版本mysql租户下普通分区表(非动态维护分区)转换为动态分区表的时候，个别老旧分区不会自动删除维护？

二、问题复现

针对该问题构建模拟测试表，复现和分析问题，具体环境和步骤如下：

2.1 复现环境信息

MySQL [test_cnt]> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.7.25-OceanBase-v4.4.2.2 |
+---------------------------+

2.2 基础准备构建测试表

构建test_dyn_part为测试对象，该表为普通分区表

-- 0. 删除测试表test_dyn_part
drop table if exists test_dyn_part;

-- 1. 创建测试test_dyn_part，RANGE COLUMNS 分区表

CREATE TABLE test_dyn_part (
  id INT NOT NULL,
  create_time DATETIME NOT NULL,
  name VARCHAR(100),
  PRIMARY KEY(id, create_time)
) PARTITION BY RANGE COLUMNS(create_time) (
  PARTITION p202401 VALUES LESS THAN ('2024-04-01'),
  PARTITION p202402 VALUES LESS THAN ('2024-07-01'),
  PARTITION p202403 VALUES LESS THAN ('2024-10-01'),
  PARTITION p202404 VALUES LESS THAN ('2025-01-01')
);

-- 2.插入模拟数据
insert into test_dyn_part(id,create_time,name)values(1,'2024-03-28','name_240328');
insert into test_dyn_part(id,create_time,name)values(2,'2024-04-01','name_240401');
insert into test_dyn_part(id,create_time,name)values(3,'2024-05-28','name_240528');
insert into test_dyn_part(id,create_time,name)values(4,'2024-07-02','name_240702');
insert into test_dyn_part(id,create_time,name)values(5,'2024-10-12','name_241012');
insert into test_dyn_part(id,create_time,name)values(6,'2024-11-19','name_241119');

-- 3.查看当前表的数据
select * from test_dyn_part;

select 'p202401' as pname,p.* from test_dyn_part PARTITION(p202401) p union all
select 'p202402' as pname,p.* from test_dyn_part PARTITION(p202402) p union all
select 'p202403' as pname,p.* from test_dyn_part PARTITION(p202403) p union all
select 'p202404' as pname,p.* from test_dyn_part PARTITION(p202404) p;

查看表和分区的TABLE_ID信息

SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and 
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;

执行结果如下

MySQL [test_cnt]> SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
    -> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
    -> FROM oceanbase.DBA_TAB_PARTITIONS tp
    -> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
    -> on tp.PARTITION_NAME=tl.PARTITION_NAME and 
    -> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
    -> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
    -> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE            | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
|   522910 |    210205 | USER TABLE | p202401        |  1002 | '2024-04-01 00:00:00' |                  1 |
|   522910 |    210206 | USER TABLE | p202402        |  1001 | '2024-07-01 00:00:00' |                  2 |
|   522910 |    210207 | USER TABLE | p202403        |  1002 | '2024-10-01 00:00:00' |                  3 |
|   522910 |    210208 | USER TABLE | p202404        |  1001 | '2025-01-01 00:00:00' |                  4 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
4 rows in set (0.05 sec)

2.4 非动态分区表转换为动态分区表

将test_dyn_part转为动态分区表

ALTER TABLE test_dyn_part DYNAMIC_PARTITION_POLICY = (
  ENABLE = TRUE,
  TIME_UNIT = 'MONTH',
  PRECREATE_TIME = '3MONTH',
  EXPIRE_TIME = '1YEAR'
);

查看test_dyn_part动态分区表的策略

SHOW CREATE TABLE test_dyn_part;

执行结果如下：

MySQL [test_cnt]> SHOW CREATE TABLE test_dyn_part;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                      |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_dyn_part | CREATE TABLE `test_dyn_part` (
  `id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`, `create_time`)
) ORGANIZATION HEAP DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'MONTH', PRECREATE_TIME = '3MONTH', EXPIRE_TIME = '1YEAR', TIME_ZONE = 'DEFAULT', BIGINT_PRECISION = 'NONE')
 partition by range columns(`create_time`)
(partition `p202401` values less than ('2024-04-01 00:00:00'),
partition `p202402` values less than ('2024-07-01 00:00:00'),
partition `p202403` values less than ('2024-10-01 00:00:00'),
partition `p202404` values less than ('2025-01-01 00:00:00')) |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

查看表和分区的TABLE_ID信息

SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and 
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;

执行结果如下: TABLET_ID 没有发生变化，分区还是一开始的4个，相关动态维护的策略还没生效，因此此时该动态分区管理定时调度任务还没有触发，设置的过期策略是year，需要等到每天到00:00才触发调度进行分区动态维护。

MySQL [test_cnt]> SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
    -> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
    -> FROM oceanbase.DBA_TAB_PARTITIONS tp
    -> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
    -> on tp.PARTITION_NAME=tl.PARTITION_NAME and 
    -> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
    -> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
    -> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE            | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
|   522910 |    210205 | USER TABLE | p202401        |  1002 | '2024-04-01 00:00:00' |                  1 |
|   522910 |    210206 | USER TABLE | p202402        |  1001 | '2024-07-01 00:00:00' |                  2 |
|   522910 |    210207 | USER TABLE | p202403        |  1002 | '2024-10-01 00:00:00' |                  3 |
|   522910 |    210208 | USER TABLE | p202404        |  1001 | '2025-01-01 00:00:00' |                  4 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
4 rows in set (0.02 sec)

2.5. 第1次手动触发动态分区维护-出现不符合预期情况

手动触发动态分区维护管理

-- 手动触发动态分区管理（不等待后台任务）
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');

查看test_dyn_part表转为动态分区表后的分区情况：此时动态分区创建出来，但是P202508之前的分区存在。

SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and 
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;

执行结果如下:当前时间为26-08-17，设置的策略是1年，但P202508之前的分区还存在不符合预期。

MySQL [test_cnt]> SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
    -> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
    -> FROM oceanbase.DBA_TAB_PARTITIONS tp
    -> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
    -> on tp.PARTITION_NAME=tl.PARTITION_NAME and 
    -> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
    -> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
    -> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE            | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
|   522910 |    210208 | USER TABLE | p202404        |  1001 | '2025-01-01 00:00:00' |                  1 |
|   522910 |    210209 | USER TABLE | P202501        |  1001 | '2025-02-01 00:00:00' |                  2 |
|   522910 |    210210 | USER TABLE | P202502        |  1002 | '2025-03-01 00:00:00' |                  3 |
|   522910 |    210211 | USER TABLE | P202503        |  1001 | '2025-04-01 00:00:00' |                  4 |
|   522910 |    210212 | USER TABLE | P202504        |  1002 | '2025-05-01 00:00:00' |                  5 |
|   522910 |    210213 | USER TABLE | P202505        |  1001 | '2025-06-01 00:00:00' |                  6 |
|   522910 |    210214 | USER TABLE | P202506        |  1002 | '2025-07-01 00:00:00' |                  7 |
|   522910 |    210215 | USER TABLE | P202507        |  1001 | '2025-08-01 00:00:00' |                  8 |
|   522910 |    210216 | USER TABLE | P202508        |  1002 | '2025-09-01 00:00:00' |                  9 |
|   522910 |    210217 | USER TABLE | P202509        |  1001 | '2025-10-01 00:00:00' |                 10 |
|   522910 |    210218 | USER TABLE | P202510        |  1002 | '2025-11-01 00:00:00' |                 11 |
|   522910 |    210219 | USER TABLE | P202511        |  1001 | '2025-12-01 00:00:00' |                 12 |
|   522910 |    210220 | USER TABLE | P202512        |  1002 | '2026-01-01 00:00:00' |                 13 |
|   522910 |    210221 | USER TABLE | P202601        |  1001 | '2026-02-01 00:00:00' |                 14 |
|   522910 |    210222 | USER TABLE | P202602        |  1002 | '2026-03-01 00:00:00' |                 15 |
|   522910 |    210223 | USER TABLE | P202603        |  1001 | '2026-04-01 00:00:00' |                 16 |
|   522910 |    210224 | USER TABLE | P202604        |  1002 | '2026-05-01 00:00:00' |                 17 |
|   522910 |    210225 | USER TABLE | P202605        |  1001 | '2026-06-01 00:00:00' |                 18 |
|   522910 |    210226 | USER TABLE | P202606        |  1002 | '2026-07-01 00:00:00' |                 19 |
|   522910 |    210227 | USER TABLE | P202607        |  1001 | '2026-08-01 00:00:00' |                 20 |
|   522910 |    210228 | USER TABLE | P202608        |  1002 | '2026-09-01 00:00:00' |                 21 |
|   522910 |    210229 | USER TABLE | P202609        |  1001 | '2026-10-01 00:00:00' |                 22 |
|   522910 |    210230 | USER TABLE | P202610        |  1002 | '2026-11-01 00:00:00' |                 23 |
|   522910 |    210231 | USER TABLE | P202611        |  1001 | '2026-12-01 00:00:00' |                 24 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
24 rows in set (0.08 sec)

2.6. 第2次手动触发动态分区维护-符合预期

再次手动触发动态分区管理

CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');

查看test_dyn_part表转为动态分区表后的分区情况

SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and 
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;

执行结果: 此时保留的分区个数符合预期，为当前时间的往前保留1年（最小的分区为P202508）

MySQL [test_cnt]> SELECT  tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
    -> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
    -> FROM oceanbase.DBA_TAB_PARTITIONS tp
    -> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
    -> on tp.PARTITION_NAME=tl.PARTITION_NAME and 
    -> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
    -> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
    -> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE            | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
|   522910 |    210216 | USER TABLE | P202508        |  1002 | '2025-09-01 00:00:00' |                  1 |
|   522910 |    210217 | USER TABLE | P202509        |  1001 | '2025-10-01 00:00:00' |                  2 |
|   522910 |    210218 | USER TABLE | P202510        |  1002 | '2025-11-01 00:00:00' |                  3 |
|   522910 |    210219 | USER TABLE | P202511        |  1001 | '2025-12-01 00:00:00' |                  4 |
|   522910 |    210220 | USER TABLE | P202512        |  1002 | '2026-01-01 00:00:00' |                  5 |
|   522910 |    210221 | USER TABLE | P202601        |  1001 | '2026-02-01 00:00:00' |                  6 |
|   522910 |    210222 | USER TABLE | P202602        |  1002 | '2026-03-01 00:00:00' |                  7 |
|   522910 |    210223 | USER TABLE | P202603        |  1001 | '2026-04-01 00:00:00' |                  8 |
|   522910 |    210224 | USER TABLE | P202604        |  1002 | '2026-05-01 00:00:00' |                  9 |
|   522910 |    210225 | USER TABLE | P202605        |  1001 | '2026-06-01 00:00:00' |                 10 |
|   522910 |    210226 | USER TABLE | P202606        |  1002 | '2026-07-01 00:00:00' |                 11 |
|   522910 |    210227 | USER TABLE | P202607        |  1001 | '2026-08-01 00:00:00' |                 12 |
|   522910 |    210228 | USER TABLE | P202608        |  1002 | '2026-09-01 00:00:00' |                 13 |
|   522910 |    210229 | USER TABLE | P202609        |  1001 | '2026-10-01 00:00:00' |                 14 |
|   522910 |    210230 | USER TABLE | P202610        |  1002 | '2026-11-01 00:00:00' |                 15 |
|   522910 |    210231 | USER TABLE | P202611        |  1001 | '2026-12-01 00:00:00' |                 16 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
16 rows in set (0.09 sec)

三、原因分析

下面语句可以查到test_dyn_part表转为动态分区表后的策略是保留1年的分区(即1年前的分区自动动态删除)；

SELECT DATABASE_NAME, TABLE_NAME, ENABLE, TIME_UNIT,
       PRECREATE_TIME, EXPIRE_TIME
FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
WHERE TABLE_NAME = 'test_dyn_part';

执行结果

MySQL [test_cnt]> SELECT DATABASE_NAME, TABLE_NAME, ENABLE, TIME_UNIT,
    ->        PRECREATE_TIME, EXPIRE_TIME
    -> FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
    -> WHERE TABLE_NAME = 'test_dyn_part';
+---------------+---------------+--------+-----------+----------------+-------------+
| DATABASE_NAME | TABLE_NAME    | ENABLE | TIME_UNIT | PRECREATE_TIME | EXPIRE_TIME |
+---------------+---------------+--------+-----------+----------------+-------------+
| test_cnt      | test_dyn_part | TRUE   | MONTH     | 3MONTH         | 1YEAR       |
+---------------+---------------+--------+-----------+----------------+-------------+
1 row in set (0.02 sec)

第1次手动触发 CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');预创建3个月表，当前时间是26-08-17，也就是逻辑上25-08-01之前的分区应该删除。但P202508之前甚至p202404的分区也存在；ob底层的动态分区维护的逻辑顺序：

先执行是否需要添加分区

起点 = 最后一个分区 p202404 的 high_bound(2025-01-01) 向下取整到 MONTH则为2025-01-01
终点 = max(表的PRECREATE_TIME=3MONTH, 指定值=3MONTH)，当前时间为2026-08 + 3MONTH ≈ 2026-11-17
创建分区：自动补分区创建 P202501, P202502, ..., P202610, P202611（共23个）

在添加分区之后再删除过期分区

每次在删除的时候最后一个分区会跳过，第一次出现过期未删除主要的时候获取table_schema是旧快照信息导致；

init()                          → table_schema_ 当前的schema快照
  ↓
execute()
  ├─ add_dynamic_partition_()   → write_ddl_("ALTER TABLE ... ADD PARTITION ...")  
  └─ drop_dynamic_partition_()  → build_expired_partition_name_list_()
                                   → for i get_partition_num() - 1  
                                     → table_schema_只有在init的时候被初始化，相当于此时读取的是旧快照，因此只遍历 p202401, p202402, p202403
                                     → p202404 是 i=3，被跳过,分区表至少得存在1个分区

第2次手动触发 CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');

先执行是否需要添加分区

此时p202404已经不是最后一个分区，最后一个分区为P202611，此时不需预先创建3个月的表，前面第一次已经创建；

在添加分区之后再删除过期分区

此时的表有p202404到P202611，发现p202404到P202507均符合过期分区，执行删除，最后剩下P202508到P202611的分区，此时符合预期；

四、问题结论

mysql租户下普通分区表(非动态维护分区)转换为动态分区表的时候，个别老旧分区不会自动删除维护(不完全准确)，该现象主要原因是：第1次触发动态分区管理的时候，在单次判断删除过期分区的时候读取的是旧快照schema导致；但在下一个调度触发周是会自动删除之前的过期分区。
前面的“不符合预期”，功能层面实际不会有大影响，效果上表现为"首次多保留一轮"；从测试从普通分区表(非动态维护分区)转换为动态分区表的时候是onlien ddl ，非动态分区转换为动态分区还是一个相对优化的功能。
需要注意的是：在该版本中动态分区的TIME_UNIT是不支持修改的，如下ddl执行的时候会报错，因此在设计动态分区表的策略的时候，对于TIME_UNIT建议不要随意设置，以免后期修改需要重建；

ALTER TABLE test_dyn_part DYNAMIC_PARTITION_POLICY = (
  TIME_UNIT = 'DAY',
  PRECREATE_TIME = '7DAY',
  EXPIRE_TIME = '90DAY'
);
-- 会提示错误：Not supported feature or function

OB混合云支持服务

OB公有云支持服务

综合服务

解决方案

OceanBase 非动态分区表转动态分区，过期的分区不会自动删除？

一、 问题描述

二、 问题复现

2.1 复现环境信息

2.2 基础准备构建测试表

2.4 非动态分区表转换为动态分区表

2.5. 第1次 手动触发动态分区维护-出现不符合预期情况

2.6. 第2次 手动触发动态分区维护-符合预期

三、 原因分析

四、 问题结论

精选推荐

联系我们

OceanBase技术服务

OceanBase培训服务

OceanBase技术社区