topic Re: Failure with h2 DB Unit test; Connection is broken: 'session closed' in Alfresco Archive

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

martan — Mon, 25 Jan 2016 22:26:08 GMT

Trying the 2nd Unit test in the Book: Activiti In Action from Chapter 1, bookorder.bpmn20.xmlActiviti engine Unit test failing in very first callrocessEngine processEngine = ProcessEngineConfiguration .createStandaloneProcessEngineConfiguration() .buildProcessEngine(); In org.act

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

vasile_dirla — Tue, 26 Jan 2016 07:03:31 GMT

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

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

martan — Tue, 26 Jan 2016 18:30:11 GMT

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

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

martan — Tue, 26 Jan 2016 18:43:53 GMT

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.

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

martan — Tue, 26 Jan 2016 22:28:31 GMT

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

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

jbarrez — Wed, 27 Jan 2016 14:46:59 GMT

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

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

martan — Wed, 27 Jan 2016 15:01:42 GMT

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

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

jbarrez — Mon, 01 Feb 2016 08:48:56 GMT

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

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

martan — Mon, 01 Feb 2016 15:54:12 GMT

Thanks!
You rock

Cheers