Failure with h2 DB Unit test; Connection is broken: 'session closed'

[martan]

Trying the 2nd Unit test in the Book: Activiti In Action  from Chapter 1, bookorder.bpmn20.xml
Activiti engine Unit test failing in very first call:
ProcessEngine processEngine = ProcessEngineConfiguration
         .createStandaloneProcessEngineConfiguration()
          .buildProcessEngine();
In org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.initDataSource call to:
try {
      connection = dataSource.getConnection();
Fails with
Connection is broken: "session closed"
Using Eclipse Mars jdk7, and the downloaded code from the book(bpmn-examples), which uses activiti-engine-5.9.jar
Complete stack:
org.h2.jdbc.JdbcSQLException: Connection is broken: "session closed" [90067-132]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
   at org.h2.message.DbException.get(DbException.java:167)
   at org.h2.message.DbException.get(DbException.java:144)
   at org.h2.engine.SessionRemote.checkClosed(SessionRemote.java:447)
   at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:326)
   at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:219)
   at org.h2.engine.SessionRemote.createSession(SessionRemote.java:213)
   at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)
   at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)
   at org.h2.Driver.connect(Driver.java:58)
   at java.sql.DriverManager.getConnection(Unknown Source)
   at java.sql.DriverManager.getConnection(Unknown Source)
   at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:201)
   at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:196)
   at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.getConnection(UnpooledDataSource.java:93)
   at org.apache.ibatis.datasource.pooled.PooledDataSource.popConnection(PooledDataSource.java:385)
   at org.apache.ibatis.datasource.pooled.PooledDataSource.getConnection(PooledDataSource.java:89)
   at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.initDatabaseType(ProcessEngineConfigurationImpl.java:489)
   at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.initDataSource(ProcessEngineConfigurationImpl.java:451)
   at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.init(ProcessEngineConfigurationImpl.java:305)
   at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:282)
   at org.bpmnwithactiviti.chapter1.BookOrderTest.startBookOrder(BookOrderTest.java:27)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
   at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
   at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
   at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
   at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76)
   at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
   at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
   at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
   at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
   at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
   at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
   at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
   at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Just a hunch, but ProcessEngineConfigurationImpl.initDataSource() has this:
      if (dataSource instanceof PooledDataSource) {
        // ACT-233: connection pool of Ibatis is not properely initialized if this is not called!
        ((PooledDataSource)dataSource).forceCloseAll();
      }
which I think might be related to the issue.
Aside: (snowed in at home during SnowZilla 2016, and trying to learn about Activiti; this stack is a drag..)

[vasile_dirla]

hi martan ,
do you have a running H2 server ?
if so please check also the database config for your engine.

[martan]

Thanks for the response. Yes. I do.
I had the H2 running in the Activiti Explorer. I shut that down, and ran H2 from the H2 download's console, which has a 'test' DB.
I note from this post: https://forums.activiti.org/content/migrating-example-source-code-manning-book
that:
"The default URL for separately installed H2 is jdbc:h2:~/test. The book example expects jdbc:h2:tcp://localhost/~/activiti "
So, I tried his suggestion, and my code is essentially this:
  ProcessEngineConfiguration config = ProcessEngineConfiguration.createStandaloneProcessEngineConfiguration();
  config.setJdbcUrl("jdbc:h2:~activiti");
  //config.setDatabaseSchemaUpdate("true");
  //config.setJdbcUrl("jdbc:h2:~/activiti;AUTO_SERVER=TRUE");
  ProcessEngine processEngine = config.buildProcessEngine();
and when I do, I get a different error. If I use Activiti-engine.5.18, it strangely tells me that the db is version 5.18.0.1 in the console logs for url:jdbc:h2:~activiti, and it is not in the H2 console.
When I use Activiti-engine-5.19, I get this error, which appears to be an error in v5.19:
26 Jan 2016 13:23:28,845 [main] DEBUG org.apache.ibatis.logging.jdbc.BaseJdbcLogger:142 - ==>  Preparing: select distinct RES.* from ACT_RU_TASK RES WHERE RES.NAME_ = ? order by order by RES.ID_ asc LIMIT ? OFFSET ?
26 Jan 2016 13:23:28,860 [main] DEBUG org.activiti.engine.impl.interceptor.CommandContext:171 - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT DISTINCT RES.*
      
    FROM ACT_RU_TASK RES
    
     WHERE  RES.NAME_ = ?
   
     ORDER BY  ORDER

BY RES.ID_ ASC

    LIMIT ? OFFSET ? "; expected "=, NOT, EXISTS, SELECT, FROM"; SQL statement:
select distinct RES.*
      
    from ACT_RU_TASK RES
    
     WHERE  RES.NAME_ = ?
   
     order by  order by RES.ID_ asc 
    LIMIT ? OFFSET ? [42001-132]
### The error may exist in org/activiti/db/mapping/entity/Task.xml
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.selectTaskByQueryCriteria
Some blank lines removed above.
I unjarred the Task.xml from the activiti-engine-5.19.jar, and it has this:
  <!– Using distinct here because a task can appear multiple times in a resultset when –>
  <select id="selectTaskByQueryCriteria" parameterType="org.activiti.engine.impl.TaskQueryImpl" resultMap="taskResultMap">
    ${limitBefore}
    select distinct RES.* ${limitBetween}
    <include refid="selectTaskByQueryCriteriaSql"/>
    ${orderBy}
    ${limitAfter}
  </select>

Appreciate looking at this.
Cheers

[martan]

Sorry, at the end of previous post, I failed to paste the criteria query from Task.xml in activiti-engine-5.19.jar
It is this:
  <!– Using distinct here because a task can appear multiple times in a resultset when –>
  <select id="selectTaskByQueryCriteria" parameterType="org.activiti.engine.impl.TaskQueryImpl" resultMap="taskResultMap">
    ${limitBefore}
    select distinct RES.* ${limitBetween}
    <include refid="selectTaskByQueryCriteriaSql"/>
    ${orderBy}
    ${limitAfter}
  </select>

Note from previous post, that the actual SQL has the trailing order by clause repeated twice.

[martan]

Got it to work. I guess I didn't understand how H2 works. I had to KILL the H2 console w/ same jdbc url.
I changed the Unit test to essentially:
  ProcessEngineConfiguration config = ProcessEngineConfiguration
    .createStandaloneProcessEngineConfiguration();
  config.setJdbcUrl("jdbc:h2:~/activiti");
  config.setDatabaseSchemaUpdate("true");

That last line was crucial, otherwise it indicated that the Activiti-engine db version was 5.18.0.1 and the H2 db was at 5.18.0.0

It also works when I use mysql.
Thanks for responding.
btw the previous issue re SQL was a mybatis issue. I had to change the pom dependency from 3.0.4 initially, b/c it could not find that in the repo that Tijs included. So I changed it all the way to 3.3.0 (which is in error) when I changed it to 3.2.8, along with above, it worked fine.
Cheers

[jbarrez]

As a sidenote: we've upgraded to MyBatis 3.3.0 on master.

[martan]

hmmh.. well then you should try the above simple test.
With 3.3.0, the SQL generated has the reserved word "ORDER BY" repeated twice, and of course fails.
I'm at work not (snow plowed, yay). Tonight I'll post the simple Unit test to show you. I didn't want to get the source to MyBatis 3.3.0 to step thru where the error is.

Cheers and Thanks for the info

[jbarrez]

Yes, we saw that, hence why we fixed it 😉

The latest release: http://www.jorambarrez.be/blog/2016/01/29/activiti-5-19-0-2-released/ fixes that

[martan]

Thanks!
You rock

Cheers