[CAM-1541] PersistenceException when logging into camunda webapp (Oracle+Websphere)

Type: Bug Report
Resolution: Fixed
Priority: L3 - Default
Fix Version/s: 7.1.0, 7.0.5, 7.1.0-alpha1
Affects Version/s: None
Component/s: cockpit, engine
Labels:
None

Environment:
WAS 8.0.5, Java 6, IBM J9 2.6, AIX 6.1
Oracle 11g, AIX 6.1, ojdbc6

org.apache.ibatis.exceptions.PersistenceException:

1. 1. Error querying database. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #1 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111
  2. The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Authorization.xml
  3. The error may involve defaultParameterMap
  4. The error occurred while setting parameters
  5. SQL: SELECT CASE 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 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 WHEN EXISTS (SELECT ID_ from ACT_RU_AUTHORIZATION A WHERE A.TYPE_ = 2 AND A.USER_ID_ = ? AND BITAND(A.PERMS_,?)Unable to render embedded object: File (=? AND A.RESOURCE_TYPE_ = ? AND A.RESOURCE_ID_ = ? ) THEN 0 WHEN EXISTS (SELECT ID_ from ACT_RU_AUTHORIZATION A WHERE A.TYPE_ = 2 AND A.USER_ID_ = ? AND BITAND(A.PERMS_,?)) not found.=? AND A.RESOURCE_TYPE_ = ? AND A.RESOURCE_ID_ = '') THEN 0 ELSE ( SELECT CASE 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 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 WHEN EXISTS (SELECT ID_ from ACT_RU_AUTHORIZATION A WHERE A.TYPE_ = 0 AND A.USER_ID_ = '' AND BITAND(A.PERMS_,?)Unable to render embedded object: File (=? AND A.RESOURCE_TYPE_ = ? AND A.RESOURCE_ID_ = ? ) THEN 0 WHEN EXISTS (SELECT ID_ from ACT_RU_AUTHORIZATION A WHERE A.TYPE_ = 0 AND A.USER_ID_ = '*' AND BITAND(A.PERMS_,?)) not found.=? AND A.RESOURCE_TYPE_ = ? AND A.RESOURCE_ID_ = '*') THEN 0 ELSE 0 END FROM DUAL ) END FROM DUAL
  6. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #1 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
    at org.camunda.bpm.engine.impl.db.DbSqlSession.selectBoolean(DbSqlSession.java:345)
    at org.camunda.bpm.engine.impl.persistence.entity.AuthorizationManager.isAuthorized(AuthorizationManager.java:117)
    at org.camunda.bpm.engine.impl.persistence.entity.AuthorizationManager.checkAuthorization(AuthorizationManager.java:100)
    at org.camunda.bpm.engine.impl.identity.db.DbReadOnlyIdentityServiceProvider.checkAuthorization(DbReadOnlyIdentityServiceProvider.java:118)
    at org.camunda.bpm.engine.impl.identity.db.DbReadOnlyIdentityServiceProvider.findUserById(DbReadOnlyIdentityServiceProvider.java:46)
    at org.camunda.bpm.engine.impl.identity.db.DbReadOnlyIdentityServiceProvider.checkPassword(DbReadOnlyIdentityServiceProvider.java:69)
    at org.camunda.bpm.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:37)
    at org.camunda.bpm.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:24)
    at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
    at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
    at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)

This is the controller panel for Smart Panels app

Daniel Meyer added a comment - 21/Nov/13 8:58 AM - edited

First of all, I can reproduce this exception with a unit test. If I do

authorizationService.isUserAuthorized(null, null, UPDATE, resource1)

It succeeds on H2 but fails on oracle with the above mentioned exception. The reason is that the oracle driver does not map the Null value for USER_ID_ to the SQL type Varchar. Other drivers seem to do that.

To fix this, we can provide a hint in mybatis mapping xml that the parameter is of type VARCHAR:

A.USER_ID_ = #

{authUserId, jdbcType=VARCHAR}

as opposed to

A.USER_ID_ = #

{authUserId}

Daniel Meyer added a comment - 21/Nov/13 8:58 AM - edited First of all, I can reproduce this exception with a unit test. If I do authorizationService.isUserAuthorized(null, null, UPDATE, resource1) It succeeds on H2 but fails on oracle with the above mentioned exception. The reason is that the oracle driver does not map the Null value for USER_ID_ to the SQL type Varchar. Other drivers seem to do that. To fix this, we can provide a hint in mybatis mapping xml that the parameter is of type VARCHAR: A.USER_ID_ = # {authUserId, jdbcType=VARCHAR} as opposed to A.USER_ID_ = # {authUserId}

kelrein marteina added a comment - 28/Oct/19 2:03 PM - edited

Hi,

I would suspect that the DB connection has been closed, however the connection is still in the connection pool. You could look at the Tomcat datasource config. https://mycoles.onl/ Consider setting;

testOnBorrow to true
validationQuery to select 1

In this case, the connection pool won’t hand out a closed connection, it should create new valida connections.

kelrein marteina added a comment - 28/Oct/19 2:03 PM - edited Hi, I would suspect that the DB connection has been closed, however the connection is still in the connection pool. You could look at the Tomcat datasource config. https://mycoles.onl/ Consider setting; testOnBorrow to true validationQuery to select 1 In this case, the connection pool won’t hand out a closed connection, it should create new valida connections.

Details

Description

mgm-controller-panel

This is the controller panel for Smart Panels app

Attachments

Activity

Collapse comment: Daniel Meyer added a comment - 21/Nov/13 8:58 AM, Edited by Daniel Meyer - 21/Nov/13 9:01 AM

Expand comment: Daniel Meyer added a comment - 21/Nov/13 8:58 AM, Edited by Daniel Meyer - 21/Nov/13 9:01 AM

Collapse comment: kelrein marteina added a comment - 28/Oct/19 2:03 PM, Edited by kelrein marteina - 28/Oct/19 2:04 PM

Expand comment: kelrein marteina added a comment - 28/Oct/19 2:03 PM, Edited by kelrein marteina - 28/Oct/19 2:04 PM

People

Dates

Salesforce