nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Find more posts tagged with

Comments

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" xmlns

mgdc="http://www.omg.org/spec/DD/20100524/DC" xmlns

mgdi="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

osilva

I believe I tried "waitForJobExecutorToProcessAllJobs" to no avail for this issue.

I guess the problem here is not with waiting for a job to finish (there's none created in an empty test method body) but with the JobExecutor polling (then the ACT_RU_JOB query breaking) even after the database is closed (i.e. during the shutdown sequence). Again, from my findings above, AcquireJobsRunnable runs in a separate thread which is stopped by a shutdown hook (spring ctx file) which, is different from the one closing the DB (h2 DatabaseCloser) and may be completed afterwards the db closer hook(sequence order not guaranteed).

One more thing, DB_CLOSE_DELAY=-1 doesn't prevent the registration of the DatabaseCloser hook (then creating the conditions for the issue), only DB_CLOSE_ON_EXIT will do it.

jbarrez

I've seen the exception before too, but never with a 'real' database (oracle, postgres, whatever …). What's the use case for trying to make this work on H2? We only use H2 for our tests.

osilva

That's the use case, automated tests.
I run into this post after facing the issue in the subject, then I digged further to understand the problem.

As per my first comment, my test setup is with H2 in-memory and a SpringJUnit4ClassRunner.

jbarrez

Hmm very odd, we use H2 in mem for all our tests: https://github.com/Activiti/Activiti/tree/master/modules/activiti-engine/src/test/java/org/activiti/engine/test

However, we don't start up the job executor automatically. Rather, we use the waitForJobExecutorToProcessAllJobs method, which starts (AND STOPS) the job executor synchronously. Hence why we never see the problem.

osilva

Thanks Joram.

I am (purposedly) setting up my tests more in an integrated fashion and I now realize I ommited that part (H2 in-memory + SpringJUnit4ClassRunner is not enough info) when summarizing my setup. I don't use spring context test files, but the main app context file so as to have the process engine fully integrated with my spring beans and camel routes -though some test overrides are in place to stub stuff-.

Thus, the processEngine is spring-managed (and then started, shutdown). Maybe relevant to this discussion is that the jobExecutorActivate is enabled.

On a separate note …
- I found TestHelper and ActivitiTestCase family use the junit.framework.* components, then running under jUnit 3?
- (this one not so separate) does the fact that waitForJobExecutorToProcessAllJobs starts/stops the jobExecutor mean that we can't have jobExecutorActivate flag turned on in the processEngine under test?

Omar

jbarrez

" I found TestHelper and ActivitiTestCase family use the junit.framework.* components, then running under jUnit 3? "

Yes, historical reasons. We've got a lot of Junit 3 unit tests.

* does the fact that waitForJobExecutorToProcessAllJobs starts/stops the jobExecutor mean that we can't have jobExecutorActivate flag turned on in the processEngine under test

No, we do that because otherwise the job executor behaviour is not predictable, since it sleeps when it can't find a job and obviously you don't know how long your test code will need.