IMPORTANT: JAC is a Public system and anyone on the internet will be able to view the data in the created JAC tickets. Please don’t include Customer or Sensitive data in the JAC ticket.
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.

      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
      );

          Form Name

            Loading...
            IMPORTANT: JAC is a Public system and anyone on the internet will be able to view the data in the created JAC tickets. Please don’t include Customer or Sensitive data in the JAC ticket.
            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.

                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
                );

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

                          Created:
                          Updated:
                          Resolved:

                            kcichy Kamil Cichy (Inactive)
                            ad506bb12b8e Chandra Shekhar Pandey
                            Affected customers:
                            11 This affects my team
                            Watchers:
                            22 Start watching this issue

                              Created:
                              Updated:
                              Resolved: