After renaming usernames in JIRA 6.1.4, worklogs do not expand author correctly in REST API

Sempla IT January 13, 2014

Hi all,

Recently we had to change the usernames of our user directory, so I upgraded JIRA from 6.0 to 6.1.4 to get the new feature.

To put it briefly, I have set up and run a script that invoked the JIRA 6.1.4 REST API and changed the usernames from format A to format B.

Everything worked correctly and the JIRA web UI is OK, but now we have a huge problem with our integration scripts because the REST API has started expanding the worklogs author incorrectly.

For instance, the following REST resources:

http(s)://jiraEndpointUrl/rest/api/2/issue/{issueKey}

http(s)://jiraEndpointUrl/rest/api/2/issue/{issueKey}/worklog

http(s)://jiraEndpointUrl/rest/api/2/search?jql=issueKey={issueKey}&fields=key,worklog,subtasks

are now expanding worklog authors as follows:

{"name":"username in format A","active":false}

which is wrong and unexpected.

I did some investigation on the database.

- Assumption (correct me if I'm wrong): The app_user table contains the mapping between user keys and lowercase user names and should be the source of information in JIRA to find a user by user key or vice versa. This is particularly true since there are unique constraints for each of the fields.

- Looking at the table data after the renaming, the app_user table has a somewhat mixed situation (maybe because our JIRA was upgraded since 4.4 to 5.0, 6.0 and now to 6.1) almost all entries have the structure (id=progressive number, user_key=old username in format A, lower_user_name=new username in format B); only a few entries have user_key matched to the id and not to a username.

- Now, the worklog table has the author column populated with username in format A, so matching the user_key as I expect, and the same holds for jiraissue, project, etc... everything in the database relates to user_key

It seems clear to me that there is a bug in the REST API implementation of the worklog object expansion, which seems to be unaware of the user_key attribute. Seems like it's not looking up the app_user table at all.

Another "proof" comes from the JSON response of http(s)://jiraEndpointUrl/rest/api/2/issue/{issueKey}, in which the users related to the issue object are expanded correctly (reporter, assignee) but the users expanded in the worklogs are not.

Are there fixes available for that? I searched but I didn't find any reference to this problem.

If I had to perform a query, can someone give me a hint on how many tables do I have to change?

Thanks in advance.

4 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

3 votes
Answer accepted
Sempla IT January 16, 2014

OK, I fixed the database by myself (thank you guys, you helped me a lot...) and now JIRA and its REST API is working properly again.

The MySQL procedure follows. Can you at least tell me if I forgot some dependent table?

Cheers,

Marco.

1) Copy table app_user to app_user_bk

create table app_user_bk like app_user;
insert app_user_bk select * from app_user;
 

2) Update app_user

# app_user
update app_user set user_key=lower_user_name;


3) Update the dependencies:

# issues:
update jiraissue i join app_user_bk b on i.reporter=b.user_key set i.reporter=b.lower_user_name; 
update jiraissue i join app_user_bk b on i.assignee=b.user_key set i.assignee=b.lower_user_name; 

# actions on issues:
update jiraaction a join app_user_bk b on a.author=b.user_key set a.author=b.lower_user_name; 
update jiraaction a join app_user_bk b on a.updateauthor=b.user_key set a.updateauthor=b.lower_user_name; 

# change groups:
update changegroup c join app_user_bk b on c.author=b.user_key set c.author=b.lower_user_name; 

# history steps:
update OS_HISTORYSTEP h join app_user_bk b on h.caller=b.user_key set h.caller=b.lower_user_name; 

# projects: 
update project p join app_user_bk b on p.lead=b.user_key set p.lead=b.lower_user_name;

# project roles: 
update projectroleactor a join app_user_bk b on a.roletypeparameter=b.user_key set a.roletypeparameter=b.lower_user_name; 

# components: 
update component c join app_user_bk b on c.lead=b.user_key set c.lead=b.lower_user_name; 

# worklogs: 
update worklog w join app_user_bk b on w.author=b.user_key set w.author=b.lower_user_name; 
update worklog w join app_user_bk b on w.updateauthor=b.user_key set w.updateauthor=b.lower_user_name; 

# userassociation (i.e. watches for tickets, etc..)
update userassociation a join app_user_bk b on a.source_name=b.user_key set a.source_name=b.lower_user_name; 

# userhistoryitem
update userhistoryitem u join app_user_bk b on u.username=b.user_key set u.username=b.lower_user_name; 

# favouriteassociations: 
update favouriteassociations f join app_user_bk b on f.username=b.user_key set f.username=b.lower_user_name; 

# filtersubscription:
update filtersubscription f join app_user_bk b on f.username=b.user_key set f.username=b.lower_user_name; 

# activity stream:
update AO_563AEE_ACTIVITY_ENTITY a join app_user_bk b on a.username=b.user_key set a.username=b.lower_user_name; 
update AO_563AEE_ACTIVITY_ENTITY a join app_user_bk b on a.poster=b.user_key set a.poster=b.lower_user_name; 

# avatar:
update avatar a join app_user_bk b on a.owner=b.user_key set a.owner=b.lower_user_name; 

# Search filters:
update searchrequest s join app_user_bk b on s.username=b.user_key set s.username=b.lower_user_name; 
update searchrequest s join app_user_bk b on s.authorname=b.user_key set s.authorname=b.lower_user_name;

# Dashboards:
update portalpage s join app_user_bk b on s.username=b.user_key set s.username=b.lower_user_name;

 

4) Restart JIRA and reindex immediately.


DJX
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 26, 2014

To make this a better answer it should also include the history item update from the query below. Otherwise, this is great!

update changeitem s join app_user_bk b on s.OLDVALUE=b.user_key set s.OLDVALUE=b.lower_user_name;
update changeitem s join app_user_bk b on s.NEWVALUE=b.user_key set s.NEWVALUE=b.lower_user_name;

Kiran Sanipai April 6, 2016

How about the following tables.

1.AO_4AEACD_WEBHOOK_DAO (LAST_UPDATED_USER)

2.AO_60DB71_AUDITENTRY (USER)

3.AO_60DB71_BOARDADMINS (KEY)

4.AO_60DB71_RAPIDVIEW (OWNER_USER_NAME)

5.AO_E8B6CC_CHANGESET_MAPPING (AUTHOR)

6.columnlayout (USERNAME)

7.fileattachment (AUTHOR)

8.remembermetoken (USERNAME).

Like Olivier Voortman likes this
1 vote
Martin Cleaver
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 30, 2015

If you want to see if you might be affected, see if you have renamed any users:

select * from app_user where user_key <> lower_user_name;
1 vote
Denis Shvedchenko February 3, 2014

Here is postgress variant of script with addition of tables : chageitem, remembermetoken, userhistoryitem

Also included TempoPlugin related tables

drop table if exists app_user_bk;
create temp table app_user_bk as select * from app_user WHERE user_key = 'RENAMED USER';


-- Tempo realted
UPDATE public."AO_013613_HD_SCHEME_MEMBER" AS i SET "USER_KEY" = b.lower_user_name
from app_user_bk AS b 
where i."USER_KEY" = b.user_key
;
UPDATE public."AO_013613_WL_SCHEME_MEMBER" AS i SET "MEMBER_KEY" = b.lower_user_name
from app_user_bk AS b 
where i."MEMBER_KEY" = b.user_key;

UPDATE public."AO_AEFED0_TEAM_MEMBER" AS i SET "MEMBER_KEY" = b.lower_user_name
from app_user_bk AS b 
where i."MEMBER_KEY" = b.user_key;
-- Tempo related End


UPDATE  public.app_user s SET  user_key = b.lower_user_name
from app_user_bk AS b where s.user_key = b.user_key;

  
  
update public.jiraissue i set reporter=b.lower_user_name
	from app_user_bk b where i.reporter=b.user_key ;
update public.jiraissue i  set assignee=b.lower_user_name
	from app_user_bk b where i.assignee=b.user_key; 
 
update public.jiraaction a set author=b.lower_user_name
	from app_user_bk b where a.author=b.user_key; 
update public.jiraaction a set updateauthor=b.lower_user_name
	from app_user_bk b where a.updateauthor=b.user_key; 
 
update public.changegroup c set author=b.lower_user_name
	from app_user_bk b where c.author=b.user_key; 
    

update public.changeitem c set oldvalue=b.lower_user_name
	from app_user_bk b where c.oldvalue=b.user_key; 
update public.changeitem c set newvalue=b.lower_user_name
	from app_user_bk b where c.newvalue=b.user_key; 
    
 
update public.OS_HISTORYSTEP h set caller=b.lower_user_name
	from app_user_bk b where h.caller=b.user_key ; 
 
update public.project p set lead=b.lower_user_name
	from app_user_bk b where p.lead=b.user_key ;
 
update public.projectroleactor a set roletypeparameter=b.lower_user_name
	from app_user_bk b where a.roletypeparameter=b.user_key ; 
 
update public.component c set lead=b.lower_user_name
	from app_user_bk b where c.lead=b.user_key ; 
 
update public.worklog w set author=b.lower_user_name
	from app_user_bk b where w.author=b.user_key ; 
update public.worklog w set updateauthor=b.lower_user_name
	from app_user_bk b where w.updateauthor=b.user_key ; 
 
update public.userassociation a set source_name=b.lower_user_name
	from app_user_bk b where a.source_name=b.user_key ; 
 
update public.userhistoryitem u set username=b.lower_user_name
	from app_user_bk b where u.username=b.user_key ; 
 
update public.favouriteassociations f set username=b.lower_user_name
	from app_user_bk b where f.username=b.user_key ; 
 
update public.filtersubscription f set username=b.lower_user_name
	from app_user_bk b where f.username=b.user_key ; 
 
update public."AO_563AEE_ACTIVITY_ENTITY" a set "USERNAME" =b.lower_user_name
	from app_user_bk b where a."USERNAME"=b.user_key ; 
update public."AO_563AEE_ACTIVITY_ENTITY" a set "POSTER"=b.lower_user_name
	from app_user_bk b where a."POSTER" =b.user_key ; 
 
update public.avatar a set owner=b.lower_user_name
	from app_user_bk b where a.owner=b.user_key ; 
 
update public.searchrequest s set username=b.lower_user_name
	from app_user_bk b where s.username=b.user_key ; 
update public.searchrequest s set authorname=b.lower_user_name
	from app_user_bk b where s.authorname=b.user_key ;
 
update public.portalpage s set username=b.lower_user_name
	from app_user_bk b where s.username=b.user_key ; 

update public.remembermetoken s set username=b.lower_user_name
	from app_user_bk b where s.username=b.user_key ; 


update public.userhistoryitem s set username=b.lower_user_name
	from app_user_bk b where s.username=b.user_key ; 

update public.userhistoryitem s set entityid=b.lower_user_name
	from app_user_bk b where s.entityid=b.user_key ;

IT Renewals Hotelbeds June 1, 2014

Hi some one can translate the script por oracle database ? thanks in advance

Like Andrew Fomin likes this
0 votes
Martin Cleaver
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 30, 2015

Atlassian strongly advise against manipulating the database in https://answers.atlassian.com/questions/15297886

Kristof Vandermeersch October 19, 2015

It's weird that the username is used as a value in all those tables, the user should represented by an ID, and the username should be a modifiable value. So if you have to change the username (for whatever reason), there is no alternative but modifying the SQL tables, or am I missing something?

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

TAGS
AUG Leaders

Atlassian Community Events