Userbase table empty in JIRA 5.27.

[Mohammed Rashid]

When i access JIRA userbase table, it shows empty set but it shows 2000 users from application

mysql> select * from userbase;

Empty set (0.00 sec)

~Rash

[Henning Tietgens · Accepted solution]

Users are in cwd_user table.

Henning

[Mohammed Rashid]

Hi..

can we rename multiple users at a time using groovy script runner??

[Mohammed Rashid]

I have installed Scriptrunner and it works fine, but doing the same for multiple users will be hectic.

It will be great if we can get the usernames from a csv file or excel file.

[Christian Czaia [Decadis AG]]

You might want to look at Jamie's script runner plugin as he's got a built-in script that's probably doing what you'Re after:

https://jamieechlin.atlassian.net/wiki/display/GRV/Built-In+Scripts#Built-InScripts-RenameaUserID

[Nic Brough -Adaptavist-]

Userbase was deprecated in Jira 4.3 - the system moved to an internal embedded Crowd.

See cwd_user instead.

[Mohammed Rashid]

Thanks and that is great...

I have to change the username of few users and i have a script that i got from http://www.darkrune.org/blog/?p=459 but few tables are deprecated.

Is there any other other script that i can use in JIRA 5 or what are records that neeeds to be renamed.

update jiraissue set reporter='newuser' where reporter='olduser@example.com';
update jiraissue set assignee='newuser' where assignee='olduser@example.com';
update jiraaction set AUTHOR='newuser' where AUTHOR='olduser@example.com';
update changegroup set AUTHOR='newuser' where AUTHOR='olduser@example.com';
update changeitem set OLDVALUE='newuser' where OLDVALUE='olduser@example.com' and FIELD='assignee';
update changeitem set NEWVALUE='newuser' where NEWVALUE='olduser@example.com' and FIELD='assignee';
update searchrequest set authorname='newuser' where authorname='olduser@example.com';
update searchrequest set username='newuser' where username='olduser@example.com';
update schemepermissions set perm_parameter='newuser' where perm_parameter='olduser@example.com' and perm_type="user";
update schemepermissions set perm_parameter='newuser' where perm_parameter='olduser@example.com' and perm_type="user";
update searchrequest set authorname='newuser' where authorname='olduser@example.com';
update membershipbase set USER_NAME='newuser' where USER_NAME='olduser@example.com';
update OS_CURRENTSTEP set owner='newuser' where owner='olduser@example.com';
update OS_CURRENTSTEP set caller='newuser' where caller='olduser@example.com';
update OS_HISTORYSTEP set owner='newuser' where owner='olduser@example.com';
update OS_HISTORYSTEP set caller='newuser' where caller='olduser@example.com';
update fileattachment set author='newuser' where author='olduser@example.com';
update filtersubscription set username='newuser' where username='olduser@example.com';
update project set lead='newuser' where lead='olduser@example.com';
update userbase set username='newuser' where username='olduser@example.com';
update customfieldvalue set stringvalue='newuser' where stringvalue='olduser@example.com';
update columnlayout set username='newuser' where username='olduser@example.com';
update portalpage set username='newuser' where username='olduser@example.com';
update userhistoryitem set USERNAME='newuser' where USERNAME='olduser@example.com';
update worklog set AUTHOR='newuser' where AUTHOR='olduser@example.com';
update worklog set UPDATEAUTHOR='newuser' where UPDATEAUTHOR='olduser@example.com';
update notification set notif_parameter='newuser' where notif_parameter='olduser@example.com';
update component set lead='newuser' where lead='olduser@example.com';
update columnlayout set username='newuser' where username='olduser@example.com';

[Nic Brough -Adaptavist-]

Oh, um...

Membershipbase has gone too. I think portalpage may have gone since that script was written too, and I don't see any references to the many-to-many table that I keep forgetting the name of.

Frankly though, I really wouldn't do it this way - it's a nightmare if there's any chance of getting any of it wrong (to the point where you'll be screaming for database backups) and even if you're immensely careful before you do it, you've still got a lot of downtime while you stop Jira, apply the changes and reindex afterwards (assuming a moderately sized Jira)

I'd reach for Jamie's script runner, as explained at https://answers.atlassian.com/questions/91415/how-to-change-username-in-jira