Skip to content

Instantly share code, notes, and snippets.

@howkymike
Last active October 1, 2021 13:48
Show Gist options
  • Save howkymike/e6678117083e6021a112ba4b9897da73 to your computer and use it in GitHub Desktop.
Save howkymike/e6678117083e6021a112ba4b9897da73 to your computer and use it in GitHub Desktop.
Alfresco Activiti - clean non-existent historical process data (fix "My completed tasks" error)
-- =============================================
-- Author: howkymike
-- Description: deletes nonexistent historical process data. It resolves the "My completed tasks" error after some
-- process definitions has been deleted via Alfresco Workflow console.
-- Before executing this script stop Alfresco instace
-- =============================================
-- Create temp table which stores faulty IDs
SELECT PROC_INST_ID_ into tmp2 FROM ACT_HI_PROCINST
left join act_re_procdef on act_re_procdef.id_ = act_hi_procinst.proc_def_id_
where act_re_procdef.id_ is null;
-- Delete all records connected to those processes
DELETE FROM ACT_HI_ACTINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_ATTACHMENT WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_COMMENT WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_DETAIL WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_IDENTITYLINK WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_VARINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_TASKINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
DELETE FROM ACT_HI_PROCINST WHERE PROC_INST_ID_ in (select PROC_INST_ID_ from tmp2);
-- Drop tmp2 table
drop table tmp2;
vacuum full;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment