We recently decided to streamline all our logins across our internal ServiceRocket systems (which include Confluence and Jira instances), to use a Single Sign On (SSO) service called Okta (in case you haven't heard, Okta is one of the major Identity Providers in the SSO sphere). Part of the requirement for us to do that is to change our login format from usernames (which we have been using for the past decade or so), to email address (appending @servicerocket.com):

With Confluence and Jira's built in capability to rename users automatically (as long as their external_id remains the same - explained below), this should be a pretty straightforward change. This is helped by the fact that all our users are managed by Crowd, thus allowing us to simply rename the users within Crowd, which should hopefully trigger the rename in all connected systems, see:

What happens in the DB in these systems:

Before:

cwd_user table

app_user table (JIRA)

user_mapping table (Confluence)

After:

Confluence and Jira detects that the external_id already exists in the cwd_user table, but with a different username. This triggers the user rename procedure, resulting in the results below:

cwd_user table

app_user table (JIRA)

user_mapping table (Confluence)

The key point to understand here is that the user_key in both Confluence and Jira databases need to stay the same for the user before and after the rename. Only the user_name, lower_user_name and lower_username is allowed to change!
This is important, because the user_key is the primary key used to link Content and Jira issues to the actual users.

See:

Jira

jiraissues table

app_user table (JIRA)

Confluence

CONTENT table

user_mapping table (Confluence)

As long as the user_key is tied to the correct entries in the CONTENT, jiraissues (and a bunch of other tables, too many to list here), then the renaming operation is a success and users will maintain their content ownership.

But...Something Went Wrong

Before we performed the change, we decided to run the following 2 queries in Confluence and JIRA DB respectively:

As some of you may have guessed, these queries should not return any results at all. But sadly, they do, and quite a bunch of rows too.

Why shouldn't these queries return any results? One reason is that since we are changing the usernames from a format like "fooguan.sim" to "fooguan.sim@servicerocket.com", the rename will fail if it detects that the username that is being renamed to, already exists in the DB. Example:

Before:

cwd_user table

app_user table (JIRA)

user_mapping table (Confluence)

After:

(Rename fails, data in these tables remain the same, except for the cwd_user table)

cwd_user table

app_user table (JIRA)

user_mapping table (Confluence)

You may argue that since the username is renamed in cwd_user, it should then work, shouldn't it? Well, the user is able to login with the new email address format, but sadly, the user will not be linked to his/her old content. Content that the user previously authored will be marked as authored by an Unknown User, and similarly, issues assigned to this user will be marked as assigned to an Unknown User.

This is because of the mismatch in the user_key:

Jira

jiraissues table

app_user table (JIRA)

Confluence

CONTENT table

user_mapping table (Confluence)

How Should You Fix This?

Alternative 1: Fix Before Rename

Alternative 1 is probably the best alternative, which is to make sure these users with @servicerocket.com (the new username) has no content tied to them, and remove them from the system before the rename. This will ensure a clean and safe rename but note that from our experience, deleting from the UI may not remove the app_user and user_mapping entries, so Alternative 2 then becomes more viable.

Alternative 2: Fix After the Rename

Alternative 2 is what we call the swap method. Swap the user_key and username pairing between the broken user and the proper user.

The concept is simple: whatever queries you run, make sure that you end up with an entry that has the new username (in this case: fooguan.sim@servicerocket.com) as its lower_username but with the old user_key. In this case it would look like below:

cwd_user table

app_user table (JIRA)

user_mapping table (Confluence)

For our case, we simply ran the following (example only, and works well on our instance, but may not work on others. Do not run this on your own instance unless you have already consulted us!):

Jira

Confluence

‍