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

Renaming users in LDAP to a previously deleted user causes duplicates in user_mapping.

    XMLWordPrintable

Details

    • Bug
    • Status: Gathering Impact (View Workflow)
    • Medium
    • Resolution: Unresolved
    • 6.7.2
    • None
    • User - Management
    • None

    Description

      Summary

      When deleting a user in LDAP, and then renaming another user to that deleted user causes duplicates in user_mapping with the second having a null value for lower_username.

      Environment

      • Confluence 6.7.2
      • External LDAP

      Steps to Reproduce

      1. Set up a fresh Confluence 6.7.2
      2. Set up an external LDAP directory
      3. Connect the external LDAP directory to Confluence as a User Directory
      4. Create a user tony_stark
      5. Create a user ext_tony_stark
      6. Synchronise Confluence with external LDAP directory
      7. Log in as ext_tony_stark and log out again (this sets up data like the People Directory)
      8. Delete user tony_stark in LDAP
      9. Rename ext_tony_stark to tony_stark in LDAP
      10. Synchronise with external LDAP directory

      Expected Results

      There should be only one entry in user_mapping for user each user.

      confluence-6.7.2=> select * from user_mapping where username in ('tony_stark', 'ext_tony_stark');
                   user_key             | username | lower_username 
      ----------------------------------+----------+----------------
       ff80808161f8b7b40161f8d1a60c0006 | tony_stark    | tony_stark
       ff80808161f8b7b40161f8d1a60b0005 | ext_tony_stark    | ext_tony_stark
      (2 rows)
      

      Actual Results

      There are 2 rows in user_mapping for username tony_stark, with null for the second.

      confluence-6.7.2=> select * from user_mapping where username in ('tony_stark', 'ext_tony_stark');
                   user_key             | username | lower_username 
      ----------------------------------+----------+----------------
       ff80808161f8b7b40161f8d1a60c0006 | tony_stark    | tony_stark
       ff80808161f8b7b40161f8d1a60b0005 | tony_stark    | 
      (2 rows)
      

      Notes

      Is caused by this code where it falls through to the second else if branch.

      com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao
        private void updateLowerName(ConfluenceUserImpl user, String newUsername, boolean overrideExisting) {
              ConfluenceUserImpl existingUser = (ConfluenceUserImpl) findByUsername(newUsername);
              if (existingUser == null) {
                  user.setLowerName(IdentifierUtils.toLowerCase(newUsername));
              } else if (overrideExisting) {
                  existingUser.setLowerName(null);
                  getHibernateTemplate().update(existingUser);
                  // Flush required because in a collection of pending session updates Hibernate doesn't guarantee that the
                  // nulling of lowerName happens before the user name is re-used for 'user'
                  getHibernateTemplate().flush();
                  user.setLowerName(IdentifierUtils.toLowerCase(newUsername));
              } else {
                  user.setLowerName(null);
              }
          }
      

      Workaround

      At this time the workaround requires database manipulation to either update or remove the extra entry in user_mapping. Follow the steps in CONFSERVER-36018 for the workaround if users have logged in or the following steps do not work.

      IMPORTANT

      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.

      You can check the data in the database with these queries.

      select * from user_mapping where username in (select username from user_mapping where lower_username is null);
      
      select * from CONTENT inner join user_mapping on CONTENT.USERNAME = user_mapping.user_key where user_mapping.lower_username is null;
      
      select count(*), CONTENT.CONTENTTYPE, user_mapping.username from CONTENT
      inner join user_mapping on CONTENT.USERNAME = user_mapping.user_key where user_mapping.lower_username is null group by CONTENT.CONTENTTYPE, user_mapping.username
      order by count(*) desc;
      

      If the last query returns anything other than CONTENTTYPE = USERINFO, then follow the steps in Option 2.

      Option 1

      Please contact Atlassian Support if you are unsure about taking these steps as they are very specific to this bug report and cannot be applied generically.

      1. Turn on SQL Logging in > General Configuration > Logging and Profiling
      2. Run this command in a terminal shell (adjust for location of atlassian-confluence.log)
        tail -F /var/atlassian/application-data/confluence/logs/atlassian-confluence.log | fgrep -B 3 -i "[engine.jdbc.spi.SqlExceptionHelper] logExceptions Violation of UNIQUE KEY constraint 'unq_lwr_username'. Cannot insert duplicate key in object 'dbo.user_mapping'. The duplicate key value is (<NULL>)."
        
      1. Run a full User Directory Sync and watch for output on the terminal
      2. When you see something like (it will be for a different user)
        2018-03-23 03:20:06,309 DEBUG [Caesium-1-2] [org.hibernate.SQL] logStatement update user_mapping set username=?, lower_username=? where user_key=?
        2018-03-23 03:20:06,309 TRACE [Caesium-1-2] [type.descriptor.sql.BasicBinder] bind binding parameter [1] as [VARCHAR] - [tony_stark]
        2018-03-23 03:20:06,310 TRACE [Caesium-1-2] [type.descriptor.sql.BasicBinder] bind binding parameter [2] as [VARCHAR] - [null]
        2018-03-23 03:20:06,324 ERROR [Caesium-1-2] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Violation of UNIQUE KEY constraint 'unq_lwr_username'. Cannot insert duplicate key in object 'dbo.user_mapping'. The duplicate key value is (<NULL>).
        

        take note of the username listed in the 2nd line, here in the example is tony_stark.

      1. Shutdown Confluence
      2. Run these SQL delete commands to remove all the invalid user entries
        delete from logininfo where username in (select user_key from user_mapping where lower_username is null);
        delete from notifications where username in (select user_key from user_mapping where lower_username is null);
        delete from CONTENT where USERNAME in (select user_key from user_mapping where lower_username is null);
        delete from content_label where owner in (select user_key from user_mapping where lower_username is null);
        delete from label where owner in (select user_key from user_mapping where lower_username is null);
        delete from user_mapping where lower_username is null;
        
      1. Restart Confluence
      2. Repeat these steps until there are no more errors

      Option 2

      This involves changing the incorrect username in user_mapping to something that won't every sync again, and so will map all the content to an invalid user. As they do not have an entry in cwd_user they will not appear in searches or @ mentions.

      1. Shutdown Confluence
      2. Identify the broken user with
        select * from user_mapping where username in (select username from user_mapping where lower_username is null);
        
      1. For the user with the null value for lower_username we update that with (here, we're using the example of tony_stark from above
        update user_mapping set username = 'tony_stark_donotuse', lower_username = 'tony_stark_donotuse' where user_key = (select user_key from user_mapping where username = 'tony_stark' and lower_username is null);
        

         

      1. Restart Confluence

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              jrichards@atlassian.com James Richards
              Votes:
              17 Vote for this issue
              Watchers:
              22 Start watching this issue

              Dates

                Created:
                Updated: