DbCachingDirectoryPoller Error occurred while refreshing the cache for directory

[Love Nyberg]

In Stash, under Administration > User Directories we have a JIRA User Directory of the Atlassian Crowd typ that fails. How can we fix this problem?

{code}

2013-02-19 14:10:09,001 WARN scheduler_Worker-10 o.h.e.jdbc.spi.SqlExceptionHelper SQL Error: 1062, SQLState: 23000

2013-02-19 14:10:09,002 ERROR scheduler_Worker-10 o.h.e.jdbc.spi.SqlExceptionHelper Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_10168_10170'

2013-02-19 14:10:09,002 ERROR scheduler_Worker-10 o.h.e.j.batch.internal.BatchingBatch HHH000315: Exception executing batch Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_101

68_10170'

2013-02-19 14:10:09,030 ERROR scheduler_Worker-10 c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory 229377 .

org.springframework.dao.DataIntegrityViolationException: Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_10168_10170'; SQL n/a ; constraint null ; nested exception is org.hibern

ate.exception.ConstraintViolationException: Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_10168_10170'

at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:159) ~ spring-orm-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.orm.hibernate4.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:606) ~ spring-orm-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:488) ~ spring-orm-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754) ~ spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723) ~ spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:394) ~ spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120) ~ spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~ spring-aop-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) ~ spring-aop-3.1.2.RELEASE.jar:3.1.2.RELEASE

at com.atlassian.crowd.directory.$Proxy841.addUserToGroup(Unknown Source) ~ na:na

at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.addUserToGroup(DirectoryCacheImplUsingChangeOperations.java:187) ~ crowd-core-2.5.3-m1.jar:na

at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseChanges(EventTokenChangedCacheRefresher.java:115) ~ crowd-ldap-2.5.3-m1.jar:na

at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:610) ~ crowd-core-2.5.3-m1.jar:na

at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:63) ~ crowd-core-2.5.3-m1.jar:na

at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:50) ~ crowd-core-2.5.3-m1.jar:na

at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobBean.executeInternal(DirectoryPollerJobBean.java:29) crowd-core-2.5.3-m1.jar:na

at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113) spring-context-support-3.1.2.RELEASE.jar:3.1.2.RELEASE

at org.quartz.core.JobRunShell.run(JobRunShell.java:223) quartz-1.8.6.jar:na

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549) quartz-1.8.6.jar:na

Caused by: org.hibernate.exception.ConstraintViolationException: Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_10168_10170'

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:128) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at $Proxy134.executeBatch(Unknown Source) ~ na:na

at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:110) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:101) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:149) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:162) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:357) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1210) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:399) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:480) ~ spring-orm-3.1.2.RELEASE.jar:3.1.2.RELEASE

... 16 common frames omitted

Caused by: java.sql.BatchUpdateException: Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_10168_10170'

at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469) ~ bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE

at sun.reflect.GeneratedMethodAccessor408.invoke(Unknown Source) ~ na:na

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~ na:1.7.0_11

at java.lang.reflect.Method.invoke(Method.java:601) ~ na:1.7.0_11

at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ~ hibernate-core-4.1.7.Final.jar:4.1.7.Final

... 31 common frames omitted

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '819209-720921-GROUP_USER' for key 'SYS_IDX_SYS_CT_10168_10170'

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~ na:1.7.0_11

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~ na:1.7.0_11

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~ na:1.7.0_11

at java.lang.reflect.Constructor.newInstance(Constructor.java:525) ~ na:1.7.0_11

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.Util.getInstance(Util.java:386) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458) ~ mysql-connector-java-5.1.22-bin.jar:na

at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006) ~ mysql-connector-java-5.1.22-bin.jar:na

... 37 common frames omitted

{code}

[RicardoA · Accepted solution]

Hi Love,

From the stack-trace the following knowledge base article seems to apply:

• Crowd Synchronization Fails with Integrity Constraint Violation

Cheers,
Ricardo

[Francis Vittini]

Hi every one.

I just found this ticket and it exactly the same thing that happened to us.

https://jira.atlassian.com/browse/CWD-3147

As some users mention in that ticket, this workaround has to be done everytime the sync process stops, and this error seems to be fixed in JIRA 5.2 and also in CROWD 2.5.4, 2.6.2, 2.4.9 they say this bug was fixed.

[BernardoA]

Basically the workaround is to disable and create agan such directory that might be in cache.

Bernardo

[Francis Vittini]

Hello,

I have the same problem in one of my JIRA 4.3 instances.

How many times do we have to repeat this workaround in order the sync process complete successfully?

I'm asking because i tried it and it just started to synchronize the first time the new directory was created, after 1 hour when it tried to sync again it failed with the same error:

ERROR ServiceRunner [atlassian.crowd.directory.DbCachingDirectoryPoller] Error occurred while refreshing the cache for directory [ 10100 ].

com.atlassian.crowd.embedded.ofbiz.db.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership][id,110948][membershipType,GROUP_USER][lowerParentName,confluence-dfccdr][parentId,15597][childId,59763][childName,Cso1][lowerChildName,cso1][directoryId,10100][parentName,confluence-DFCCDR] (SQL Exception while executing the following:INSERT INTO pdsupport.cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot insert duplicate key row in object 'pdsupport.cwd_membership' with unique index 'uk_mem_parent_child_type'. The duplicate key value is (15597, 59763, GROUP_USER).))

The problem started when we added a new user to the usergroup "confluence-dfccdr" in Crowd, then when jira tried to sync it rise that error. Now we have the usergroup "confluence-dfccdr" with 18 users in Crowd and in JIRA it only has 17.

Thanks for the feedback.