'selectExclusiveJobsToExecute' does not work for DB2 and MSSQL

2012-03-26 18:12:01,625 ERROR | Thread-821 - () | [AcquireJobsRunnable:86] exception during job acquisition:

1. 1. 1. Error querying database. Cause: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
      2. The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectExclusiveJobsToExecute-Inline
      3. The error occurred while setting parameters
      4. Cause: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
         org.apache.ibatis.exceptions.PersistenceException:
      5. Error querying database. Cause: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
      6. The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectExclusiveJobsToExecute-Inline
      7. The error occurred while setting parameters
      8. Cause: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
         at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
         at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:81)
         at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73)
         at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:182)
         at org.activiti.engine.impl.persistence.entity.JobManager.findExclusiveJobsToExecute(JobManager.java:123)
         at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:62)
         at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:33)
         at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
         at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
         at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
         at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
         at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
         at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
         at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:57)
         at java.lang.Thread.run(Thread.java:619)
         Caused by: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
         at com.ibm.db2.jcc.am.bd.a(bd.java:676)
         at com.ibm.db2.jcc.am.bd.a(bd.java:60)
         at com.ibm.db2.jcc.am.bd.a(bd.java:127)
         at com.ibm.db2.jcc.am.km.c(km.java:2506)
         at com.ibm.db2.jcc.am.km.d(km.java:2483)
         at com.ibm.db2.jcc.am.km.a(km.java:1963)
         at com.ibm.db2.jcc.t4.db.g(db.java:139)
         at com.ibm.db2.jcc.t4.db.a(db.java:39)
         at com.ibm.db2.jcc.t4.t.a(t.java:32)
         at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
         at com.ibm.db2.jcc.am.km.eb(km.java:1934)
         at com.ibm.db2.jcc.am.lm.ic(lm.java:2798)
         at com.ibm.db2.jcc.am.lm.b(lm.java:3517)
         at com.ibm.db2.jcc.am.lm.Yb(lm.java:2130)
         at com.ibm.db2.jcc.am.lm.execute(lm.java:2114)
         at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
         at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
         at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
         at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
         at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
         at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
         at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
         at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
         at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79)
         ... 13 more
         2012-03-26 18:12:01,671 ERROR | Thread-821 - () | [CommandContext:121] Error while closing command context
         org.apache.ibatis.exceptions.PersistenceException:
      9. Error querying database. Cause: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
      10. The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectExclusiveJobsToExecute-Inline
      11. The error occurred while setting parameters
      12. Cause: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
          at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
          at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:81)
          at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73)
          at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:182)
          at org.activiti.engine.impl.persistence.entity.JobManager.findExclusiveJobsToExecute(JobManager.java:123)
          at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:62)
          at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:33)
          at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
          at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
          at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
          at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
          at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
          at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
          at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:57)
          at java.lang.Thread.run(Thread.java:619)
          Caused by: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-401, SQLSTATE=42818, SQLERRMC==, DRIVER=3.57.82
          at com.ibm.db2.jcc.am.bd.a(bd.java:676)
          at com.ibm.db2.jcc.am.bd.a(bd.java:60)
          at com.ibm.db2.jcc.am.bd.a(bd.java:127)
          at com.ibm.db2.jcc.am.km.c(km.java:2506)
          at com.ibm.db2.jcc.am.km.d(km.java:2483)
          at com.ibm.db2.jcc.am.km.a(km.java:1963)
          at com.ibm.db2.jcc.t4.db.g(db.java:139)
          at com.ibm.db2.jcc.t4.db.a(db.java:39)
          at com.ibm.db2.jcc.t4.t.a(t.java:32)
          at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
          at com.ibm.db2.jcc.am.km.eb(km.java:1934)
          at com.ibm.db2.jcc.am.lm.ic(lm.java:2798)
          at com.ibm.db2.jcc.am.lm.b(lm.java:3517)
          at com.ibm.db2.jcc.am.lm.Yb(lm.java:2130)
          at com.ibm.db2.jcc.am.lm.execute(lm.java:2114)
          at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
          at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
          at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
          at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
          at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
          at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
          at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
          at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
          at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79)
          ... 13 more

Most databases with "native" boolean types will support string literals as well: 'true' or 'false' instead of TRUE / FALSE. This will e.g. work both for Postgres, H2 and MSSQL (DB2 not checked so far, but I doubt it would work ...).

Changing point of view, it seems to be extremely rare for usual database systems to support true native boolean literals, while literal string representation seem to work for most databases with native bit / boolean type support.

Therefore I'd suggest to change the default statement from

and (EXCLUSIVE_ = TRUE)

to

and (EXCLUSIVE_ = 'true')

And BTW, this bug is a show stopper on systems like MS-SQL, so a timely fix would be highly appreciated ...

Most databases with "native" boolean types will support string literals as well: 'true' or 'false' instead of TRUE / FALSE. This will e.g. work both for Postgres, H2 and MSSQL (DB2 not checked so far, but I doubt it would work ...).

Changing point of view, it seems to be extremely rare for usual database systems to support true native boolean literals, while literal string representation seem to work for most databases with native bit / boolean type support.

Therefore I'd suggest to change the default statement from

and (EXCLUSIVE_ = TRUE)

to

and (EXCLUSIVE_ = 'true')

And BTW, this bug is a show stopper on systems like MS-SQL, so a timely fix would be highly appreciated ...

The problem was introduced with ACT-989 fix.

Proposed patch to fix issue

FYI - as a work around, I've created a class extending DbSqlSessionFactory that statically adds a "mssql" override to use the existing oracle query -

addDatabaseSpecificStatement("mssql", "selectExclusiveJobsToExecute", "selectExclusiveJobsToExecute_oracle");

Configure the dbSqlSessionFactory property on the processEngineConfiguration to use the new class.
It's working fine - but obviously will remove when native fix is issued.

On a related issue, is there a plan for any bug fix releases, e.g. 5.9.1? It doesn't appear to be something the activiti team have done in the past - I assume this was due to the monthly release policy. Now release frequency has reduced a bug fix release would seem sensible. It seems wrong to wait until 5.10 for something like this. Probably should raise in forum rather than jira (apologies for that)!

Yes, Al, we are doing the same thing: overriding DbSqlSessionFactory and hooking in the custom sql statements.

I also agree that a bug fix release before 5.10 would be very sensible.

Thanks for reporting this issue.

A fix is commited here: http://fisheye.codehaus.org/changelog/activiti/?cs=3367

Can you confirm that this solves the issue?

Daniel, your patch does what we did locally to patch Activiti 5.9. So, it is looking good. But... I cannot try it out right now until I get back from my vacation.

Can confirm that it runs in our environment as well.
Any news on a fix release like 5.9.1? This is definitely a blocker for MSSQL users.

Does somebody know how to fix this temporary?
We use the activiti engine in a project, importing it in our pom.

Should i check the activiti engine out, make the changes proposed in this issue and rebuild with an intermediate version?

Any help should be great, i had discovered already the problem in ACT_RU_JOB EXCLUSIVE_ but don't know the impact of that.

How should i use the patch? Should i just modify the job.xml?

my sincere thanks,

Michallis

Some of the comments above explain what Activiti methods to override to make this work without having to build a custom Activiti.

Fixed on trunk. Thanks Etienne for the patch.

Thank you Etienne and Daniel!