[GSS](6.4.z) Not possible to migrate process with recurring timer

– This is different issue than https://issues.jboss.org/browse/RHBPMS-5124

It is not possible to migrate a process with reoccurring timer (i.e. when Cycle attribute is set and the timer already fired at least one time)

When I enabled debug/trace hibernate logging, I was able to observe following:
Migration Service will try to migrate active events (i.e. timer):

06:48:57,337 DEBUG SQL:104 - select eventtypes0_.InstanceId as Instance1_25_0_, eventtypes0_.element as element2_12_0_ from EventTypes eventtypes0_ where eventtypes0_.InstanceId=?
06:48:57,338 TRACE BasicBinder:84 - binding parameter [1] as [BIGINT] - 111749
06:48:57,342 TRACE BasicExtractor:74 - Found [111749] as column [Instance1_25_0_]
06:48:57,342 TRACE BasicExtractor:74 - Found [timer] as column [element2_12_0_]

But it wants to make sure it migrates only the active ones - i.e. the ones, which were entered but not exited:

06:48:57,347 DEBUG SQL:104 - select nodeinstan0_.nodeInstanceId as col_0_0_ from NodeInstanceLog nodeinstan0_ where nodeinstan0_.nodeId=? and nodeinstan0_.processInstanceId=? group by nodeinstan0_.nodeInstanceId having sum(nodeinstan0_.type)=0

Notice the usage of keyword sum. In case of reoccurring timer this condition will never be fulfilled. This is because the reoccurring timer have entries in NodeInstanceLog with nodeType equal to 1 (for previous timer executions) and also always one entry with nodeType equal to 0 (this one represents the currently active timer instance). So sum (N*1+0) will never be equal to 0.

Therefore, the subsequent query:

06:48:57,354 DEBUG SQL:104 - update NodeInstanceLog set nodeId=?, nodeName=?, nodeType=? where (nodeInstanceId in ()) and processInstanceId=?

Fails with syntax error:

06:48:57,363 ERROR SqlExceptionHelper:146 - ERROR: syntax error at or near ")"
Position: 90
06:48:57,371 ERROR MigrationManager:231 - Unexpected error during migration
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1397)
at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:111)
at org.jbpm.runtime.manager.impl.migration.MigrationManager.updateNodeInstances(MigrationManager.java:383)
at org.jbpm.runtime.manager.impl.migration.MigrationManager.upgradeProcessInstance(MigrationManager.java:330)
at org.jbpm.runtime.manager.impl.migration.MigrationManager.migrate(MigrationManager.java:217)
at org.jbpm.runtime.manager.impl.migration.MigrationManager.migrate(MigrationManager.java:102)
at org.jbpm.kie.services.impl.admin.ProcessInstanceMigrationServiceImpl.migrate(ProcessInstanceMigrationServiceImpl.java:46)
at org.redhat.support.bpmsuite.migration.EmbeddedMigrationService.migrateProcessInstance(EmbeddedMigrationService.java:106)
at org.redhat.support.bpmsuite.migration.EmbeddedMigrationService.main(EmbeddedMigrationService.java:69)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:282)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:189)
at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:108)
at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:77)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:415)
at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:321)
at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1251)
at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:116)
at org.hibernate.ejb.QueryImpl.internalExecuteUpdate(QueryImpl.java:198)
at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:102)
... 13 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 90
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:422)
at com.sun.proxy.$Proxy56.executeUpdate(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64)
at com.sun.proxy.$Proxy57.executeUpdate(Unknown Source)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186)
... 21 more