Physical deletion of a process

Support
#1

Hi,

I need to physical delete a process starting from a processInstanceId.

Could you provide a working sql script?

I already tried:

DELETE FROM ACT_RU_EXECUTION WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_HI_PROCINST WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_RU_TASK WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_HI_TASKINST WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_HI_DETAIL WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_RU_VARIABLE WHERE PROC_INST_ID_ = 'your_process_instance_id';
DELETE FROM ACT_HI_ATTACHMENT WHERE PROC_INST_ID_ = 'your_process_instance_id';

but what I get are contraints errors due to relations with other tables.

Thank you!
Vittorio

#3

Hello,

first, backup your database. :slight_smile:

RU in the names of the tables comes from “runtime” means the process instance is in active state, HI is for “history”. Only running instances are in RU tables because of the performance, but they also mirror in HI. When the process instance is finished, everything is written in HI only.

Your SQL is not bad, but its a matter of precedence, some tables needs to be deleted before others, and there is a thing with ACT_RU_IDENTITYLINK entries where you also need to delete based on ACT_RU_TASK.ID_ not just general PROC_INST_ID_ , like this:

DELETE FROM ACT_HI_IDENTITYLINK WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_HI_DETAIL WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_HI_ATTACHMENT WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_HI_TASKINST WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_HI_ACTINST WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_HI_PROCINST WHERE ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';

DELETE FROM ACT_RU_IDENTITYLINK WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_RU_VARIABLE WHERE EXECUTION_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_RU_EVENT_SUBSCR WHERE EXECUTION_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';

DELETE FROM ACT_RU_IDENTITYLINK
WHERE TASK_ID_ IN (
    SELECT ID_
    FROM ACT_RU_TASK
    WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec'
);
DELETE FROM ACT_RU_TASK WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_RU_EXECUTION WHERE PROC_INST_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';

Test this thoroughly.

These SQL commands are good when performing large bulk deletes, such as cleaning the database or externally deleting records via SQL, otherwise Flowable REST API or Java API can be used.

Look for

runtimeService.deleteProcessInstance
runtimeService.bulkDeleteProcessInstance (list of instances to delete)

Kind regards,
Mladen

#4

Breakdown of Table Naming Components:

  1. ACT_: All tables start with this prefix.
  2. RU / HI / GE / ID / EV: This segment indicates the scope or function of the table.
  • RU: Runtime data for active processes.
  • HI: Historical data for completed processes.
  • GE: General, shared data (e.g., resources).
  • ID: Identity management (e.g., users and groups).
  • EV: Event-related data.
  1. TASK / EXECUTION / VARIABLE / IDENTITYLINK / etc.: This part defines the content of the table, such as tasks, executions, variables, identity links, or events.
#5 
DELETE FROM ACT_RU_VARIABLE WHERE EXECUTION_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';
DELETE FROM ACT_RU_EVENT_SUBSCR WHERE EXECUTION_ID_ = 'dea46cf9-742e-11ef-9377-00ff19afe8ec';

field EXECUTION_ID_ holds the same value as PROC_INST_ID_ so in this case its equivalent.