Cannot remove user(s) from certain group membership(s)
Symptoms
During the removal of a certain group(s) in JIRA user(s), JIRA would not process the command and act like there are no process done as the group membership is not removed. There are no stack traces thrown by JIRA during the whole process.
- In order to confirm that this is the issue, please try to remove another groups on that particular user and see if it is successful.
- This issue usually appear on a deleted user_name(s) that re-added into JIRA.
Cause
- Jira did not completely delete all of the necessary user rows on
cwd_membership.
See the relevant bug report regarding this issue: - JRA-25611Getting issue details... STATUS - The user is associated with the incorrect group's ID in the
cwd_membership
table.
Diagnosis and Resolution
Make sure that you shut down JIRA before proceeding.
Cause 1:
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
- Identify the problematic user_name
Run the following SQL query against JIRA database
SELECT ID,child_id,parent_name,child_name,directory_id FROM cwd_membership WHERE lower_child_name='<problematic user_name>';
The query will list down all of the membership under the problematic
user_name
- From the result of the above steps, ensure there is a different
child_id
with the samedirectory_id
before proceed to the next steps. If there are no multiplechild_id
, this is not the issue Remove the problematic row(s) by executing this SQL query against JIRA database
DELETE FROM cwd_membership WHERE child_name = '<problematic user_name>' AND child_id NOT IN (SELECT id FROM cwd_user WHERE user_name = '<problematic user_name>')
The above command would delete all rows relevant to the offending
child_id
value which comes from the undeleted rows- Restart JIRA
Cause 2 :
- Identify the problematic group_name and user_name.
Run the following SQL Query on the JIRA Database:
SELECT id,group_name,active,directory_id FROM cwd_group where group_name='<problematic group_name>';
This will list the information of the group details for each directory.
With the result from the above query, take note of the
id
anddirectory_id
as it will be needed later.Next, Run the following SQL query against JIRA Database:
SELECT ID,parent_id,child_id,parent_name,child_name,directory_id FROM cwd_membership WHERE child_name='<problematic user_name>';
The query will list down all of the membership under the problematic
user_name.
Compare the
parent_id
anddirectory_id
values return from the second SQL query against the values ofid
anddirectory_id
return in the first SQL query. If the value is exactly the same, this is not the issue.If the value of
parent_id(cwd_membership)
andid(cwd_group)
have the same value but it is not reflecting thedirectory_id
value this means it is mismapped.Example: In table
cwd_group
the group withid
= '10000' is associated todirectory_id
= '1'. But incwd_membership
for that problematic username, theparent_id
= '10000' is associated todirectory_id
= '2'.Run this select and save the results:
SELECT * FROM cwd_membership WHERE directory_id = '<mismapped value>' AND parent_id NOT IN (SELECT id FROM cwd_group WHERE directory_id = '<mismapped value>');
Remove the problematic row(s) by executing this SQL query against JIRA database:
If groups in the output from 7 are related to application access, be mindful it may cause users to lose application access.DELETE FROM cwd_membership WHERE directory_id = '<mismapped value>' AND parent_id NOT IN (SELECT id FROM cwd_group WHERE directory_id = '<mismapped value>');
The above command would delete all groups that have the mismapped directory_id.
Restart JIRA.