Duplicate variables allowed in ACT_RU_VARIABLE and FK constraint error

1,594 views

Skip to first unread message

Galen Hollins

unread,

Nov 26, 2013, 8:22:31 PM11/26/13

to camunda...@googlegroups.com

Hi,

I'm running into a problem that I think can be summed up like this:

1) Duplicate variables (duplicate name_, type_, execution_id_ and proc_inst_id_) are allowed to be created in the ACT_RU_VARIABLES table.

They have different "REV_" column values though.

2) At the end of the process when it goes to delete from the ACT_RU_EXECUTION table, there is a foreign key error (see stacktrace below).

When I keep the rest of the code the same and the only change I make is to use different variables names, I don't see any issues.

I haven't dug into this a lot, but I think this may be related/similar to:

http://jira.codehaus.org/browse/ACT-1082

Also, looking at the table structure, it looks like the foreign key constraints aren't in the latest Activiti tables.

The overall symptom I see is that it keeps retrying every 5 minutes, but the process never gets closed (due to the FK error, a new entry just keeps getting inserted into the jobs table).

Thanks,

Galen

EXAMPLE STATE OF TABLES (at end of process)

mysql> select * from act_ru_variable;

+-----+------+--------+-------+---------------+---------------+----------+---------------+---------+-------+---------------+--------+

+-----+------+--------+-------+---------------+---------------+----------+---------------+---------+-------+---------------+--------+

| 39 | 1 | string | foo | 30 | 30 | NULL | NULL | NULL | NULL | 1385514449526 | NULL |

| 40 | 2 | string | foo | 30 | 30 | NULL | NULL | NULL | NULL | 1385514461612 | NULL |

+-----+------+--------+-------+---------------+---------------+----------+---------------+---------+-------+---------------+--------+

2 rows in set (0.00 sec)

mysql> select * from act_ru_execution;

+-----+------+---------------+---------------+------------+--------------------------+-------------+-------------------+----------------------+------------+----------------+-----------+-----------------+-------------------+-------------------+

| 30 | 2 | 30 | NULL | NULL | cam_par_variables_1:1:27 | NULL | ParallelGateway_1 | 30 | 0 | 0 | 1 | 0 | 1 | 0 |

| 33 | 4 | 30 | NULL | 30 | cam_par_variables_1:1:27 | NULL | ParallelGateway_2 | ParallelGateway_2:47 | 0 | 1 | 0 | 0 | 1 | 11 |

| 34 | 2 | 30 | NULL | 30 | cam_par_variables_1:1:27 | NULL | ServiceTask_4 | NULL | 1 | 1 | 0 | 0 | 1 | 15 |

3 rows in set (0.00 sec)

Nov 26, 2013 4:48:22 PM org.camunda.bpm.engine.impl.interceptor.CommandContext close

SEVERE: Error while closing command context

org.apache.ibatis.exceptions.PersistenceException:

### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`camunda`.`act_ru_variable`, CONSTRAINT `ACT_FK_VAR_EXE` FOREIGN KEY (`EXECUTION_ID_`) REFERENCES `ACT_RU_EXECUTION` (`ID_`))

### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline

### The error occurred while setting parameters

### SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`camunda`.`act_ru_variable`, CONSTRAINT `ACT_FK_VAR_EXE` FOREIGN KEY (`EXECUTION_ID_`) REFERENCES `ACT_RU_EXECUTION` (`ID_`))

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)

at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158)

at org.camunda.bpm.engine.impl.db.DbSqlSession$DeletePersistentObjectOperation.execute(DbSqlSession.java:264)

at org.camunda.bpm.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:735)

at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:502)

at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:211)

at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:154)

at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)

at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)

at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)

at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)

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$Worker.runTask(ThreadPoolExecutor.java:895)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)

at java.lang.Thread.run(Thread.java:695)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`camunda`.`act_ru_variable`, CONSTRAINT `ACT_FK_VAR_EXE` FOREIGN KEY (`EXECUTION_ID_`) REFERENCES `ACT_RU_EXECUTION` (`ID_`))

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.Util.getInstance(Util.java:386)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)

... 16 more

Daniel Meyer

unread,

Nov 27, 2013, 8:47:13 AM11/27/13

to camunda...@googlegroups.com

Hi Galen,

Tanks a lot for reporting this!

I was able to reproduce a problem with concurrent inserts of a variable with the same name.

https://github.com/camunda/camunda-bpm-platform/commit/6fffa99cbfe91204f402174349a8f1c87347efe1

Can you confirm that this is the same situation which caused your problems?

I created a JIRA as well: https://app.camunda.com/jira/browse/CAM-1574

Cheers,

Daniel Meyer

Galen Hollins

unread,

Dec 2, 2013, 6:36:08 PM12/2/13

to camunda...@googlegroups.com

Hi Daniel,

Yes, I get the same exception when I run my code, that your code gets, with the exception that my code also gets a "WARNING: Execption while executing job with id 112" error, but this may be due to the way my test is setup..

My Code:

SEVERE: Error while closing command context

org.camunda.bpm.engine.OptimisticLockingException: VariableInstanceEntity[111] was updated by another transaction concurrently

at org.camunda.bpm.engine.impl.db.DbSqlSession.flushUpdates(DbSqlSession.java:704)

at org.camunda.bpm.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:500)