topic Re: Missing table 'ACT_RU_JOB' with h2 database issue with 5.16.x in Alfresco Archive

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

dharmesh — Thu, 16 Oct 2014 20:34:57 GMT

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

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

trademak — Fri, 17 Oct 2014 06:36:01 GMT

Difficult to say anything without your unit test code.

Best regards,

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

dharmesh — Fri, 17 Oct 2014 13:03:16 GMT

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>

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

jbarrez — Mon, 20 Oct 2014 08:58:20 GMT

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

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

dharmesh — Mon, 20 Oct 2014 16:21:48 GMT

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>

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

jbarrez — Tue, 21 Oct 2014 07:29:00 GMT

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.

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

dharmesh — Tue, 21 Oct 2014 12:53:14 GMT

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

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

jbarrez — Wed, 22 Oct 2014 10:57:31 GMT

perfect, thanks.

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

osilva — Thu, 23 Oct 2014 23:19:08 GMT

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)

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

jbarrez — Fri, 24 Oct 2014 09:17:59 GMT

* 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

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

osilva — Fri, 24 Oct 2014 19:23:20 GMT

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.

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

jbarrez — Mon, 27 Oct 2014 11:17:29 GMT

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.

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

osilva — Mon, 27 Oct 2014 15:07:56 GMT

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.

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

jbarrez — Tue, 28 Oct 2014 10:52:01 GMT

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.

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

osilva — Fri, 31 Oct 2014 18:39:57 GMT

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

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

jbarrez — Mon, 03 Nov 2014 10:27:08 GMT

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