Uploaded image for project: 'Confluence Cloud'
  1. Confluence Cloud
  2. CONFCLOUD-30050

Users are duplicated in the People Directory, shares, mentions, and have two USERINFO records in the database

    XMLWordPrintable

Details

    Description

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

      Update from Atlassian

      This issue has been resolved in Confluence 5.9.8.

      The fix we implemented is in the front end, and stops duplicate user records from displaying anywhere in Confluence - in the People Directory, Shares, or Mentions. The underlying duplicates may still exist in the database, and new duplicates may be created, however these are harmless and can be safely ignored. As of Confluence 5.9.8, you should no longer see duplicate users anywhere in Confluence.

      Symptoms

      • Users appear twice in the People Directory, in shares, and mentions
      • Users have two USERINFO records with no 'prevver' value (ie two current records), eg:
        select * from content where contenttype like 'USERINFO' and prevver is null;
        

        USERINFO records that do not have an entry in prevver are the currently active records. This is a little counter-intuitive given the name of the field, however older records that are no longer active contain the id of the superseding record in prevver. If prevver is null, the record is considered active.

      Steps to Reproduce

      This may not be the only way to have this happen, but doing the following sequence will cause this to happen:

      1. Create a user in an LDAP server, which you want to test with
      2. Set up the LDAP server in Confluence
      3. Synchronise
      4. Log in as that user and possibly add some extra profile information such as a picture/description
      5. Disable the LDAP directory
      6. Rename that user in the LDAP server
      7. Create a new LDAP directory exactly the same
      8. Synchronise

      Known Triggers

      This has been confirmed to occur on upgrade to 5.1.3, and possibly earlier or later versions.

      Case Study

      • These 3 users were duplicated as part of the upgrade to 5.1.3 (some irrelevant columns have been removed for ease of reading):
        contentid contenttype version creator creationdate lastmodifier lastmoddate prevver content_status username
        22217497 USERINFO 1 an.admin 10/05/2013 an.admin 10/05/2013   current USER1
        24248321 USERINFO 1   7/06/2013   7/06/2013   current user1
        917505 USERINFO 3 admin 12/03/2012 user2 14/12/2012   current user2
        24248322 USERINFO 1   7/06/2013   7/06/2013   current USER2
        1409789 USERINFO 1 USER3 29/03/2012 USER3 29/03/2012   current USER3
        24248323 USERINFO 1   7/06/2013   7/06/2013   current user3

      NB: Note the casing above - the 1st and 3rd records were originally uppercase, the second was originally lowercase.

      • All 3 duplicates were created on June 7th. This was in the logs:
        2013-06-07 19:40:39,244 INFO [main] [confluence.upgrade.upgradetask.UserIndexingUpgradeTask] doUpgrade Found 3 usernames that need PersonalInformation created.
        
      • I originally suspected that the casing of the usernames may have changed, but the users are currently in the original casing in cwd_user (upperfor USER1 and USER3, and lower for user2).
      • There was a warning regarding a case change for user2, going back as far as October 2012 (found in old support case) :
        2013-06-10 09:22:11,856 WARN [http-8080-40] [atlassian.crowd.directory.DelegatedAuthenticationDirectory] updateLdapUser remote username [ USER2 ] casing differs from local username [ user2 ]. User details will be kept updated, but the username cannot be updated
        
      • All 3 users are LDAP user accounts. This is a Delegated Authentication directory, and not a synchronising Connector type.

      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 c.username
            FROM CONTENT c
            WHERE
              contenttype LIKE 'USERINFO'
              AND prevver is null
                GROUP BY c.username
                HAVING count(c.username)>1;
      

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

      Work Around

      1. Stop Confluence
      2. Make a full database and file system backup
      3. Run this command to find the usernames of affected users - ensure these match what you are seeing in the People Directory before deleting
        // FOR CONFLUENCE 5.3 AND ABOVE
        SELECT m.username FROM content c
         JOIN user_mapping m ON c.username = m.user_key
          WHERE c.username IN
            (SELECT c.username
              FROM CONTENT c
              WHERE
                contenttype LIKE 'USERINFO'
                AND prevver is null
                  GROUP BY c.username
                  HAVING count(c.username)>1)
            GROUP BY m.username;
        
        // FOR CONFLUENCE 5.2 AND BELOW
        SELECT c.username FROM CONTENT c
          WHERE c.username IN
            (SELECT c.username
              FROM CONTENT c
              WHERE
                contenttype LIKE 'USERINFO'
                AND prevver is null
                  GROUP BY c.username
                  HAVING count(c.username)>1)
            GROUP BY c.username;
        
      4. Run this command to delete the duplicate entries (all versions). The row with the higher contentid will be deleted.
        DELETE FROM CONTENT
        WHERE contentid IN
            (SELECT DISTINCT c1.contentid AS "pageid"
             FROM CONTENT c1
             JOIN CONTENT c2 ON c1.username = c2.username
             WHERE c1.contenttype = 'USERINFO'
             AND c2.contenttype = 'USERINFO'
             AND c1.prevver is null 
             AND c2.prevver is null 
             AND c1.contentid > c2.contentid);
      5. Restart Confluence.
      6. If you are still experiencing problems, have a look at CONF-36018 as well.

      NB: If you have any trouble running the delete query because of foreign key constraints on the ATTACHMENT or CONTENT tables, do not remove the entries from that table. Instead, please get in touch with Support and we can help you remove the duplicates, and update the queries to be more robust.

      Attachments

        Issue Links

          Activity

            People

              fxu Feng Xu (Inactive)
              dunterwurzacher Denise Unterwurzacher [Atlassian]
              Votes:
              50 Vote for this issue
              Watchers:
              95 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: