Users are duplicated when adding 'Participants' in the new Retrospective page template, and multiple USERINFO records in the "content" table of the database for these users are found

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. Click on the '+' icon to create a new page.
2. Select the 'Retrospective Template'.
3. In the 'Participants' field, while adding some users, search returns duplicated records.
4. Upon checking the database, duplicate USERINFO records in the 'content' table will be available:

   SELECT c.username
         FROM CONTENT c
         WHERE
           contenttype LIKE 'USERINFO'
           AND prevver is null
             GROUP BY c.username
             HAVING count(c.username)>1;
   

Known Triggers

This has been confirmed to occur on upgrade to 5.1.3, and possibly earlier or later versions. Carried over to cloud when migrated from server

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. Make a site backup, database backup.
2. Run this SQL 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;
   

3. Run this command to delete the duplicate entries (all versions). The row with the higher contentid will be deleted.

   DELETE FROM CONTENT
   WHERE prevver 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);
   
   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);

4. Run a Confluence site content re-index.