Unable to add a user to a group due to a duplication in database
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
This process requires the use of direct database manipulation. As such, this process is not covered under the Atlassian Support Offerings and the information on this page is provided as-is. It should be thoroughly tested in a development or staging environment before implementing any changes in your production instance.
Unable to add a user to a group due to Database error in cwd_membership table which corresponds to violation of uk_mem_parent_child_type constraint.
The Crowd UI shows a System Error and the following error occurs in the atlassian-crowd.log.
1
Caused by: java.sql.BatchUpdateException: Duplicate entry '819491-4128773-GROUP_USER' for key 'cwd_membership.uk_mem_parent_child_type'Environment
5.1.5
Diagnosis
Identify the parent_id and child_id from the error.
Identify the following error snippet from the atlassian-crowd.log:
1
2024-02-01 09:55:46,514 ajp-nio-127.0.0.1-8009-exec-8 ERROR [jdbc.batch.internal.BatchingBatch] HHH000315: Exception executing batch [java.sql.BatchUpdateException: Duplicate entry '820920-4128773-GROUP_USER' for key 'cwd_membership.uk_mem_parent_child_type'], SQL: insert into cwd_membership (parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, created_date, directory_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)We can identify parent_id and child_id from the error snippet. For example, as per '820920-4128773-GROUP_USER' in above snippet: parent_id is 820920 & child_id is 4128773.
Analyse cwd_membership table.
Identify the duplicate entry from cwd_membership table using below query:
1
SELECT * FROM cwd_membership WHERE parent_id = '<replace with parent_id from error snippet>' and child_id = '<replace with child_id from error snippet>';Pleasenote the child_name & lower_child_name from SQL results.
In a problematic scenario, the child_name/lower_child_name will be different from the affected username.
Analyse cwd_user table.
Check the cwd_user table and confirm that affected child_id is the id of affected user.
1
SELECT * FROM cwd_user WHERE id = '<replace with child_id from error snippet>';Please note that the child_id in the cwd_membership table correlates with the id in the cwd_user table.
After analysing the cwd_membership table and the cwd_user table, we can confirm the presence of Database corruption, as the id of affected user is present as the child_id of different user in the cwd_membership table.
Cause
The error is caused by duplicate values in the child_id column of the cwd_membership table corresponding to the affected user. The duplication of values can be a result of Database corruption.
Solution
To resolve the issue we need to update the cwd_membership table with correct child_id value.
Run below query to pull out the affected records where affected user's child_id is associated different user's child_name/lower_child_name:
1
SELECT * FROM cwd_membership WHERE child_id = '<replace with child_id from error snippet>' AND lower_child_name = '<replace with lower_child_name from SQL results of cwd_membership table analysis>'; Identify the correct id of different/incorrect username by running:
1
SELECT id from cwd_user WHERE lower_user_name = '<replace with lower_child_name from SQL results of cwd_membership table analysis>'; Always Production Backup Strategy before removing or modifying any data on it.
Run below query to update the affected records identified earlier:
1
UPDATE cwd_membership SET child_id = '<replace with correct id from previous SQL query result>' WHERE child_id = '<replace with child_id from error snippet>' AND lower_child_name = '<replace with lower_child_name from SQL results of cwd_membership table analysis>'; Once the problematic record is updated in the cwd_membership table, add the affected user to the group and it should be successful.
Was this helpful?