Missing table 'ACT_RU_JOB' with h2 database issue with 5.16.x

[dharmesh]

My unit tests give me below error when i execute test suite with h2 database. Please note it works fine with Postgres.

Caused by: org.h2.jdbc.JdbcSQLException: Table "ACT_RU_JOB" not found; SQL statement:
select 
       RES.*           
    from ACT_RU_JOB RES    
       LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_
    where (RES.RETRIES_ > 0)
      and (RES.DUEDATE_ is null or RES.DUEDATE_ <= ?)
      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ <= ?)
     and (
            (RES.EXECUTION_ID_ is null)
           or 
           (PI.SUSPENSION_STATE_ = 1)
      )  
    LIMIT ? OFFSET ? [42102-166]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.message.DbException.get(DbException.java:169) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.message.DbException.get(DbException.java:146) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.readTableOrView(Parser.java:4757) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.readTableFilter(Parser.java:1084) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1690) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parseSelectSimple(Parser.java:1797) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parseSelectSub(Parser.java:1684) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parseSelectUnion(Parser.java:1527) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parseSelect(Parser.java:1515) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parsePrepared(Parser.java:405) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parse(Parser.java:279) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.parse(Parser.java:251) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.command.Parser.prepareCommand(Parser.java:217) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.engine.Session.prepareLocal(Session.java:415) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.engine.Session.prepareCommand(Session.java:364) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71) ~[h2-1.3.166.jar:1.3.166]
   at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:266) ~[h2-1.3.166.jar:1.3.166]
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) ~[commons-dbcp-1.4.jar:1.4]
   at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) ~[commons-dbcp-1.4.jar:1.4]
   at sun.reflect.GeneratedMethodAccessor34.invoke(Unknown Source) ~[na:na]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_51]
   at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_51]
   at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:240) ~[spring-jdbc-4.0.6.RELEASE.jar:4.0.6.RELEASE]
   at com.sun.proxy.$Proxy72.prepareStatement(Unknown Source) ~[na:na]
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:72) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:82) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:54) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:70) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81) ~[mybatis-3.2.5.jar:3.2.5]
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104) ~[mybatis-3.2.5.jar:3.2.5]
   … 18 common frames omitted
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[trademak]

Difficult to say anything without your unit test code.

Best regards,

[dharmesh]

This is one of the basic test with activiti and I am having feeling that it is something on my machine causing this.
But why it works with postgres!!!

Unit Test:

<code>
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:META-INF/spring/test-context.xml"})
public class TestBasicWorkflow extends ActivitiTestBase{

@Test()
public void validateXML() {
 
  repositoryService.createDeployment().addClasspathResource("workflow/basic.bpmn20.xml").deploy();

  ProcessInstance pi = runtimeService.startProcessInstanceByKey("dcrApprovalProcess_basic");
 
  completeNextSingleTask(pi);//Create DCR
  completeNextSingleTask(pi);//Edit DCR
  completeNextSingleTask(pi);//Review DCR
 
  assertEquals(true, isClosed(pi));
}

       protected void completeNextSingleTask(ProcessInstance pi){
 
  TaskQuery query = taskService.createTaskQuery()
      .processInstanceId(pi.getProcessInstanceId());
  Task task = query.singleResult();
  taskService.complete(task.getId());
}
}
</code>

Workflow XML:

<code>

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<definitions xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:activiti="http://activiti.org/bpmn" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlnsmgdc="http://www.omg.org/spec/DD/20100524/DC" xmlnsmgdi="http://www.omg.org/spec/DD/20100524/DI" xmlns:tns="http://www.activiti.org/test" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:yaoqiang="http://bpmn.sourceforge.net" exporter="Yaoqiang BPMN Editor" exporterVersion="2.2.18 (GPLv3, Non-Commercial)" expressionLanguage="http://www.w3.org/1999/XPath" id="_1408998459866" name="" targetNamespace="http://www.activiti.org/test" typeLanguage="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://www.omg.org/spec/BPMN/20100524/MODEL http://bpmn.sourceforge.net/schemas/BPMN20.xsd">
  <process id="dcrApprovalProcess_basic" isClosed="false" isExecutable="true" name="DCR Workflow Process" processType="None">
    <startEvent id="startevent1" isInterrupting="true" name="Start" parallelMultiple="false">
      <outgoing>flow1</outgoing>
    </startEvent>
    <endEvent id="endevent1" name="End">
      <incoming>flow6</incoming>
    </endEvent>
    <userTask completionQuantity="1" id="CREATE" implementation="##unspecified" isForCompensation="false" name="Create DCR" startQuantity="1">
      <incoming>flow1</incoming>
      <outgoing>flow4</outgoing>
    </userTask>
    <sequenceFlow id="flow1" sourceRef="startevent1" targetRef="CREATE"/>
    <userTask completionQuantity="1" id="REVIEW" implementation="##unspecified" isForCompensation="false" name="Review DCR" startQuantity="1">
      <incoming>flow8</incoming>
      <outgoing>flow6</outgoing>
    </userTask>
    <serviceTask activiti:delegateExpression="${dummyServiceTask}" completionQuantity="1" id="linkDCRWithWorkflowInDB" implementation="##WebService" isForCompensation="false" name="Call Spring Bean" startQuantity="1">
      <incoming>flow4</incoming>
      <outgoing>flow7</outgoing>
    </serviceTask>
    <sequenceFlow id="flow4" sourceRef="CREATE" targetRef="linkDCRWithWorkflowInDB"/>
    <sequenceFlow id="flow6" sourceRef="REVIEW" targetRef="endevent1"/>
    <userTask completionQuantity="1" id="EDIT" implementation="##unspecified" isForCompensation="false" name="Edit DCR" startQuantity="1">
      <incoming>flow7</incoming>
      <outgoing>flow8</outgoing>
    </userTask>
    <sequenceFlow id="flow7" sourceRef="linkDCRWithWorkflowInDB" targetRef="EDIT"/>
    <sequenceFlow id="flow8" sourceRef="EDIT" targetRef="REVIEW"/>
  </process>
</definitions>

</code>

[jbarrez]

How's your Activiti config looking? Is it actually creating the database tables?

[dharmesh]

I use dbcp-common 1.2.2 and h2 1.3.166.

This issue was introduced when I wrote timer based unit test and all test started failing because of this.
I reverted to my earlier running build and I am good now, i am able to execute tests, the one which i shared with you earlier also works this time.

However unit tests run in isolation so theoretically they should not interfere so basically i dont have any clue why i had that problem. I have feeling that i will see this again if i will introduce timer based unit test. So I will further post when i start doing that activity again.

Currently i am using 5.16 version.

Below is the main configuration of activiti unit test suite where i can switch between postgres and h2 easily.


<code>
       <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
             <property name="dataSource" ref="dataSourceAct" />
             <property name="transactionManager" ref="transactionManagerAct" />
             <property name="databaseSchemaUpdate" value="true" />
             <property name="jobExecutorActivate" value="true" />
        </bean>
<bean id="dataSourceAct" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="${jdbc.driver}" />
  <property name="url" value="${activiti.jdbc.url}" />
  <property name="username" value="${db.username}" />
  <property name="password" value="${db.password}" />
</bean>
</code>

[jbarrez]

That config looks ok. I don't know why that error happened, in theory if the other ACT_RU_* tables are created … the ACT_RU_JOB should be created just as well. Very odd.

[dharmesh]

I will post you on that once i start timer based implementation. I will share root cause and unit tests with your team.

[jbarrez]

perfect, thanks.

[osilva]

I found this issue happens when running with H2 in-memory and a SpringJUnit4ClassRunner (and of course, a spring-managed processEngine)
Particularly in my case, it was being caused during execution of below code (during shutdown sequence):
AcquireJobsRunnable.run() –> AcquiredJobs.execute() –> commandContext.getJobEntityManager().findNextJobsToExecute(new Page(0, maxNonExclusiveJobsPerAcquisition));

When the test methods are completed, the TestRunner invokes a System.exit() and the shutdown sequence is started.

The ApplicationShutdownHooks are run in parallel (threads) - i.e with no guaranteed order - among those being H2 DatabaseCloser and AbstractApplicationContext shutdown hook.

====================================================================================
Basically, the AcquireJobsRunnable could keep running after the test suite is finished but after the DatabaseCloser has closed the DB.
====================================================================================
AbstractApplicationContext.doClose –> … –> ProcessEngineFactoryBean.destroy() –> ProcessEngineImpl.close() –> JobExecutor.shutdown()

I guess an error message may create concern and is not nice to spot, but even when this doesn't seem to be a neat shutdown, I understand it's not relevant to the actual test execution (i.e. everything is being shut down)

Other observations/findings:
———————–
- issue doesn't occur when switching over to embedded H2 (i.e. persisted to files).  Without digging much, I suppose that even when the db might get closed the tables are still there (not sure if it might get reopened by the jobExecutor thread).  Also,  even if "create-drop" is used … the drop doesn't happen until after the jobExecutor is shutdown (see ProcessEnginImp.close() sequence)
- issue goes away (with in-mem db) if "DB_CLOSE_ON_EXIT=false" is added to the h2 url definition, since this property will prevent the DatabaseCloser hook to be created/registered


Note: I can reproduce the issue with a single empty test method within the class (since the issue is erratic and is timing dependent, decreasing the JobExecutor waiting frequency may "hide" it, i.e. increasing waitTimeInMillis)

[jbarrez]

* Thats why we have in our tests a method called 'waitForJobExecutorToProcessAllJobs', to make sure no jobs are left anymore at the end of a test.

* Indeed. Embedded H2 database gets reopened automatically when you create a connection.

* Indeed, adding DB_CLOSE_ON_EXIT or DB_CLOSE_DELAY=-1 is what we use in our tests