Periodic SQL Error in Activiti

[sakumar1]

We are getting below error in Console Periodically, However when we ran the Query Manually it didn't give me any error . Can you please tell me why Activiti throwing Below error in Console?

Mar 11, 2015 12:57:07 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

### The error may exist in org/activiti/db/mapping/entity/Job.xml
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
### The error occurred while setting parameters
### SQL: select * from ( select a.*, ROWNUM rnum from (     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)       )         ) a where ROWNUM < ?) where rnum  >= ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
        at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:286)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:277)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:272)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:259)
        at org.activiti.engine.impl.persistence.entity.JobEntityManager.findNextJobsToExecute(JobEntityManager.java:105)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:48)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:33)
        at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57)
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
        at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:59)
        at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3594)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3695)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
        at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:101)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
        … 18 more
Regards,
Sakumar

[trademak]

Hi,

Can you provide a bit more background in when this is happening?
Is this happening in unit tests?

Best regards,

[christianscheer]

I have a similar stacktrace in Activiti 5.14. This did not occur in a unit test.

16:37:23.173 ERROR org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable exception during job acquisition:
### Error querying database.  Cause: org.hibernate.exception.SQLGrammarException: ORA-00942: Tabelle oder View nicht vorhanden
### The error may exist in org/activiti/db/mapping/entity/Job.xml
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
### The error occurred while setting parameters
### SQL: select * from ( select a.*, ROWNUM rnum from (     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)       )         ) a where ROWNUM < ?) where rnum  >= ?
### Cause: org.hibernate.exception.SQLGrammarException: ORA-00942: Tabelle oder View nicht vorhanden
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.hibernate.exception.SQLGrammarException: ORA-00942: Tabelle oder View nicht vorhanden
### The error may exist in org/activiti/db/mapping/entity/Job.xml
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
### The error occurred while setting parameters
### SQL: select * from ( select a.*, ROWNUM rnum from (     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)       )         ) a where ROWNUM < ?) where rnum  >= ?
### Cause: org.hibernate.exception.SQLGrammarException: ORA-00942: Tabelle oder View nicht vorhanden
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23) ~[mybatis-3.2.5.jar:3.2.5]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107) ~[mybatis-3.2.5.jar:3.2.5]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98) ~[mybatis-3.2.5.jar:3.2.5]
        at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:422) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:413) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:408) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:395) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.persistence.entity.JobEntityManager.findNextJobsToExecute(JobEntityManager.java:97) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:47) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:32) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47) ~[activiti-spring-5.16.3.jar:5.16.3]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131) ~[spring-tx-3.2.4.RELEASE.jar:3.2.4.RELEASE]
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45) ~[activiti-spring-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35) ~[activiti-engine-5.16.3.jar:5.16.3]
        at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:54) ~[activiti-engine-5.16.3.jar:5.16.3]
        at java.lang.Thread.run(Unknown Source) [na:1.7.0_55]
Caused by: org.hibernate.exception.SQLGrammarException: ORA-00942: Tabelle oder View nicht vorhanden
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        at com.sun.proxy.$Proxy58.execute(Unknown Source) ~[na:na]
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56) ~[mybatis-3.2.5.jar:3.2.5]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70) ~[mybatis-3.2.5.jar:3.2.5]
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57) ~[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
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: Tabelle oder View nicht vorhanden
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
        at sun.reflect.GeneratedMethodAccessor46.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.7.0_55]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.7.0_55]
        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ~[hibernate-core-4.1.12.Final.jar:4.1.12.Final]
        … 28 common frames omitted


[trademak]

That's a very old post you are reusing ;-). Is this reproducible? If so, did you test if it's also occurring with Activiti 5.17?

Best regards,

[christianscheer]

Our Activiti Solution with Version 5.14 was started for the first time on the production system. This error occures now once every few seconds. For a few hours after the start of the Tomcat everything was fine.

During development and intensiv testing this error did not occur. It is somewhat related to the new environment (tomcat, database etc.).
We neither have this problem with Activiti 5.16.4 (current development) on any system.

Operations did not get back to my questions on how a manual execution of the query performs. I will get back to you on monday, hopefully i have an anwer to that by then. It's Friday afternoon in my timezone right now.

[christianscheer]

Hi.
After a reboot of the Application Server (Tomcat 6), the problem disappeared. For now, we are fine. I will get back to you with a more in depth analysis, if it occures again.

[jbarrez]

Hmm very odd. Keep us posted if you see it happening again!

[mnarendra333]

even i am also getting the same issue but from my end use case is little different i copied all dev activiti tables to prod schema then i deployed activiti-explorer which connects to prod db instance  i am getting this error from activiti-explorer war file 

engine.properties :

# demo data properties
create.demo.users=false
create.demo.definitions=false
create.demo.models=false
create.demo.reports=false

# engine properties
engine.schema.update=true
engine.activate.jobexecutor=false
engine.asyncexecutor.enabled=true
engine.asyncexecutor.activate=true
engine.history.level=full

# email properties
#engine.email.enabled=true
#engine.email.host=localhost
#engine.email.port=1025

error saying table not found when i verified in DB both the tables are present which are mentioned in the query

any idea why this error will occur ?

[tamankaur]

Was this issue resolved ?? Did you get that error again ?? 

I am facing similar issue on production system but not on dev or qa and it's random.

I am using 5.21 version

### The error may exist in org/activiti/db/mapping/entity/Job.xml
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextTimerJobsToExecute
### The error occurred while handling results
### SQL: 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 TYPE_ = 'timer' and ( (RES.EXECUTION_ID_ is null) or (PI.SUSPENSION_STATE_ = 1) ) LIMIT ? OFFSET ?
### Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'TYPE_' from result set. Cause: java.sql.SQLException: No such column :TYPE_
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:122)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:443)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:434)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:429)