Duplicates in the People Directory due to duplicates in the user_mapping table

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

UPDATE

This bug no longer causes duplicates in the people directory because we have hack/patch to hide duplicates on that page, even if duplicates exist in the index. However, dups still appear in @ mentions and other places.When dups show in @ mentions, the dup will show as "Unknown user (<username>) (<username>)

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.

SELECT  * FROM user_mapping where lower_username is null;

If this query returns nothing, have a look at CONF-30050 as well.

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 u.username in (select nullrecord.username from user_mapping nullrecord where lower_username is null) order by u.username;

Proceed to the appropriate workaround based on whether you see single or duplicated records. If you have both, apply the single records workaround first, then duplicated records.

SINGLE RECORDS Workaround

Set the lower_username value for single records only. This will not affect nulls for duplicate records.

update user_mapping u set lower_username = lower(u.username) where u.lower_username is null and u.username in (select nullrecords.username from user_mapping nullrecords group by nullrecords.username having count(nullrecords.username) = 1);

Now run the diagnosis query again to ensure you are no longer affected.

DUPLICATED RECORDS Workaround 1 - if no content has been created by the null user records.

First, try to remove the null values. If there is no content that has been created by the users, this set of queries should work. If you run into any Foreign Key constraint errors during this process, proceed to Workaround 2.

Cleanup

• For Confluence 5.7 and above

  delete from imagedetails where attachmentid in
   (select avatar.contentid from content avatar where avatar.pageid in
    (select userinfo.contentid from content userinfo 
     where userinfo.contenttype = 'USERINFO' 
     and userinfo.username in 
      (select user_key from user_mapping where lower_username is null)));
  
  delete from contentproperties where contentid in
   (select avatar.contentid from content avatar where avatar.pageid in
    (select userinfo.contentid from content userinfo 
     where userinfo.contenttype = 'USERINFO' 
     and userinfo.username in 
      (select user_key from user_mapping where lower_username is null)));
  
  delete from content avatar where avatar.pageid in
   (select userinfo.contentid from content userinfo 
    where userinfo.contenttype = 'USERINFO' 
    and userinfo.username in 
     (select user_key from user_mapping where lower_username is null));
  
  delete from content userinfo 
   where userinfo.contenttype = 'USERINFO' 
   and prevver is not null
   and userinfo.username in
    (select user_key from user_mapping where lower_username is null);
  
  delete from content userinfo 
   where userinfo.contenttype = 'USERINFO' 
   and prevver is null
   and userinfo.username in
    (select user_key from user_mapping where lower_username is null);
  
  delete from logininfo where username in 
    (select user_key from user_mapping where lower_username is null);
  
  delete from user_mapping where lower_username is null;
  

• For Confluence 5.6.x and below

  delete from IMAGEDETAILS 
  where ATTACHMENTID in (select ATTACHMENTID from ATTACHMENTS where PAGEID in (select CONTENTID from CONTENT  where CONTENTTYPE = 'USERINFO' and USERNAME in (select user_key from user_mapping where lower_username is null)));
   
  delete from ATTACHMENTS where PAGEID in (select CONTENTID from CONTENT where CONTENTTYPE = 'USERINFO' and USERNAME in (select user_key from user_mapping where lower_username is null));
  
  delete from CONTENT where CONTENTTYPE = 'USERINFO' and PREVVER is not null and USERNAME in (select user_key from user_mapping where lower_username is null);
  
  delete from CONTENT where CONTENTTYPE = 'USERINFO' and PREVVER is null and USERNAME in (select user_key from user_mapping where lower_username is null);
  
  delete from user_mapping where lower_username is null;
  

DUPLICATED RECORDS Workaround 2 - if the null user records have content associated with them.

Step One: Find the affected users

Firstly, run the below query, and save the results. You'll need the oldKey and newKey from the results.

SELECT 
    user_key AS oldKey,
    (SELECT 
            user_key
        FROM
            user_mapping AS u
        WHERE
            lower(u.username) = u.lower_username
                AND u.username = um.username) AS newKey,
    um.username,
    um.lower_username
FROM
    user_mapping AS um
WHERE
    user_key IN (SELECT 
            user_key
        FROM
            user_mapping
        WHERE
            (lower(username) != lower_username))
        OR (lower_username is null)

Step Two: Validate your Keys:

Then, for each faulty user, grab the old and new keys and input them here in place of 'foo' and 'bar'. Firstly, we'll perform a validation just to make sure that we have the correct user. Only proceed with the fix if the validation query returns exactly two rows - the username you're applying the fix for:

SET @oldKey := 'foo';
SET @newKey := 'bar';

SELECT *
FROM user_mapping
WHERE (user_key = @oldKey AND lower(username) != lower_username) OR (user_key = @newKey AND lower(username) = lower_username)

This query will return two rows. The row where the user names do not match is the oldKey, and the row where they do match is the newKey. Verify that you've got the right key for the given user. You can then use these variables in the following query:

Note about other databases:

The scripts in this bug report are for MySQL, however the syntax is identical for other database types - with the exception of setting variables. Below are some examples of how you might adapt these scripts to suit your database type - you may need to adjust them further.

Microsoft SQL Server

DECLARE @oldKey varchar(255);
SET @oldKey = 'foo';

Oracle

DECLARE
    oldKey VARCHAR2(255) := 'foo';
    newKey VARCHAR2(255) := 'bar';
BEGIN
    -- add the individual fixes here, and remove the "@" symbol

Step Three: Individual Fixes

-- Declare Variables:
SET @oldKey := 'foo';
SET @newKey := 'bar';

-- TRACKBACKLINKS
UPDATE TRACKBACKLINKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE TRACKBACKLINKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACES
UPDATE SPACES SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACES SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACE PERMISSIONS
UPDATE SPACEPERMISSIONS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEPERMISSIONS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE SPACEPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

-- SPACEGROUPS
UPDATE SPACEGROUPS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE SPACEGROUPS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- SPACEGROUPPERMISSIONS
UPDATE SPACEGROUPPERMISSIONS SET PERMUSERNAME = @newKey WHERE PERMUSERNAME = @oldKey;

-- PAGETEMPLATES
UPDATE PAGETEMPLATES SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE PAGETEMPLATES SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- NOTIFICATIONS
UPDATE NOTIFICATIONS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE NOTIFICATIONS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE NOTIFICATIONS SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- LINKS
UPDATE LINKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE LINKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- LIKES
UPDATE LIKES SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- LABEL
UPDATE LABEL SET OWNER = @newKey WHERE OWNER = @oldKey;

-- FOLLOW_CONNECTIONS
UPDATE FOLLOW_CONNECTIONS SET FOLLOWER = @newKey WHERE FOLLOWER = @oldKey;
UPDATE FOLLOW_CONNECTIONS SET FOLLOWEE = @newKey WHERE FOLLOWEE = @oldKey;

-- EXTRNLINKS
UPDATE EXTRNLNKS SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE EXTRNLNKS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;

-- CONTENT_PERM
UPDATE CONTENT_PERM SET CREATOR = @newKey WHERE CREATOR = @oldKey;
UPDATE CONTENT_PERM SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE CONTENT_PERM SET USERNAME = @newKey WHERE USERNAME = @oldKey;

-- CONTENT_LABEL
UPDATE CONTENT_LABEL SET OWNER = @newKey WHERE OWNER = @oldKey;

-- CONTENT
UPDATE CONTENT SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE CONTENT SET USERNAME = @newKey WHERE USERNAME = @oldKey;
UPDATE CONTENT SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- ATTACHMENTS
UPDATE ATTACHMENTS SET LASTMODIFIER = @newKey WHERE LASTMODIFIER = @oldKey;
UPDATE ATTACHMENTS SET CREATOR = @newKey WHERE CREATOR = @oldKey;

-- LOGIN INFO
DELETE FROM logininfo WHERE USERNAME = @oldKey;

Using the mass duplicate fixer

If you have a large number of duplicates, you can use the Mass Duplicate Fixer to generate the SQL required. Using the diagnosis query in Step 2, you can generate a tab delimited list of SQL queries. Save the output of that query in the format oldKey<tab>newKey is a tab. Name the file keypairs.txt.

You'll need python installed to run the script.

Extract mass-dup-fixer.zip to a directory, and place your keypairs.txt file into this directory. Once you've got Python installed, just run path/to/python mass-dup-fixer.py from the directory you extracted everything to.

Step 4: Remove the user records

Now, go back and run the queries in DUPLICATED RECORDS Workaround 1 again to remove the user records.

NB: If you are still experiencing problems, have a look at CONF-30050 as well.

Suggestion for fix

Is it possible to place a null constraint on lower_username? And potentially username as well? Currently there are no null constraints on this table.
Another fix that would be ideal is to fix the user indexing upgrade task so that it handles null values gracefully, and performs this operation to remove them.