Fixing dangling memberships in Crowd
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
Description
Due to how memberships were handled prior to Crowd 2.11.0 (https://jira.atlassian.com/browse/CWD-4801), in some cases incremental synchronisation could leave memberships pointing to items which no longer exist. Those memberships not only take up space, but can lead to problems when new memberships are created for items with the same names, but different ids. This could, for example, happen when a user stops meeting the directory's LDAP filter criteria, is synchronised incrementally, and then starts meeting the LDAP filter criteria again, or when a user deletion and a rename of another user to the deleted user's username are picked up in a single incremental synchronisation. This results in Crowd creating a new user with the same name as the deleted one, but with a different id. This could cause problems with authentication to Crowd and cause user memberships to be shown incorrectly in the UI and results from API calls.
Fix
Dangling memberships can be found by an SQL query like the one below:
1
2
3
4
5
6
7
8
9
10
SELECT mem.*,
(
mem.membership_type = 'GROUP_USER' AND mem.child_id NOT IN (SELECT id FROM cwd_user))
OR (mem.membership_type = 'GROUP_GROUP' AND mem.child_id NOT IN (SELECT id FROM cwd_group)
) AS child_missing,
mem.parent_id NOT IN (SELECT id FROM cwd_group) AS parent_missing
FROM cwd_membership mem
WHERE mem.parent_id NOT IN (SELECT id FROM cwd_group)
OR (mem.membership_type = 'GROUP_USER' AND mem.child_id NOT IN (SELECT id FROM cwd_user))
OR (mem.membership_type = 'GROUP_GROUP' AND mem.child_id NOT IN (SELECT id FROM cwd_group))This query will find membership entries that point to a non-existent parent or have a non-existent child. The found entries should be reviewed and deleted.
Was this helpful?