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

[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/...

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.