Uploaded image for project: 'Confluence Server and Data Center'
  1. Confluence Server and Data Center
  2. CONFSERVER-54971

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




      NOTE: This bug report is for Confluence Server


      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.


      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_key username lower_username
      ff8080814a5a97df014a5b16c37c0008 usera@mail.com usera@mail.com
      ff8080814a5a97df014a5a97fb240001 userA  usera
      • user_mapping table after renaming one of the user in LDAP side:
      user_key username lower_username
      ff8080814a5a97df014a5b16c37c0008 userA usera
      ff8080814a5a97df014a5a97fb240001 userA  



      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:

      diagnosis for specific users
      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:

      NULL users diagnosis
      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.

      pair NULL users
      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;


      Follow the workaround provided in the bug report CONFSERVER-36018




        Issue Links



              Unassigned Unassigned
              mkhairuliana Monique Khairuliana (Inactive)
              17 Vote for this issue
              26 Start watching this issue