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

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.

  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.

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>)."
   

3. Run a full User Directory Sync and watch for output on the terminal
4. 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.

5. Shutdown Confluence
6. 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;
   

7. Restart Confluence
8. 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);
   

3. 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);
   

4. Restart Confluence