Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-74171

Synchronisation fails with Active Directory because of duplicate records in cwd_membership table.

XMLWordPrintable

      Issue Summary

      Synchronisation of active directory is failing.

      Steps to Reproduce

      Not reproduced.

      Expected Results

      Synchronisation with Active Directory should complete not fail.

      Actual Results

      Synchronisation fails and below exception is printed in the atlassian-jira.log file:

      2022-08-01 01:16:23,897 Caesium-1-3 INFO ServiceRunner [c.a.crowd.directory.DbCachingRemoteDirectory] failed synchronisation complete for directory [ 10100 ] in [ 78458ms ]
2022-08-01 01:16:23,929 Caesium-1-3 ERROR ServiceRunner [c.a.crowd.directory.DbCachingDirectoryPoller] Error occurred while refreshing the cache for directory [ 10100 ].
java.lang.IllegalArgumentException: Passed List had more than one value.
  at org.ofbiz.core.entity.EntityUtil.getOnly(EntityUtil.java:68)
  at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.removeMembership(OfBizInternalMembershipDao.java:289)
  at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.removeUserFromGroup(OfBizInternalMembershipDao.java:282)
  at com.atlassian.jira.crowd.embedded.ofbiz.OfBizDelegatingMembershipDao.removeUserFromGroup(OfBizDelegatingMembershipDao.java:107)
  at com.atlassian.crowd.directory.AbstractInternalDirectory.removeUserFromGroup(AbstractInternalDirectory.java:857)
  at com.atlassian.crowd.directory.DbCachingRemoteChangeOperations.removeUserMembershipsForGroup(DbCachingRemoteChangeOperations.java:723)
  at com.atlassian.crowd.directory.DirectoryCacheImplUsingChangeOperations.syncUserMembersForGroup(DirectoryCacheImplUsingChangeOperations.java:114)
  at com.atlassian.crowd.directory.synchronisation.cache.AbstractCacheRefresher.synchroniseMemberships(AbstractCacheRefresher.java:211)
  at com.atlassian.crowd.directory.synchronisation.cache.AbstractCacheRefresher.synchroniseAll(AbstractCacheRefresher.java:56)
  at com.atlassian.crowd.directory.synchronisation.cache.UsnChangedCacheRefresher.synchroniseAll(UsnChangedCacheRefresher.java:172)
  at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1095)
  at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.lambda$synchronise$0(DirectorySynchroniserImpl.java:82)
  at com.atlassian.crowd.audit.NoOpAuditLogContext.withAuditLogSource(NoOpAuditLogContext.java:17)
  at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:80)
  at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:48)
  at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobRunner.runJob(DirectoryPollerJobRunner.java:92)
  at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134)
  at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106)
  at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90)
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:435)
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:430)
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:454)
  at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:382)
  at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66)
  at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60)
  at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35)
  at java.lang.Thread.run(Thread.java:748)

      Workaround

      Following updates will remove any inconsistent data. Always have a backup of your database before doing any changes to it.

      Postgres

      Run all 6 queries - 3 updates and 3 deletes.

      update cwd_membership M
set child_id = U.id
from cwd_user U
where M.lower_child_name = U.lower_user_name
and M.directory_id = U.directory_id
and M.membership_type = 'GROUP_USER'
and M.child_id != U.id;

update cwd_membership M
set child_id = G.id
from cwd_group G
where M.lower_child_name = G.lower_group_name
and M.directory_id = G.directory_id
and M.membership_type = 'GROUP_GROUP'
and M.child_id != G.id;

update cwd_membership M
set parent_id = G.id
from cwd_group G
where M.lower_parent_name = G.lower_group_name
and M.directory_id = G.directory_id
and M.parent_id != G.id;

delete from cwd_membership 
where lower_child_name not in
(
  select lower_user_name 
  from cwd_user
)
and membership_type = 'GROUP_USER';

delete from cwd_membership 
where lower_child_name not in
(
  select lower_group_name
  from cwd_group
)
and membership_type = 'GROUP_GROUP';

delete from cwd_membership 
where lower_parent_name not in 
(
  select lower_group_name
  from cwd_group
);

      MySQL

      Run all 6 queries - 3 updates and 3 deletes.

      update
    cwd_membership M,
    cwd_user U
set M.child_id = U.id
where M.lower_child_name = U.lower_user_name
  and M.directory_id = U.directory_id
  and M.membership_type = 'GROUP_USER'
  and M.child_id != U.id;

update 
    cwd_membership M,
    cwd_group G
set M.child_id = G.id
where M.lower_child_name = G.lower_group_name
  and M.directory_id = G.directory_id
  and M.membership_type = 'GROUP_GROUP'
  and M.child_id != G.id;

update 
    cwd_membership M,
    cwd_group G
set M.parent_id = G.id
where M.lower_parent_name = G.lower_group_name
  and M.directory_id = G.directory_id
  and M.parent_id != G.id;

delete from cwd_membership 
where lower_child_name not in
(
  select lower_user_name 
  from cwd_user
)
and membership_type = 'GROUP_USER';

delete from cwd_membership 
where lower_child_name not in
(
  select lower_group_name
  from cwd_group
)
and membership_type = 'GROUP_GROUP';

delete from cwd_membership 
where lower_parent_name not in 
(
  select lower_group_name
  from cwd_group
);

        1. synchronisation-fails.png
          synchronisation-fails.png
          53 kB

              kcichy Kamil Cichy (Inactive)
              ad506bb12b8e Chandra Shekhar Pandey
              Votes:
              11 Vote for this issue
              Watchers:
              22 Start watching this issue

                Created:
                Updated:
                Resolved: