JIRA recording wrong name under username in database

Question

My organization recently made changes to our AD connection in JIRA (to limit the scope of users being pulled from AD).

After the changes we determined that users no longer had access to their Dashboards and Filters.

Upon experimenting with the issue it was determined that when a user created a new Dashboard the 'username' being recorded in the table 'portalpage' was incorrect.

While it should be first initial last name (ie. jdoe) it is being recorded in this table as last name, first name (ie. doe, jon).

Anyone have any idea why JIRA is recording the wrong value to this column in portalpage (as well as other tables)?

Regards.

James Kosiba · Answer

For anyone with the same issue that may stumble upon this post.

DISCLAIMER: The following solution is somewhat drastic and YMMV.  ATLASSIAN in no way recommends or condones direct manual edits of database tables.  Please proceed at your own risk.

ISSUE: Any changes to AD where users are removed from JIRA and then added back in with additional AD changes (renames/group filtering/etc), results in a new user record being created and the old record being renamed and dissociated with the new user account.

CAUSE:  When users are added to JIRA they have their 'LOWER_USER_NAME' and a generated key added to DB table 'APP_USER'.  When a new user is added with the same 'LOWER_USER_NAME' (even if it is the same user that was previously in the system), the new account gets a new entry in the APP_USER table, but the key is different.  Additionally, JIRA changes the 'LOWER_USER_NAME' of the old account.

EXAMPLE:  User Jon Doe (jdoe) is added to the system via AD.  User is recorded in APP_USER as USER_KEY: jdoe / LOWER_USER_NAME: jdoe.  Changes are made to AD and jdoe gets dropped from JIRA.  When AD is corrected and jdoe is added back to JIRA, a new record is added to APP_USER such as USER_KEY: jdoe1 / LOWER_USER_NAME: jdoe.  AND the old record is changed to USER_KEY: jdoe / LOWER_USER_NAME: jdoe#1.

This process, while functional in a scenario where a new employee has the same name as an employee that is no longer with the company (new user will not be associated with old users records), it breaks down when it is actually the same user and gives you no way of indicating that it is the same user.

DRASTIC SOLUTION (AGAIN, proceed at your own risk): DELETE all affected AD entries in APP_USER and DELETE all affected AD entries in CWD_USER.  All affected AD entries means, DON'T delete records associated with your local Admin account or any other local accounts you may have, and make sure you do not have any old users that have the same username as new users (making entries like jdoe#1 valid).  Once all AD records are deleted, re-Sync your AD.  This will create new users in CWD_USER and clean (original) 1:1 records in APP_USER (USER_KEY: jdoe / LOWER_USER_NAME: jdoe).

ADDITIONAL CLEANUP:  If any time has passed between the breaking of AD (new user associations created in APP_USER) and performing the above fix, you will also need to update any entries made by affected users in any tables that contain username/author/creator/reporter/etc. because any activity between the break and fix will have resulted in records associating users with the new 'bad' user_key and once changed back users will lose access to these records created/changed in this window.

Example of affected tables (may do others depending on your usage): PORTALPAGE, 
SEARCHREQUEST, FILTERSUBSCRIPTION, FAVORITEASSOCIATION, JIRAISSUE, JIRAACTION, CHANGEGROUP, OS_HISTORYSTEP, USERHISTORYITEM, CHANGEITEM.

In an ORACLE (PL/SQL) environment, the following sample scripts may be helpful.

--UPDATE PORTALPAGE
MERGE INTO portalpage pp
USING (SELECT t2.ID
                          ,s2.lower_user_name
                          ,s2.user_name
             FROM    portalpage t2
                          ,cwd_user s2
           WHERE    t2.username (+) = s2.lower_display_name
        ) src
ON (pp.id = src.id)
WHEN MATCHED THEN
UPDATE SET pp.username = src.lower_user_name;

Also, here is the scrip I used to clean up APP_USER and CWD_USER tables.  PLEASE NOTE: My environment was relatively new and did not have any instances of old/terminated users.  Just a lot of new AD accounts and local accounts.

DELETE FROM cwd_user cu

WHERE cu.directory_id <> 1; --1 is the ID for my local directory.

DELETE FROM app_user;

INSERT INTO app_user

(id, user_key, lower_user_name)

SELECT id, user_name, lower_user_name

FROM cwd_user;

This script deletes all directory users that are not from the local directory (mine was 1, change yours as needed).  It then wipes all entries from APP_USER (again, I did not have any old AD accounts in the system that I needed to retain).  And finally, it creates new clean records in APP_USER for all of the local accounts that were retained from the local directory (admin account, etc).

Again, YMMV and please be absolutely sure of what your are doing before you attempt any of these steps.  These are simply the steps that worked for me in my environment that may be helpful for anyone experiencing the same issue.

Good Luck!

EDIT:  Forgot to give credit to this thread for pointing me in the right direction.

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

James Kosiba · Answer

I have reindexed multiple times (background and locked) and restarted multiple times.

I have changed the user records in the 'CWD_USER' table and everything appears correct.

I have gone so far as to change the displayname and lower_display_name in CWD_USER and what makes this even more odd is that the system still generates the 'username' as 'last, first'.  So it's not even collecting this value from CWD_USER unless it is concatenating some other fields.

Michael Partyka · Answer

Did you reindex JIRA (full)?

Try to restart JIRA too