The user_key is being displayed in the issue instead of username for Reporter/Assignee
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
Issues previously associated with a username are now showing up with the user_key of the user for Reporter or Assignee field.
Environment
Jira Software v8.x and above.
Diagnosis
If user entries are accidentally deleted from the database for any reason, then the user_key will be displayed in the issues that were assigned to the affected user earlier.
Let's assume that a user with username test has been created. if user entries are deleted from DB, we might see issues with the user_key showing up instead of the username in Reporter or Assignee field. Let's look at an example where we can see that the user_key JIRAUSER11111 being displayed for the tickets that were created by the user with username test.
Solution
We will create a new user similar to the test and replace his user key with JIRAUSER11111 in the database. After which in the ticket view, the user key JIRAUSER11111 will be replaced with the new username.As a final step, we will anonymise the new user and transfer his ownership to the old user's test.
Alert
Please perform the below SQL updates in your test environment first before trying in production. Always take a backup of your database before making any changes
Create new user:
Create a new user XYZ similar to the old user (including all the permissions and groups associated).
Perform the select query and take a backup for the newly created user.
Backup the created users:
Launch the following queries over the Jira database:
1
2
3
4
5
select id from cwd_user where user_name like 'Newly created user which is XYZ here'; => Please take a note of this id
select * from cwd_user_attributes where user_id = 'Newly created user ID'; => Please Refer the above query
select * from cwd_user where user_name like 'Newly created user';
select * from cwd_membership where child_name like 'Newly created user';
select * from app_user where lower_user_name like 'Newly created user';Swapping the users:
Swap the user key for the newly created user with JIRAUSER11111 using the below steps. You will need use the id and user_key from the previous backup step:
Launch the following queries over the Jira database:
1
2
3
4
5
update app_user set id = '11111' where lower_user_name like 'Newly created user';
update app_user set user_key = 'JIRAUSER11111' where lower_user_name like 'Newly created user';
update cwd_membership set child_id = '11111' where child_name like 'Newly created user';
update cwd_user set id = '11111' where user_name like 'Newly created user';
update cwd_user_attributes set user_id = '11111' where user_id = 'Please replace id that you got from the first select query shared'(select id from cwd_user where user_name like 'Newly created user'; ) ;Anonymising the original user:
After Performing the above steps, please check in affected Jira issues if the JIRAUSER11111 has been replaced with the new user. If it shows fine, then please perform the anonymising from the newly created user to the old user by following this article:
This will change ownership from the new user to the old user.
Was this helpful?