topic Re: Periodic SQL Error in Activiti in Alfresco Archive

Periodic SQL Error in Activiti

sakumar1 — Wed, 11 Mar 2015 07:31:40 GMT

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 closeSEVERE: Error while closing

Re: Periodic SQL Error in Activiti

trademak — Wed, 11 Mar 2015 16:13:16 GMT

Hi,

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

Best regards,

Re: Periodic SQL Error in Activiti

christianscheer — Tue, 21 Apr 2015 15:32:22 GMT

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

Re: Periodic SQL Error in Activiti

trademak — Fri, 24 Apr 2015 13:23:27 GMT

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,

Re: Periodic SQL Error in Activiti

christianscheer — Fri, 24 Apr 2015 15:05:52 GMT

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.

Re: Periodic SQL Error in Activiti

christianscheer — Mon, 27 Apr 2015 10:57:31 GMT

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.

Re: Periodic SQL Error in Activiti

jbarrez — Tue, 28 Apr 2015 18:47:51 GMT

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

Re: Periodic SQL Error in Activiti

mnarendra333 — Sat, 07 Apr 2018 18:11:04 GMT

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 ?

Re: Periodic SQL Error in Activiti

tamankaur — Mon, 18 Jun 2018 18:53:38 GMT

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)