### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER)

[Peter Metz]

Hallo,
ich bekomme folgenden "Fehler"  'Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER) verletzt'.

Kann es sein, dass wenn die User-Task einen Assignee oder Candidate User hat, der nicht konfiguriert ist, dass dann camunda trotzdem Versucht diesen User als Assignee zuzuordnen?
Die User-Aufgabe wird trotzdem generiert, und über den Filter "Alle Aufgaben" ist diese auch sichtbar.

Plattform Tomcat 7.0.62, camunda 7.3, Oracle 11g2, neu installiert.

Danke, Grüße, Peter

[Roman Smirnov]

Hi Peter,

Could you provide the full stacktrace please? How can we reproduce it? Could you provide a testcase?

It seems that the user has already an authorization (with the same type, resource type and resource id).

If an assignee (or a candidate user) is defined, the engine does not check whether the given user exists or not. The engine simply sets the given user as assignee (or as candidate user).

Cheers,

Roman

[Peter Metz]

Hi Roman, here is the full stack trace. I removed the user "demo" in the process-bpmn.xml. User "demo" does not exists (I use "admin").
Now the ERR does not appear again.

Thanks, Best Regards, Peter

Jul 31, 2015 1:06:55 PM org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable run
WARNING: Exception while executing job with id 41099902-3774-11e5-9a0f-6eae8b26ee91
org.camunda.bpm.engine.ProcessEngineException: Exception while executing Database Operation: INSERT AuthorizationEntity[411c84de-3774-11e5-9a0f-6eae8b26ee91]:
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER) verletzt

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.insertAuthorization-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_AUTHORIZATION (       ID_,       TYPE_,       GROUP_ID_,       USER_ID_,       RESOURCE_TYPE_,       RESOURCE_ID_,       PERMS_,       REV_       )     values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       1     )
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER) verletzt

Flush summary:
[
  INSERT HistoricJobLogEventEntity[411c84e1-3774-11e5-9a0f-6eae8b26ee91]
  INSERT AuthorizationEntity[411c84dc-3774-11e5-9a0f-6eae8b26ee91]
  INSERT AuthorizationEntity[411c84de-3774-11e5-9a0f-6eae8b26ee91]
  INSERT AuthorizationEntity[411c84e0-3774-11e5-9a0f-6eae8b26ee91]
  INSERT HistoricTaskInstanceEventEntity[411c84db-3774-11e5-9a0f-6eae8b26ee91]
  INSERT HistoricActivityInstanceEventEntity[turCheckResult:411c84da-3774-11e5-9a0f-6eae8b26ee91]
  INSERT TaskEntity[411c84db-3774-11e5-9a0f-6eae8b26ee91]
  INSERT IdentityLinkEntity[411c84dd-3774-11e5-9a0f-6eae8b26ee91]
  INSERT IdentityLinkEntity[411c84df-3774-11e5-9a0f-6eae8b26ee91]
  DELETE MessageEntity[41099902-3774-11e5-9a0f-6eae8b26ee91]
  DELETE_BULK deleteByteArrayNoRevisionCheck 41130eef-3774-11e5-9a0f-6eae8b26ee91
  UPDATE ExecutionEntity[f7bf8aef-3773-11e5-9a0f-6eae8b26ee91]
]
    at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:279)
    at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:265)
    at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:263)
    at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:192)
    at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:106)
    at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
    at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.executeJob(ExecuteJobsRunnable.java:79)
    at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:67)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER) verletzt

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.insertAuthorization-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_AUTHORIZATION (       ID_,       TYPE_,       GROUP_ID_,       USER_ID_,       RESOURCE_TYPE_,       RESOURCE_ID_,       PERMS_,       REV_       )     values (       ?,       ?,       ?,       ?,       ?,       ?,       ?,       1     )
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER) verletzt

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
    at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeInsertEntity(DbSqlSession.java:136)
    at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.insertEntity(DbSqlSession.java:126)
    at org.camunda.bpm.engine.impl.db.AbstractPersistenceSession.executeDbOperation(AbstractPersistenceSession.java:35)
    at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:276)
    ... 10 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: Unique Constraint (CAMUNDA_PROC.ACT_UNIQ_AUTH_USER) verletzt

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    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:505)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3612)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3713)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
    ... 15 more

[Roman Smirnov]

Hi Peter,

According to the flush summary there will be three authorization inserted and it seems that there some authorizations duplicated for the same resource.

 

Flush summary:
[
  ...

  INSERT AuthorizationEntity[411c84dc-3774-11e5-9a0f-6eae8b26ee91]
  INSERT AuthorizationEntity[411c84de-3774-11e5-9a0f-6eae8b26ee91]
  INSERT AuthorizationEntity[411c84e0-3774-11e5-9a0f-6eae8b26ee91]

  ...

 ]   

How is your user task "configured" inside the bpmn xml? Could you summarize the steps you perform to reproduce it? Maybe you could provide us a simple test [1]?

Cheers,

Roman

[1]: https://github.com/camunda/camunda-engine-unittest

[Roman Smirnov]

Hi Peter,

See the following bug report: https://app.camunda.com/jira/browse/CAM-4369

Cheers,

Roman

[pm...@steelcase.com]

Hi Roman,

thanks for your update.
The user assignment is differently to CAM-4369 not within the code, but inside the bpm definition file.
<bpmn2:userTask id="turCheckResult" camunda:candidateUsers="admin" camunda:assignee="admin" camunda:asyncAfter="true" camunda:exclusive="false" name="Check Result">

The confusing is (but correct), that depending on the settings for async and exclusive it looks like the error occures not at the user task but at the sequence flow or service task before.


I'm happy that there is a CAM-4369, because that means, not my code is affected...

Cheers, Peter

[pm...@steelcase.com]

On Thursday, August 6, 2015 at 10:11:38 AM UTC+2, Roman Smirnov wrote:

Hi Roman,

I created a trigger for tracing all changes to that table ACT_RU_AUTHORIZATION and changed the index ACT_UNIQ_AUTH_USER from unique to non unique.

The conclusion is that for each of the crashed instances there where records always twice for the user task
RESOURCE_ID_ = fa8297b5-50a0-11e5-b45f-6eae8b39be39 :

"COUNT(1)" "TYPE_" "USER_ID_" "RESOURCE_TYPE_" "RESOURCE_ID_"
"2" "1" "admin" "7" "fa8297b5-50a0-11e5-b45f-6eae8b39be39"


Now comes the funny thing :-)

I changed the user assignment this way: assignee is empty and candidate users is "admin".
The duplicates are no more there.

That's a workaround that is ok for me now, I'll track CAM-4369.

Thanks,
Cheers,
Peter

[pm...@steelcase.com]

CAM-4397