Primary Key violation in ACT_HI_ACTINST

[distantworker]

Hello,

We're having a repeatible PK violation on the ACT_HI_ACTINST table running Activiti-5.11, MySQL embedded in a Spring 3.2 application that uses connection pooling.  I've searched hi and lo and can't find anyone with a similar problem. If we clean out the entire activiti db and run fresh, it's fine for a few minutes but as soon as the ACT_HI_ACTINST table starts getting populated eventually the PK violation hits.

The only thing I've been able to find is this statement "Using different DB schema's on the same datasource can explain why this error occurs. Probabily the ID is retrieved from one datasource, and new variable is written to to other datasource using the wrong id." from frederikheremans.

We do have two distinct process definitions in the db, and I notice that when the error occurs the existing record is from one definition and the attempted insert is from the other

Any ideas?

Best. DW.

  <bean id="dataSourceActiviti" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/activiti?autoReconnect=true" />
    <property name="username" value="activiti" />
    <property name="password" value="secret" />
  </bean>

  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceActiviti" />
  </bean>

  <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
    <property name="dataSource" ref="dataSourceActiviti" />
    <property name="transactionManager" ref="transactionManager" />
    <property name="databaseType" value="mysql" />
    <property name="databaseSchemaUpdate" value="true" />
    <property name="jobExecutorActivate" value="false" />
  </bean>


SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '224' for key 'PRIMARY'
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricActivityInstanceEntity.insertHistoricActivityInstance-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '224' for key 'PRIMARY'] with root cause
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '224' for key 'PRIMARY'
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
   at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
   at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
   at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:29)
   at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:122)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:111)
   at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:632)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:459)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:167)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:114)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:69)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
   at org.activiti.engine.impl.TaskServiceImpl.complete(TaskServiceImpl.java:157)
   at net.pe.spring.controller.ClientController.dashCompleteTaskGet(ClientController.java:1399)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
   at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
   at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
   at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
   at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:920)
   at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:816)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
   at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:801)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
   at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
   at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:119)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:188)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.access.channel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:109)
   at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
   at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
   at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
   at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
   at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
   at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
   at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
   at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
   at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
   at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
   at java.lang.Thread.run(Thread.java:744)

[distantworker]

  <bean id="dataSourceActiviti" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/activiti?autoReconnect=true" />
    <property name="username" value="activiti" />
    <property name="password" value="secret" />
  </bean>

  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceActiviti" />
  </bean>

  <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
    <property name="dataSource" ref="dataSourceActiviti" />
    <property name="transactionManager" ref="transactionManager" />
    <property name="databaseType" value="mysql" />
    <property name="databaseSchemaUpdate" value="true" />
    <property name="jobExecutorActivate" value="false" />
  </bean>

[frederikherema1]

Do you have multiple engines running against the same database?

[distantworker]

Thanks for the response. If multiple engines means multiple instances of our application, the answer is no. We do have two dataSources, one for our application and one for activiti. Our configuration seems fairly straight forward (included below).

Could it be a problem that our application database is using

org.apache.commons.dbcp.BasicDataSource

and activiti is using

org.springframework.jdbc.datasource.SimpleDriverDataSource

?

Best, DW

————— This is the activiti conf ————–
<code>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

  <bean id="dataSourceActiviti" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/activiti?autoReconnect=true" />
    <property name="username" value="activiti" />
    <property name="password" value="secret" />
  </bean>

  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceActiviti" />
  </bean>

  <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
    <property name="dataSource" ref="dataSourceActiviti" />
    <property name="transactionManager" ref="transactionManager" />
    <property name="databaseType" value="mysql" />
    <property name="databaseSchemaUpdate" value="true" />
    <property name="jobExecutorActivate" value="false" />
  </bean>

  <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">
    <property name="processEngineConfiguration" ref="processEngineConfiguration" />
  </bean>

  <bean id="repositoryService" factory-bean="processEngine" factory-method="getRepositoryService" />
  <bean id="runtimeService" factory-bean="processEngine" factory-method="getRuntimeService" />
  <bean id="taskService" factory-bean="processEngine" factory-method="getTaskService" />
  <bean id="historyService" factory-bean="processEngine" factory-method="getHistoryService" />
  <bean id="managementService" factory-bean="processEngine" factory-method="getManagementService" />

</beans>
</code>

————— This is the regular application db conf ————–
<code>
  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/pe?autoReconnect=true" />
    <property name="username" value="projectecon" />
    <property name="password" value="secret" />
  </bean>
</code>

[nicksmith]

@frederikhereman - I have just started seeing this sort of error when we are running multiple engines against the same database in a clustered environment. Could you provide more details in what you were thinking the issue might be in that case?

The weird thing is that we have been running in a clustered environment for almost a year now and this is now happening. We are running Activiti 5.16 and we have been on that version for several months with no issues like this.

[gideono]

Good Morning

Did you ever manage to resolve the above issue?

I seem too be getting the duplicate primary key issue about once a week now and at that point I basically can't add new jobs or complete any tasks on existing jobs.

My first thought was that maybe there are two processes running but I have taken a look at how we launch the process engine in our application and I don't think that is the case. Is there anything else I can look at ?

We call the following method from a servlet on application init and every thing else that wants to access the process engine from then on uses the same method to access it.

<java>
    private static ProcessEngine processEngine = null;

    public static ProcessEngine getProcessEngine() {
       if (processEngine == null) {
            processEngine = ProcessEngineConfiguration.createProcessEngineConfigurationFromResource("activiti.cfg.xml")
                .setProcessEngineName(ProcessEngines.NAME_DEFAULT)
                .buildProcessEngine();
            return processEngine;
       } else {
           return processEngine;
       }
    }
</java>

[distantworker]

It just kind of resolved itself. When we were getting these errors (and they were repeatable) we were in a heavy development cycle, and things were probably messed up in the activiti database in some form or fashion. That's all I can offer.

[sakthi1970]

May I know the primary key is against which table.column(s) ?

[jbarrez]

Not following the questions here … ?

[sakthi1970]

The issue states "Duplicate entry '224' for key 'PRIMARY'" … so the primary key is against which column