External user directory sync fails due to violation of unique constraint uk_mem_dir_parent_child
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
All of a sudden, synchronisation to an external user directory starts failing and the following exception is written in atlassian-bitbucket.log:
2019-09-12 00:35:17,792 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 1867777 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:100)
at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:59)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:450)
at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:374)
at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1514)
at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1526)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1504)
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.renameUserRelationships(MembershipDAOHibernate.java:277)
at com.atlassian.crowd.dao.user.UserDAOHibernate.rename(UserDAOHibernate.java:340)
at com.atlassian.stash.internal.crowd.HibernateUserDao.rename(HibernateUserDao.java:249)
at com.atlassian.stash.internal.crowd.HibernateUserDao.rename(HibernateUserDao.java:72)
at com.atlassian.crowd.directory.AbstractInternalDirectory.forceRenameUser(AbstractInternalDirectory.java:583)
at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.updateUsers(DbCachingRemoteChangeOperations.java:238)
at com.atlassian.crowd.directory.$Proxy364.updateUsers(Unknown Source)
at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.addOrUpdateCachedUsers(DirectoryCacheImplUsingChangeOperations.java:58)
at com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresher.synchroniseAllUsers(UsnChangedCacheRefresher.java:181)
at com.atlassian.crowd.directory.ldap.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:46)
at com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresher.synchroniseAll(UsnChangedCacheRefresher.java:161)
at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:977)
at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:71)
at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:45)
at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobRunner.runJob(DirectoryPollerJobRunner.java:85)
at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153)
at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118)
at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:443)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:438)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:462)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:390)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:285)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:282)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:65)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:59)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:34)
at java.lang.Thread.run(Thread.java:748)
... 51 frames trimmed
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
Detail: Key (directory_id, lower_child_name, lower_parent_name, membership_type)=(1867777, some_user, some_group, GROUP_USER) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
... 36 common frames omittedDiagnosis
Environment
- Even though the stack trace above mentions PostgreSQL, the error happens with any database type
- Any of the supported External User Directories may be in use
Cause
The problem happens when, for some unknown reason thus far (most likely related to cache though), Bitbucket Server tries inserting an entry that already exists in cwd_membership database table.
Resolution
- Log in to Bitbucket Server as a System Administrator;
- Go to the Administration area and locate the User Directories configuration;
- Disable the external user directory that has been failing to sync;
- Add a new connection to the same user directory that has just been disabled;
- Users, groups and memberships should be synced;
- Delete the connection to the old user directory that has been disabled.
Doing that will create a new cache and will also insert new entries in the database, preventing any potential "duplicate key" entries, and associating them with existing data from pull requests, etc.
Last modified on Feb 9, 2022
Powered by Confluence and Scroll Viewport.