User Directory Stops Syncing with ERROR: duplicate key value violates unique constraint cwd_unique_user_membership
症状
At some point the User Directory in Confluence stops synchronising. There is a 'Synchronisation Failed' error in the admin interface:
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.
再現手順
- 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
原因
Might be related to CONFSERVER-28194 - Confluence attempts to apply group memberships when synchronising from a remote directory even if they already exist, causing a duplicate key violation or CWD-3147 - If a user exists in two directories in an application, and is added to a group in one directory that it was already in the other, clients get duplicate key errors.
回避策
- 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.