Uploaded image for project: 'Confluence Server'
  1. Confluence Server
  2. CONFSERVER-36018

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

    Details

    • Support reference count:
      108

      Description

      NOTE: This bug report is for Confluence Server. Using Confluence Cloud? See the corresponding bug report.

      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.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                16 Vote for this issue
                Watchers:
                43 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Last commented:
                  15 weeks, 2 days ago