User Directory Stops Syncing with ERROR: duplicate key value violates unique constraint cwd_unique_user_membership
Symptoms
At some point the User Directory in Confluence stops synchronising. There is a 'Synchronisation Failed' error in the admin interface:
The following appears in the atlassian-confluence.log:
2013-02-06 15:12:39,417 ERROR [scheduler_Worker-8] [atlassian.crowd.directory.DbCachingDirectoryPoller] pollChanges Error occurred while refreshing the cache for directory [ 98306 ].
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.crowd.embedded.hibernate2.HibernateMembership#294922]; SQL []; ERROR: duplicate key value violates unique constraint "cwd_unique_user_membership"
Detail: Key (parent_id, child_user_id)=(163847, 229380) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "cwd_unique_user_membership"
Detail: Key (parent_id, child_user_id)=(163847, 229380) already exists.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:619)
at org.springframework.orm.hibernate.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:605)
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:518)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:321)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at com.atlassian.crowd.directory.$Proxy1388.addUserToGroup(Unknown Source)
at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.addUserToGroup(DirectoryCacheImplUsingChangeOperations.java:187)
at com.atlassian.crowd.directory.ldap.cache.EventTokenChangedCacheRefresher.synchroniseChanges(EventTokenChangedCacheRefresher.java:115)
at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:610)
at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:63)
at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:50)
at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobBean.executeInternal(DirectoryPollerJobBean.java:29)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
at com.atlassian.confluence.schedule.quartz.ConfluenceQuartzThreadPool$1.run(ConfluenceQuartzThreadPool.java:20)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "cwd_unique_user_membership"
Detail: Key (parent_id, child_user_id)=(163847, 229380) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:462)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2464)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2450)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2407)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2276)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:510)
... 17 more
The tested case was JIRA 5.2.4 as a User Directory, with Confluence 4.3.7 as the 'client'. This error may occur in numerous other configurations.
Steps to Reproduce
- Set up Confluence and JIRA
- Add Confluence as an application in JIRA's User Server configuration
- Add the directory to Confluence. Select Read/Write in the options.
- Add a user to JIRA
- Sync the user to Confluence
- In Confluence, add the user to another group
- Sync with JIRA
Cause
Might be related to - CONF-28194Getting issue details... STATUS or - CWD-3147Getting issue details... STATUS
Workaround
- Log in with an internal administrator account
- Edit the properties of the directory
- Set it to Read Only
- Save and sync
- Edit the directory again and set it back to Read/Write
- Sync again
- Observe that the group membership has synced to JIRA, and there are no more failures
If this doesn't work, you can disable the current directory and set up another one, after making the user's membership changes in JIRA if required.