Ug is not a valid user error while adding user as a Board Admin

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

  

Where a workaround involves running SQL scripts directly on your database, we strongly recommend you do this first on a non-production, staging or test environment before running the scripts on your production database. We also strongly recommend you take a backup of your data before making any modifications via SQL scripts.

 

Summary

Trying to add a new board admin will throw the following error in the UI upon saving the new Board Admin user;

Environment


  • Jira Data Center recently migrated from Cloud

Diagnosis


Query the app_user table to see the user_key of the specific user-facing the error:


select * from app_user where lower_user_name = 'psouza'


You may find the following:

IDuser_keylower_user_name
11054
ug:1287e681-a24c-44ec-bac7-e72ad966a9a2
psouza
11153
ug:55617a31-bbeb-4d7b-857a-776684313a24
pedro.souza




Cause

The issue happens because of a bug from the cloud instance: JSWCLOUD-20732 - Getting issue details... STATUS  the bug is fixed in Cloud so that those user keys will no longer cause the error in the Cloud, however, the fix was not ported to Server or Datacenter, that being said when you have users created with that user_key format and import the backup to server/data center, you will still face the error.

Solution


Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

The easier approach to fix this issue is simply to update the user_key to match the lower_user_name in the app_user table, however, this is a bit tricky since all data association in Jira is made in the user_key, which means if we need to do that we will need to migrate all existing data the new user_key, otherwise those users would lose all their data associated to their profiles:


  1. Used the following query to get an output of all the users with that type of user_key:

    select * from app_user where user_key like 'ug%'
    
  2. With this list, make sure to adjust manually and kept only the valid users, there might be user_keyes 'ug:' with lower_user_name also set to 'ug:', those don't need to be changed, make sure to have only the ones that had lower_user_name different than the user_key.

  3. Create a temporary table to create the relation of the new user key to the old user key:

    CREATE TABLE tmp_app_user_migration
    (
    old_user_key varchar(255),
    new_user_key varchar(255)
    );



  4.  Insert the values gathered from the first query that will do the mapping:

    INSERT INTO tmp_app_user_migration (old_user_key, new_user_key) 
    VALUES
    ('ug:70a5cee4-f6c3-4a94-bb12-69a021b25e68', 'psouza'),
    ('ug:6130f42d-2a4b-466c-bcc8-c675b173c61b', 'souza2'),
    ('ug:36962dc0-74b2-4016-95c2-4187623c4915', 'souza3'),
    ('ug:000e3e20-2440-45d8-80d0-7365e3d3ccbf', 'souza4);
  5. Now, we have the mapping created in the temp table, we will use the following set of updates to migrate all data from the old_user_key to the new_user_key to all users we imported into the table is doing the mapping:

    update audit_item set object_id = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_id = tmp_app_user_migration.old_user_key;
    update audit_item set object_name = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_name = tmp_app_user_migration.old_user_key;
    update audit_log set author_key= tmp_app_user_migration.new_user_key from tmp_app_user_migration where author_key= tmp_app_user_migration.old_user_key;
    update audit_log set object_id = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_id = tmp_app_user_migration.old_user_key;
    update audit_log set object_name = tmp_app_user_migration.new_user_key from tmp_app_user_migration where object_name = tmp_app_user_migration.old_user_key;
    update changegroup set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
    update changeitem set newvalue = tmp_app_user_migration.new_user_key from tmp_app_user_migration where newvalue = tmp_app_user_migration.old_user_key;
    update changeitem set oldvalue = tmp_app_user_migration.new_user_key from tmp_app_user_migration where oldvalue = tmp_app_user_migration.old_user_key;
    update component set lead = tmp_app_user_migration.new_user_key from tmp_app_user_migration where lead = tmp_app_user_migration.old_user_key;
    update customfieldvalue set stringvalue = tmp_app_user_migration.new_user_key from tmp_app_user_migration where stringvalue = tmp_app_user_migration.old_user_key;
    update favouriteassociations set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
    update fileattachment set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
    update filtersubscription set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
    update jiraaction set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
    update jiraaction set updateauthor = tmp_app_user_migration.new_user_key from tmp_app_user_migration where updateauthor = tmp_app_user_migration.old_user_key;
    update jiraissue set reporter = tmp_app_user_migration.new_user_key from tmp_app_user_migration where reporter = tmp_app_user_migration.old_user_key;
    update jiraissue set assignee = tmp_app_user_migration.new_user_key from tmp_app_user_migration where assignee = tmp_app_user_migration.old_user_key;
    update jiraissue set creator = tmp_app_user_migration.new_user_key from tmp_app_user_migration where creator = tmp_app_user_migration.old_user_key;
    update os_historystep set caller = tmp_app_user_migration.new_user_key from tmp_app_user_migration where caller = tmp_app_user_migration.old_user_key;
    update portalpage set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
    update project set lead = tmp_app_user_migration.new_user_key from tmp_app_user_migration where lead = tmp_app_user_migration.old_user_key;
    update projectroleactor set roletypeparameter = tmp_app_user_migration.new_user_key from tmp_app_user_migration where roletypeparameter = tmp_app_user_migration.old_user_key;
    update searchrequest set authorname = tmp_app_user_migration.new_user_key from tmp_app_user_migration where authorname = tmp_app_user_migration.old_user_key;
    update searchrequest set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
    update userhistoryitem set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key;
    update worklog set author = tmp_app_user_migration.new_user_key from tmp_app_user_migration where author = tmp_app_user_migration.old_user_key;
    update worklog set updateauthor = tmp_app_user_migration.new_user_key from tmp_app_user_migration where updateauthor = tmp_app_user_migration.old_user_key;
    update userassociation set source_name = tmp_app_user_migration.new_user_key from tmp_app_user_migration where source_name = tmp_app_user_migration.old_user_key
    	and sink_node_id not in (select U1.sink_node_id from userassociation U1, userassociation U2 
    								where U1.source_name = tmp_app_user_migration.old_user_key and U2.source_name = tmp_app_user_migration.new_user_key
    								and U2.sink_node_id = U1.sink_node_id);
    update userhistoryitem set username = tmp_app_user_migration.new_user_key from tmp_app_user_migration where username = tmp_app_user_migration.old_user_key
    	and entityid not in (select U1.entityid from userhistoryitem U1, userhistoryitem U2 
    								where U1.username = tmp_app_user_migration.old_user_key and U2.username = tmp_app_user_migration.new_user_key
    								and U2.entityid = U1.entityid
    								and U2.entitytype = U1.entitytype);	
    
    update "AO_60DB71_BOARDADMINS" set "KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "KEY" = tmp_app_user_migration.old_user_key;			
    update "AO_60DB71_AUDITENTRY" set  "USER" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER" = tmp_app_user_migration.old_user_key;
    update "AO_60DB71_RAPIDVIEW" set  "OWNER_USER_NAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "OWNER_USER_NAME" = tmp_app_user_migration.old_user_key;
    update "AO_0A5972_NOTIFICATION_SETTING" set "USER_KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_KEY" = tmp_app_user_migration.old_user_key;
    update "AO_0A5972_PUSH_REGISTRATION" set "USER_KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_KEY" = tmp_app_user_migration.old_user_key;
    update "AO_21F425_MESSAGE_MAPPING_AO" set "USER_HASH" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_HASH" = tmp_app_user_migration.old_user_key;
    update "AO_21F425_USER_PROPERTY_AO" set "USER" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER" = tmp_app_user_migration.old_user_key;
    update "AO_4789DD_READ_NOTIFICATIONS" set "USER_KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_KEY" = tmp_app_user_migration.old_user_key;
    update "AO_4AEACD_WEBHOOK_DAO" set "LAST_UPDATED_USER" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "LAST_UPDATED_USER" = tmp_app_user_migration.old_user_key;
    update "AO_563AEE_ACTIVITY_ENTITY" set "USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USERNAME" = tmp_app_user_migration.old_user_key;
    update "AO_563AEE_ACTOR_ENTITY" set "USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USERNAME" = tmp_app_user_migration.old_user_key;
    
    update "AO_733371_EVENT" set "USER_KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_KEY" = tmp_app_user_migration.old_user_key;
    update "AO_733371_EVENT_PARAMETER" set "VALUE" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "VALUE" = tmp_app_user_migration.old_user_key;
    update "AO_733371_EVENT_RECIPIENT" set "USER_KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_KEY" = tmp_app_user_migration.old_user_key;
    update "AO_97EDAB_USERINVITATION" set "SENDER_USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "SENDER_USERNAME" = tmp_app_user_migration.old_user_key;
    update "AO_A0B856_WEB_HOOK_LISTENER_AO" set "LAST_UPDATED_USER" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "LAST_UPDATED_USER" = tmp_app_user_migration.old_user_key;
    update "AO_CFF990_AOTRANSITION_FAILURE" set "USER_KEY" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USER_KEY" = tmp_app_user_migration.old_user_key;
    update "AO_E8B6CC_ORGANIZATION_MAPPING" set "ADMIN_USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "ADMIN_USERNAME" = tmp_app_user_migration.old_user_key;
    update "AO_E8B6CC_PROJECT_MAPPING" set "USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USERNAME" = tmp_app_user_migration.old_user_key;
    update "AO_E8B6CC_PROJECT_MAPPING_V2" set "ADMIN_USERNAME" = "ADMIN_USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "ADMIN_USERNAME" = tmp_app_user_migration.old_user_key;
    update "AO_E8B6CC_PR_PARTICIPANT" set "USERNAME" = tmp_app_user_migration.new_user_key from tmp_app_user_migration where "USERNAME" = tmp_app_user_migration.old_user_key;


  6. We are good to fix the problematic user_keys at this point with the additional update:

    update app_user set user_key = lower_user_name where user_key like 'ug:%'
  7. Once this is done, start Jira and perform a lock-reindex.




Last modified on Apr 6, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.