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.
Community moderators have prevented the ability to post new answers.
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.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi some one can translate the script por oracle database ? thanks in advance
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Atlassian strongly advise against manipulating the database in https://answers.atlassian.com/questions/15297886
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Community moderators have prevented the ability to post new answers.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.