Camunda 7.20 and Oracle Database: java.sql.SQLException: ORA-17133

oateam · January 16, 2024, 7:59am

Hello,

I’m encountering an issue while installing Camunda 7 for the first time using Apache Tomcat and Oracle Database. Following the instructions in the Camunda 7 documentation, specifically URL, I’ve set up the database and started the server.

However, when I try to log in at /camunda, I’m getting a status 500 server error related to the database. Here’s the datasource definition I used:

<Resource
              name="jdbc/ProcessEngine"
              auth="Container"
              type="javax.sql.DataSource"
              driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@[redacted]"
              username="[redacted]"
              password="[redacted]"
              maxTotal="25"
              maxIdle="10"
              maxWaitMillis="-1" />

In the catalina.out error log, I found the following details:

16-Jan-2024 07:22:48.711 SEVERE [http-nio-8090-exec-3] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [default] in context with path [/camunda] threw exception
        org.camunda.bpm.engine.ProcessEnginePersistenceException: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
                at org.camunda.bpm.engine.impl.util.ExceptionUtil.wrapPersistenceException(ExceptionUtil.java:357)
                at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:352)
                at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeSelectList(DbSqlSession.java:112)
                at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:104)
                at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectBoolean(DbEntityManager.java:195)
                at org.camunda.bpm.engine.impl.persistence.entity.AuthorizationManager.isAuthorized(AuthorizationManager.java:280)
                at org.camunda.bpm.engine.impl.persistence.entity.AuthorizationManager.isAuthorized(AuthorizationManager.java:260)
                at org.camunda.bpm.engine.impl.cmd.AuthorizationCheckCmd.execute(AuthorizationCheckCmd.java:69)
                at org.camunda.bpm.engine.impl.cmd.AuthorizationCheckCmd.execute(AuthorizationCheckCmd.java:40)
                at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
                at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:110)
                at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
                at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35)
                at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
                at org.camunda.bpm.engine.impl.interceptor.ExceptionCodeInterceptor.execute(ExceptionCodeInterceptor.java:55)
                at org.camunda.bpm.engine.impl.AuthorizationServiceImpl.isUserAuthorized(AuthorizationServiceImpl.java:58)
                at org.camunda.bpm.webapp.impl.security.auth.AuthenticationUtil.isAuthorizedForApp(AuthenticationUtil.java:159)
                at org.camunda.bpm.webapp.impl.security.auth.AuthenticationUtil.createAuthentication(AuthenticationUtil.java:111)
                at org.camunda.bpm.webapp.impl.security.auth.ContainerBasedAuthenticationFilter.createAuthentication(ContainerBasedAuthenticationFilter.java:182)
                at org.camunda.bpm.webapp.impl.security.auth.ContainerBasedAuthenticationFilter.doFilter(ContainerBasedAuthenticationFilter.java:105)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
                at edu.yale.its.tp.cas.client.filter.CASFilter.doFilter(CASFilter.java:600)
                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481)
                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
                at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:673)
                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
                at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390)
                at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
                at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926)
                at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791)
                at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
                at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
                at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
                at java.base/java.lang.Thread.run(Thread.java:833)
        Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'CASEWHENEXISTS(SELECTA.RESOURCE_ID_FROMACT_RU_AUTHORIZATIONAWHEREA.TYPE_=:"SYS_B_00"ANDA.USER_ID_=:1ANDBITAND(A.PERMS_,:2)=:3ANDA.RESOURCE_TYPE_=:4ANDA.RESOURCE_ID_=:5)THEN:"SYS_B_01"WHENEXISTS(SELECTID_FROMACT_RU_AUTHORIZATIONAWHEREA.TYPE_=:"SYS_B_02' from result set.  Cause: java.sql.SQLException: ORA-17133: Identifier or literal is invalid.
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Authorization.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.isUserAuthorizedForResource
### The error occurred while handling results
### SQL: SELECT     CASE                         WHEN                                   EXISTS                         (SELECT                     A.RESOURCE_ID_              FROM                     ACT_RU_AUTHORIZATION A              WHERE                     A.TYPE_ = 1              AND                     A.USER_ID_ = ?              AND                     BITAND(A.PERMS_,?) = ?              AND                     A.RESOURCE_TYPE_ = ?              AND                     A.RESOURCE_ID_ =  ? )         THEN 1                                       WHEN         EXISTS           (SELECT                   ID_            FROM                   ACT_RU_AUTHORIZATION A            WHERE                   A.TYPE_ = 1            AND                   A.USER_ID_ = ?            AND                   BITAND(A.PERMS_,?) = ?            AND                   A.RESOURCE_TYPE_ = ?            AND                   A.RESOURCE_ID_ = '*')         THEN 1                        ELSE           (           SELECT             CASE                                                                       WHEN                                                                 EXISTS                                             (SELECT                               A.RESOURCE_ID_                        FROM                               ACT_RU_AUTHORIZATION A                        WHERE                               A.TYPE_ = 1                        AND                               A.GROUP_ID_ IN  (  ? )                         AND                               BITAND(A.PERMS_,?) = ?                        AND                               A.RESOURCE_TYPE_ = ?                        AND                               A.RESOURCE_ID_ =  ? )                   THEN 1                                                                                         WHEN                   EXISTS                     (SELECT                             ID_                      FROM                             ACT_RU_AUTHORIZATION A                      WHERE                             A.TYPE_ = 1                      AND                             A.GROUP_ID_ IN  (  ? )                       AND                             BITAND(A.PERMS_,?) = ?                      AND                             A.RESOURCE_TYPE_ = ?                      AND                             A.RESOURCE_ID_ = '*')                   THEN 1                                                      ELSE (                       SELECT                         CASE                                                                                                    WHEN                                                                                               EXISTS                                                                 (SELECT                                         A.RESOURCE_ID_                                  FROM                                         ACT_RU_AUTHORIZATION A                                  WHERE                                         A.TYPE_ = 0                                  AND                                         A.USER_ID_ = '*'                                  AND                                         BITAND(A.PERMS_,?) = ?                                  AND                                         A.RESOURCE_TYPE_ = ?                                  AND                                         A.RESOURCE_ID_ =  ? )                             THEN 1                                                                                                                                           WHEN                             EXISTS                               (SELECT                                       ID_                                FROM                                       ACT_RU_AUTHORIZATION A                                WHERE                                       A.TYPE_ = 0                                AND                                       A.USER_ID_ = '*'                                AND                                       BITAND(A.PERMS_,?) = ?                                AND                                       A.RESOURCE_TYPE_ = ?                                AND                                       A.RESOURCE_ID_ = '*')                             THEN 1                                                                                                               ELSE                                                                                     null                                                                      END FROM DUAL                 )                             END FROM DUAL       )     END  FROM DUAL
### Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'CASEWHENEXISTS(SELECTA.RESOURCE_ID_FROMACT_RU_AUTHORIZATIONAWHEREA.TYPE_=:"SYS_B_00"ANDA.USER_ID_=:1ANDBITAND(A.PERMS_,:2)=:3ANDA.RESOURCE_TYPE_=:4ANDA.RESOURCE_ID_=:5)THEN:"SYS_B_01"WHENEXISTS(SELECTID_FROMACT_RU_AUTHORIZATIONAWHEREA.TYPE_=:"SYS_B_02' from result set.  Cause: java.sql.SQLException: ORA-17133: Identifier or literal is invalid.
                at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
                at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
                at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
                at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.lambda$executeSelectList$1(DbSqlSession.java:112)
                at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:350)
                ... 40 more
        Caused by: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'CASEWHENEXISTS(SELECTA.RESOURCE_ID_FROMACT_RU_AUTHORIZATIONAWHEREA.TYPE_=:"SYS_B_00"ANDA.USER_ID_=:1ANDBITAND(A.PERMS_,:2)=:3ANDA.RESOURCE_TYPE_=:4ANDA.RESOURCE_ID_=:5)THEN:"SYS_B_01"WHENEXISTS(SELECTID_FROMACT_RU_AUTHORIZATIONAWHEREA.TYPE_=:"SYS_B_02' from result set.  Cause: java.sql.SQLException: ORA-17133: Identifier or literal is invalid.
                at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:87)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createPrimitiveResultObject(DefaultResultSetHandler.java:755)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:651)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:630)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:397)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
                at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
                at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
                at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
                at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:92)
                at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
                at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
                at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
                at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
                at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
                ... 43 more
        Caused by: java.sql.SQLException: ORA-17133: Identifier or literal is invalid.
                at oracle.jdbc.driver.PhysicalConnection.enquoteIdentifier(PhysicalConnection.java:12263)
                at oracle.jdbc.driver.OracleStatement.enquoteIdentifier(OracleStatement.java:7536)
                at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:4715)
                at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:370)
                at oracle.jdbc.driver.GeneratedResultSet.getInt(GeneratedResultSet.java:608)
                at org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:635)
                at org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:635)
                at org.apache.ibatis.type.IntegerTypeHandler.getNullableResult(IntegerTypeHandler.java:37)
                at org.apache.ibatis.type.IntegerTypeHandler.getNullableResult(IntegerTypeHandler.java:26)
                at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
                ... 59 more

I researched the ORA code (ORA-17133) and it seems to be connected to the usage of bind variables with double-quotation marks in queries. You can learn more about it in this article: Link. As a temporary solution, executing the query alter system set cursor_sharing=force resolves the problem. However, this isn’t a permanent fix.

Interestingly, the cursor_sharing database parameter needs to be set for every user’s initial login. After the first login, the parameter can be reverted to its default value, and subsequent logins work fine. I suspect this behavior is related to how Camunda interacts with the database during the first login.

If you have any insights or suggestions regarding this issue, I’d greatly appreciate your help. Is this a known bug, or could I have made a mistake during the installation process?

Thank you for your time and assistance.

Yana · January 22, 2024, 2:26pm

Hi @oateam,

Welcome to the forum and our community!

Could you please give more details regarding your setup:

Camunda version - I see Camunda 7.20 from the title, is it correct?
Database version and database driver version
How did you created the database schema and tables?

Best,
Yana

oateam · January 31, 2024, 7:44am

Hi @yana,

Thank you very much.

Yes, of course. Here are the answers:

Yes, you are correct. I am trying to install the Camunda community edition version 7.20.0. I downloaded the Tomcat package from this address: Download Camunda 7 | Camunda.
The database is reporting Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.20.0.0.0 as its version information. As for the JDBC driver version, I went to the Oracle official JDBC download page and grabbed a copy of ojdbc8.jar and ojdbc11.jar. I tried both files individually but got the same result.
Basically, I tried to follow the instructions at Install the Database Schema | docs.camunda.org. I created an Oracle schema without any special options and then ran the sql\create\oracle_engine_7.20.0.sql and sql\create\oracle_identity_7.20.0.sql scripts using my trusted IDE of choice for Oracle Databases. Dropping the tables using drop queries and creating them again didn’t change the result.

Here is some additional information that may be useful: JAVA_HOME is set to an installation of JDK version 17 from Oracle. The operating system is Oracle Linux.

I would like to add something to my previous observations:
A couple of days later, I visited the same newly installed server and tried to log in using one of the users I could log in with before, but I was shown the exact same error! So, it seems that nothing was fixed.
I decided to install older versions of Camunda to see if I get a different outcome. I downloaded versions 7.13 and 7.19 and installed them using the exact same method. The same database schema was used, and I made sure to drop all of the tables from the previous installation and create them with each version’s provided script. Both versions turned out to be okay, and I couldn’t reproduce the error.
Then, I installed version 7.20 and, to my surprise, I didn’t encounter any problems. I could log in using multiple users. I played with Oracle Database’s cursor_sharing parameter, setting it to force and exact (the default and recommended value). I even dropped and created the tables multiple times, but I couldn’t reproduce the problem.
Now, I’m confused because I have no clue what the problem was and how it was solved!

If you have any insights or suggestions regarding this issue, I would greatly appreciate your help.

Yana · February 16, 2024, 7:42am

Hi @oateam,

Thank you for sharing your analysis with us.
Without reproducing the behaviour, it’s hard to say what was wrong.
My guess will be something went wrong with the schema creation.