Resolve Discrepancy in User Listings Between API and Jira Data Center UI
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
In this article, we will explore a scenario in which a discrepancy is observed between the number of users listed under project roles when viewed through the API and the Jira UI.
Environment
Jira DC 9.12.x, 10.x
Diagnosis
Check the UI result
Go to Project -> Project Settings -> Users and Roles
Check the number displayed for the different roles
Or use the following internal API to get roles and associated users for a project
<base-ur>/rest/projectconfig/latest/roles/project-key?pageNumber=1&pageSize=50
Compare with the REST API
This is the REST API role-getProjectRole
curl -su "username:password" <base.url>/rest/api/2/project/project-key/role/<role-id>
Cause
This can be caused by stale entries in the projectroleactor table. This table keeps mapping project roles and users.
There might be entries related to users who have been removed from Jira. Hence, there will be no matching entry in the cwd_user table.
Solution
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.
Find and remove stale entries
Use this query to retrieve information about users who are associated with project roles in a Jira database but don't have corresponding entries in the
cwd_usertable.SELECT distinct a.id, a.user_key, a.lower_user_name, p.pkey, r.id, r.roletype FROM app_user a Inner join projectroleactor r on a.user_key = r.roletypeparameter inner join project p on p.id = r.PID LEFT JOIN cwd_user c ON a.lower_user_name = c.lower_user_name WHERE c.lower_user_name IS NULL;For each user listed, run the following query to delete the stale entry from the
projectroleactor table. Replace<r.id>with the results from the previous queryDELETE FROM public.projectroleactor WHERE ID IN ('<r.id>')
Was this helpful?