What are all the SQL Tables to be modified if we want to change a group of usernames ?

Nithin CK May 7, 2015

Hi,

 

We have lot of usernames with their mail address and now we have to update the domain name of all those user accounts.

 

So we need the list of tables to modify for a successful username update ?

 

Best Regards,

Nithin CK

4 answers

1 accepted

2 votes
Answer accepted
Matheus Fernandes
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

Actually, there is also data on app_user. Still, I would not recommend doing this - rather, modify these usernames by editing the users in JIRA. If multiple users need to be edited, use a script to update them through REST: https://docs.atlassian.com/jira/REST/latest/#d2e4140

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

Granted, Atlassian advises against the use of SQL to manipulate user tables.

In at least the case of https://answers.atlassian.com/questions/250787 and my case, using the UI (or REST) does not work (see https://jira.atlassian.com/browse/JRA-45203)

Tempo users had this problem here: https://tempoplugin.jira.com/browse/JTMPO-1100

Atlassian acknowledge this in https://jira.atlassian.com/browse/JRA-36642 - this is reported to be fixed in JIRA 7.

 

 

0 votes
Tim Eddelbüttel
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.
May 9, 2015

Hello Nithin,

what is you JIRA version?
I've done a complete rename of all JIRA users over 3 directories (internal, 2 different active directories). Some internal users where also in these active directories with different usernames. This was done against JIRA 6.0 and at this version there was no offical rename feature.

Regards,
Tim

0 votes
João Palharini
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

Hi Nithin!

All information regarding users in JIRA (at least on the most recent versions) is distributed between two tables: {{cwd_user}} and {{cwd_membership}}.

--
Cheers!

Joao

Nithin CK May 7, 2015

Hi Joao,

 

Thanks for the quick response.

 

Actually I modified the usernames in all these below tables and attributes in the image but still we faced few problems like dashboard was not accessible, filters were not available.

 

So we need to know if we have missed anything else in our procedure ?

 

SQL_Tables_List.jpg

 

~Nithin CK

Matheus Fernandes
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

Nithin, The thing is, not all of those will need to be changed. Most of those are associated with the user's user_key (from app_user) and not with the user_name from cwd_user. That's likely the reason this is breaking.

Matheus Fernandes
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

Those should not be changed, the user_key should always remains the same. In order to maintain the integrity of the database I'd definitely advise you to use REST or the UI. :)

Nithin CK May 7, 2015

Hi Matheus, Thanks for the response. Can I get a proper list of those tables ? Also if I don't change the lower_user_name attribute on app_user table then will it not lead to some inconsistency ?

crf
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

You should use REST, as advised. Modifying the database directly when there is a perfectly good *supported* way to accomplish what you want is needlessly reckless. However, if you stubbornly and against all sane advice insist on manipulating the data directly, the impact should be limited to app_user.lower_user_name, the cwd_user name columns, and the cwd_membership child_name and lower_child_name columns for those with a membership_type of CWD_USER. All the others should remain the user's key -- that is the whole point of the rename user work we did in 6.0.

crf
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

(In other words, by changing all those others, you have effectively trashed your database. You should restore from a backup and use REST as was recommended.)

crf
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

Correction: ... membership_type of GROUP_USER ...

Nithin CK May 7, 2015

Hi Chris & Matheus, Thanks for the help. Is REST a plugin and do we have a compatible REST plugin for Jira 6.1.1 version ?

crf
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
May 7, 2015

REST is a plugin, but it is included with JIRA, not one you would have to download and install separately. You can read more about what REST is and walk through a few tutorials about it here: https://developer.atlassian.com/jiradev/api-reference/jira-rest-apis

Suggest an answer

Log in or Sign up to answer