User directory synchronization fails with Active Directory because of duplicate records in the cwd_membership table.
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
Synchronization of Active Directory is failing.
The following appears in the
atlassian-jira.log:
1
2
3
4
5
6
7
8
9
10
11
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)
[...]Environment
Jira 8.2.x and later, fixed in 9.3.1, 8.20.15
Active Directory connector
Diagnosis
Related bug ticket: JRASERVER-74171 - Synchronisation fails with Active Directory because of duplicate records in cwd_membership table.
Cause
This bug can manifest when users are removed from group memberships in Jira and a directory is synced afterward. The code that removes memberships currently relies on user and group IDs, rather than lowercase usernames, when memberships are being removed. As a result, this causes duplicate memberships to be added to the database.
Solution
Workaround
The following SQL queries will remove any inconsistent data.
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Fix membership table records where the user's ID is incorrect, despite the username/directory matching
UPDATE cwd_membership mem
SET child_id = usr.id
FROM cwd_user usr
WHERE mem.lower_child_name = usr.lower_user_name
AND mem.directory_id = usr.directory_id
AND mem.membership_type = 'GROUP_USER'
AND mem.child_id != usr.id;
-- Fix membership table records where the child group's ID is incorrect, despite the group name/directory matching
UPDATE cwd_membership mem
SET child_id = grp.id
FROM cwd_group grp
WHERE mem.lower_child_name = grp.lower_group_name
AND mem.directory_id = grp.directory_id
AND mem.membership_type = 'GROUP_GROUP'
AND mem.child_id != grp.id;
-- Fix membership table records where the parent group's ID is incorrect, despite the group name/directory matching
UPDATE cwd_membership mem
SET parent_id = grp.id
FROM cwd_group grp
WHERE mem.lower_parent_name = grp.lower_group_name
AND mem.directory_id = grp.directory_id
AND mem.parent_id != grp.id;
-- Delete membership records where the username isn't found in the users table
DELETE
FROM cwd_membership
WHERE lower_child_name NOT IN (SELECT lower_user_name FROM cwd_user)
AND membership_type = 'GROUP_USER';
-- Delete membership records where the child group name isn't found in the groups table
DELETE
FROM cwd_membership
WHERE lower_child_name NOT IN (SELECT lower_group_name FROM cwd_group)
AND membership_type = 'GROUP_GROUP';
-- Delete membership records where the parent group name isn't found in the groups table
DELETE
FROM cwd_membership
WHERE lower_parent_name NOT IN (SELECT lower_group_name FROM cwd_group);Was this helpful?