3.1.X~3.3.X Workflow执行数据清理

2026-07-06 其他常见问题

内容纲要

概要描述

1、任务流间依赖关系：仅支持无周期任务流依赖有周期任务流，并且在有任务流间依赖关系的情况下，仅支持一天一次的调度，不需要特殊处理（如果进行数据清理时，一般清理最小单位为已结束的一天）

2、任务流自身依赖：任务流配置依赖上次，任务流调度执行时，需要确认上一次调度执行的执行结果。所以，进行数据清理时，需要注意保留至少一条历史执行记录（如果有多天执行一次的任务流的情况需要注意，要保留最大周期跨度的时间范围的数据）

相关数据表：WORKFLOW_COMPLEMENT_COMMAND、WORKFLOW_COMPLEMENT_RECORD、WORKFLOW_COMPLEMENT_WORKSPACE、WORKFLOW_DEBUG_COMMAND、WORKFLOW_DEBUG_RECORD、WORKFLOW_FLOW_EXECUTION、WORKFLOW_FLOW_EXECUTION_DEPENDENCY、WORKFLOW_TASK_EXECUTION

其中，WORKFLOW_FLOW_EXECUTION、WORKFLOW_TASK_EXECUTION、WORKFLOW_FLOW_EXECUTION_DEPENDENCY是日常时间调度会不断写入数据的表，其他表多记录用户操作行为。

详细说明

1、不停机清理

以调度时间为依据进行待清理数据筛选及清理，以被清理的时间范围内的执行记录都已执行结束为前提。需要注意，1. 如果较早的调度时间的执行记录在近期有重跑、错误恢复的运维操作，会被一同清理，为避免这个问题，可以使用startTime（即任务流执行开始时间）字段替代调度时间字段进行筛选；2. 如果近期有针对清理时间范围内的日期进行补数的操作，相关补数执行记录会被一起清理，为避免这个问题，需要在查询待清理执行记录时，将executionMode为4的记录过滤出去。

以下方式每次清理10000条任务流执行记录及相关的任务执行记录，超出部分需要再次执行。假设需要保留从2024.1.1开始的执行记录。

# 第一部分 注意该部分sql只需要执行一次，且执行时务必保证和第二部分的schedule_time范围保持一致
delete from WORKFLOW_FLOW_EXECUTION_EXPIRATION where schedule_time < '2024-01-01 00:00:00';

# 第二部分开始 第二部分需要循环执行，每次循环执行的范围截止到第二部分结束
DROP temporary table if exists wait_to_delete_flow_execution_ids;
create temporary table wait_to_delete_flow_execution_ids as
select id
from WORKFLOW_FLOW_EXECUTION
# 此处定义删除的条件
where scheduleTime < '2024-01-01 00:00:00'
order by id
limit 10000;
# delete execution dependence
DELETE
FROM WORKFLOW_EXECUTION_DEPENDENCY
WHERE (post_execution_id in (select id from wait_to_delete_flow_execution_ids) AND post_type = 'FLOW')
   OR (post_execution_id IN (SELECT id
                             FROM WORKFLOW_TASK_EXECUTION
                             WHERE parentFlowExecutionId IN (select id
                                                             from wait_to_delete_flow_execution_ids))
    AND post_type = 'TASK'
    );
DELETE
FROM WORKFLOW_EXECUTION_DEPENDENCY
WHERE (pre_execution_id IN (select id from wait_to_delete_flow_execution_ids) AND pre_type = 'FLOW')
   OR (pre_execution_id IN (SELECT id
                            FROM WORKFLOW_TASK_EXECUTION
                            WHERE parentFlowExecutionId IN (select id
                                                            from wait_to_delete_flow_execution_ids))
    AND pre_type = 'TASK'
    );
# task execution
delete
from WORKFLOW_TASK_EXECUTION
where parentFlowExecutionId in (select id from wait_to_delete_flow_execution_ids);
#  flow execution
delete
from WORKFLOW_FLOW_EXECUTION
where id in (select id from wait_to_delete_flow_execution_ids);

DROP temporary table if exists wait_to_delete_flow_execution_ids;
# 第二部分结束

根据如上操作清理执行记录数据后，针对用户操作记录的清理方式如下。

（1）以createTime为依据，清理补数操作记录（不包含对flow execution的清理），清理WORKFLOW_COMPLEMENT_COMMAND、WORKFLOW_COMPLEMENT_RECORD以及WORKFLOW_COMPLEMENT_WORKSPACE表。

delete from WORKFLOW_COMPLEMENT_RECORD where commandId in (select commandId from WORKFLOW_COMPLEMENT_COMMAND where createTime < '2024-01-01 00:00:00');
delete from WORKFLOW_COMPLEMENT_WORKSPACE where commandId in (select commandId from WORKFLOW_COMPLEMENT_COMMAND where createTime < '2024-01-01 00:00:00');
delete from WORKFLOW_COMPLEMENT_COMMAND where createTime < '2024-01-01 00:00:00';

（2）以createTime、scheduleTime为依据，清理调试操作记录（不包含对flow execution的清理），清理WORKFLOW_DEBUG_COMMAND以及WORKFLOW_DEBUG_RECORD表。

delete from WORKFLOW_DEBUG_COMMAND where createTime < '2024-01-01 00:00:00';
delete from WORKFLOW_DEBUG_RECORD where scheduleTime < '2024-01-01 00:00:00';

（3）以createTime为依据，清理用户操作记录，清理WORKFLOW_MANUAL_COMMAND、WORKFLOW_OPERATION_COMMAND表。

delete from WORKFLOW_MANUAL_COMMAND where createTime < '2024-01-01 00:00:00';
delete from WORKFLOW_OPERATION_COMMAND where createTime < '2024-01-01 00:00:00';

（4）以event_time为依据，清理任务流事件触发记录，清理WORKFLOW_FLOW_EVENT_RECORD表。

delete from WORKFLOW_FLOW_EVENT_RECORD where event_ime < '2024-01-01 00:00:00';

2、停机清理
（1）停止workflow服务

（2）将需要保留的数据写入新的记录表

# rename
rename table WORKFLOW_FLOW_EXECUTION to WORKFLOW_FLOW_EXECUTION_BAK;
rename table WORKFLOW_TASK_EXECUTION to WORKFLOW_TASK_EXECUTION_BAK;
rename table WORKFLOW_EXECUTION_DEPENDENCY to WORKFLOW_EXECUTION_DEPENDENCY_BAK;
rename table WORKFLOW_FLOW_EXECUTION_EXPIRATION to WORKFLOW_FLOW_EXECUTION_EXPIRATION_BAK;
# new table
create table WORKFLOW_FLOW_EXECUTION like WORKFLOW_FLOW_EXECUTION_BAK;
create table WORKFLOW_TASK_EXECUTION like WORKFLOW_TASK_EXECUTION_BAK;
create table WORKFLOW_EXECUTION_DEPENDENCY like WORKFLOW_EXECUTION_DEPENDENCY_BAK;
create table WORKFLOW_FLOW_EXECUTION_EXPIRATION like WORKFLOW_FLOW_EXECUTION_EXPIRATION_BAK;
# insert valid data
insert into WORKFLOW_FLOW_EXECUTION select * from WORKFLOW_FLOW_EXECUTION_BAK where scheduleTime >= '2024-01-01 00:00:00';
insert into WORKFLOW_FLOW_EXECUTION_EXPIRATION select * from WORKFLOW_FLOW_EXECUTION_EXPIRATION_BAK where schedule_time >= '2024-01-01 00:00:00';
insert into WORKFLOW_TASK_EXECUTION select * from WORKFLOW_TASK_EXECUTION_BAK where parentFlowExecutionId in (select id from WORKFLOW_FLOW_EXECUTION);
insert into WORKFLOW_EXECUTION_DEPENDENCY select * from WORKFLOW_EXECUTION_DEPENDENCY_BAK where post_execution_id in (select id from WORKFLOW_FLOW_EXECUTION) and post_type = 'FLOW';

（3）删除旧表，根据实际需求操作，如果需要保留历史数据，且数据盘空间充裕，不需要另外删除，之后进行升级、迁移等操作时，注意排除备份表。

drop table WORKFLOW_FLOW_EXECUTION_BAK;
drop table WORKFLOW_TASK_EXECUTION_BAK;
drop table WORKFLOW_EXECUTION_DEPENDENCY_BAK;
drop table WORKFLOW_FLOW_EXECUTION_EXPIRATION_BAK;

（4）启动workflow服务

3.1.X~3.3.X Workflow执行数据清理

概要描述

详细说明

近期文章

分类

归档