'ERROR: duplicate key value violates unique constraint "uk_mem_parent_child_type"' thrown when renaming user

Summary

JIRA DataAccessException error message was thrown when trying to rename a user

Steps to Reproduce

We are unsure on how to reproduce the problem, but the database was already in this state when we started to investigate.

Expected Results

User is renamed as expected

Actual Results

Error message is thrown:

com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership][id,13011][membershipType,GROUP_USER][lowerParentName,developer][parentId,10017][childId,10721][childName,aaa0000][lowerChildName,aaa0000][directoryId,1][parentName,Developer] (SQL Exception while executing the following:INSERT INTO public.cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "uk_mem_parent_child_type"
  Detail: Key (parent_id, child_id, membership_type)=(10017, 10721, GROUP_USER) already exists.))

Notes

It seems that there are inconsistencies on user_name, lower_user_name in cwd_user compare to the child_name, lower_child_name in cwd_membership. The child_name seems to use ALL CAPITAL alphabet, while in user_name it uses lower case.

cwd_user: aaa0000, aaa0000
cwd_membership: AAA0000, aaa0000

Workaround

1. Backup the database before making any changes!
2. Stop JIRA.
3. First identify any orphaned membership records:

   select * from cwd_membership m left outer join cwd_user u on m.child_id = u.id where u.user_name is null;
   

4. Delete any of the records returned, using the id returned above:

   delete from cwd_user where id in (a, b, c, d)
   

5. And now check for membership records where the usernames are not matching:

   select * from cwd_membership m join cwd_user u on m.child_id = u.id where u.user_name != m.child_name;
   

   These records will need to be updated.

6. Update them with the following:

   update cwd_membership set child_name = cwd_user.user_name from cwd_user where cwd_membership.child_id = cwd_user.id;
   

7. Run the SQL from step 4 to verify the fix has gone through.
8. Start JIRA and check you can succesfully rename the users.