Process Instance Query returning completed or cancelled workflows (MS SQL)

[cchen]

I am trying to use a ProcessInstanceQuery to return all active workflows initiated by bpm_initiator. However, a number of inactive workflows are getting returned. When these inactive workflows are returned, exceptions are thrown, since bpm_package has been deleted. When querying ACT_RU_VARIABLE for the bpm_package values that are causing the exceptions, there are no process instances returned, implying that there are no active workflows for those bpm_package values.

Activiti Engine 5.16.4
MS SQL 2012 SP1

We are using the following Activiti API:

ProcessEngine pe = ProcessEngines.getDefaultProcessEngine(); 
pe.getIdentityService().setAuthenticatedUserId( userLoginName ); 
List<ProcessInstance> processes = pe.getRuntimeService().createProcessInstanceQuery().variableValueEquals("bpm_initiator", userLoginName).list(); 
‍‍‍‍‍

Logging has been turned on for: log4j.logger.org.activiti.engine.impl.persistence.entity.ExecutionEntity=DEBUG to obtain the SQL Query. Executing the logged query in MS SQL Server Management Studio does not produce workflows that correspond to any of the completed/cancelled workflows with deleted node refs.

Due to the additional logging, this is the query we think gets executed, but does not seem to return the cancelled workflows that are causing our errors:

SELECT SUB.* FROM ( select distinct RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId, P.NAME_ as ProcessDefinitionName, P.VERSION_ as ProcessDefinitionVersion, P.DEPLOYMENT_ID_ as DeploymentId from ACT_RU_EXECUTION RES inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_ inner join ACT_RU_VARIABLE A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ WHERE RES.PARENT_ID_ is null and A0.EXECUTION_ID_ = A0.PROC_INST_ID_ and A0.NAME_= ? and A0.TYPE_ = ? and A0.TEXT_ = ? )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ? 
‍‍‍

Please advise. Thank you.

[jbarrez]

what does 'inactive' mean for you? It's not a concept from Activiti. Do you mean suspended?

> getRuntimeService().createProcessInstanceQuery().variableValueEquals("bpm_initiator", userLoginName)

This returns all instances wth that variable, irregardless of the state, you'd need to add more constraints to the query to filter more narrowly.

[cchen]

Yes, I do mean suspended.

To counter your point about all instances with that variable getting returned… We are not seeing all instances getting returned. As in, not all suspended (completed or cancelled) workflows are returned but just a few of them are getting returned along with the active workflows. Is this possibly an issue with MS SQL?

Additionally, to constrain my query, is adding <java>.active()</java> appropriate?

[jbarrez]

Yes, as the javadocs says:

<code>
 
  /**
   * Only select process instances which are active, which means that
   * neither the process instance nor the corresponding process definition
   * are suspended.
   */
  ProcessInstanceQuery active();
</code>

About your other problem: how many are returned. There is a limit on the total amount returned, i believe. But it should be a large value.