In development server, due to the fact that process design will always get updated and developed, there will be more and more XPDLs cached in the memory. In the long run, this may cause "java.lang.OutOfMemoryError" exception during runtime or server startup. To solve this issue, we will need to clean up the unused XPDLs with following steps. The following example queries are written in MySQL syntaxMySQL and MSSQL syntax.
| Note |
|---|
|
1. Please shutdown your application server. 2. Please do a full database backup before proceeding to the following steps. |
...
| Code Block |
|---|
| language | sql |
|---|
| title | MySQL, MSSQL |
|---|
|
select count(*) from SHKXPDLS |
...
| Code Block |
|---|
| language | sql |
|---|
| title | MySQL, MSSQL |
|---|
|
select count(*) from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
select concat(packageId,packageVersion) as id from (
select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
from SHKActivities act
join SHKProcesses pro on act.Process = pro.oid
left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
group by def.PackageId, def.ProcessDefinitionVersion
union
select packageId, packageVersion from app_package
) as used_processes group by packageId, packageVersion
); |
...
| Code Block |
|---|
| language | sql |
|---|
| title | MySQL, MSSQL |
|---|
|
select * from SHKXPDLS x
where concat(x.XPDLId, x.XPDLVersion) not in (
select concat(packageId,packageVersion) as id from (
select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
from SHKActivities act
join SHKProcesses pro on act.Process = pro.oid
left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
group by def.PackageId, def.ProcessDefinitionVersion
union
select packageId, packageVersion from app_package
) as used_processes group by packageId, packageVersion
); |
...
| Code Block |
|---|
|
SET foreign_key_checks = 0;
delete sp, spr, spd, sac, sad, sa
from SHKProcesses sp
join SHKProcessRequesters spr on spr.Id = sp.ID
left join SHKProcessData spd on spd.Process = sp.oid
left join SHKActivities sac on sac.ProcessId = sp.ID
left join SHKActivityData sad on sad.Activity = sac.oid
left join SHKAssignmentsTable sa on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
SET foreign_key_checks = 1; |
| Code Block |
|---|
|
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
delete sp, spr, spd, sac, sad, sa
from SHKProcesses sp
join SHKProcessRequesters spr on spr.Id = sp.ID
left join SHKProcessData spd on spd.Process = sp.oid
left join SHKActivities sac on sac.ProcessId = sp.ID
left join SHKActivityData sad on sad.Activity = sac.oid
left join SHKAssignmentsTable sa on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" |
4. Delete the unused XDPLs
...
| Code Block |
|---|
|
SET FOREIGN_KEY_CHECKS=0;
delete x, xd from SHKXPDLS x join SHKXPDLData xd on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
select concat(packageId,packageVersion) as id from (
select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
from SHKActivities act
join SHKProcesses pro on act.Process = pro.oid
left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
group by def.PackageId, def.ProcessDefinitionVersion
union
select packageId, packageVersion from app_package
) as used_processes group by packageId, packageVersion
);
SET FOREIGN_KEY_CHECKS=1; |
| Code Block |
|---|
|
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
delete x, xd from SHKXPDLS x join SHKXPDLData xd on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
select concat(packageId,packageVersion) as id from (
select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion
from SHKActivities act
join SHKProcesses pro on act.Process = pro.oid
left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid
group by def.PackageId, def.ProcessDefinitionVersion
union
select packageId, packageVersion from app_package
) as used_processes group by packageId, packageVersion
);
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
|