Unknown Users appears in Mentions, permissions, etc due to duplicates in the user_mapping table

Summary

Unknown Users appears in various places such as Mentions:

Page restrictions:

And everywhere else. This causes degraded functions:

• Mentioned users are not notified
• Permissions and restrictions are not properly set - eg, users unable to edit pages

How to replicate

The issue happens when we rename LDAP users to usernames that already exists in Confluence. For example:

1. Set a user directory connection to LDAP A with username attribute: SAMAccountname
2. Set another user directory connection to LDAP A with username attribute: mail
3. Edit the second user directory's username attribute to SAMAccountname

Expected Result

Since the newly renamed users are being renamed to existing set of users, it's expected that the users are now "merged".

Actual Results

Duplicated users exists in the user_mapping table. One of them has NULL lower_username and causing sightings of Unknown User in Confluence UI.

Notes

When connecting to a new user directory with a new set of usernames, Confluence will create records of these users in the user_mapping table. 

When user are renamed, Confluence will update user records in the user_mapping table. This is where the issue happens as users are being renamed to an already existing username and forced one of the records' lower_username to be NULL to avoid duplicate constraint in the database:

• user_mapping table after connecting to 2 LDAP with different usernames

• user_mapping table after renaming one of the user in LDAP side:

Diagnosis

This can occur because user_mapping.lower_user_name is null. You may have a single user record for the affected user, or you may have another record which does include a lower_username.

• Single user record

  user_key	username	lower_username
  ff8080814a5a97df014a5a97fb240001	userA

• Duplicated users (taken from an OnDemand instance)

  user_key	username	lower_username
  ff8080814a5a97df014a5b16c37c0008	userA	usera
  ff8080814a5a97df014a5a97fb240001	userA

Diagnosis - is this affecting you?

First, try a content reindex. If this doesn't help, run this query (on any version). If it returns any results, this bug is the cause of your duplicates.

Check and confirm if the user showing as unknown user is indeed affected by this bug:

SELECT * FROM user_mapping where lower(username) = '<username_lowercase>';

Replace <username_lowercase> with the lower cased version of the username that's observed to be having this behavior.

Check if there are more users affected by this bug:

SELECT  * FROM user_mapping where lower_username is null;

If the above SQL query returns with some results, now you need to determine if you have single records or duplicated records, as the workaround you perform will depend on this.

select u.* from user_mapping u where lower(u.username) in (select nullrecord.username from user_mapping nullrecord where lower_username is null) order by u.username;

Workaround:

Follow the workaround provided in the bug report CONFSERVER-36018