Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-34473

Deleted users still active on watcher's list for Confluence pages - show as an Unknown User and Cannot Be Deleted.

XMLWordPrintable

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

      Also affects the current version of Confluence in all Cloud instances.

      Steps to Reproduce in Self Hosted Confluence instances using LDAP:

      1. Add an LDAP user as a watcher to a page
      2. Remove the user from LDAP
      3. Sync the LDAP directory in Confluence
      4. View the page's Manage Watchers
      5. Try to remove the Unknown User
      6. Reload the page, and notice that the Unknown User still appears there:

      Workaround

      As a space administrator, remove the invalid user from the watchers list.

      For a specific user

      If the user disappears and reappears then do:

      1. Find the user:
        SELECT username FROM user_mapping WHERE user_key IN (SELECT username FROM notifications WHERE contentid = <PAGEID>);

        PAGEID can be determined by going to Ellipsis button > Page information and grabbing the last number in the Browser URL.

      2. Get your user_key by selecting the user from the previous query and searching with:
        SELECT user_key FROM user_mapping WHERE username = '<YOUR_USERNAME_HERE>';

        This will also fix the problem for any other pages that have that deleted user as a watcher

      3. Backup Database
      4. Delete all notifications for the invalid user:
        DELETE FROM notifications WHERE username = '<YOUR_USER_KEY_HERE>';

      For any invalid/deleted user regardless of the pages they're watching

      1. Find the invalid user list (this is to confirm that the listed users are actually invalid, before removing them):
        SELECT n.notificationid AS "Notification ID"
             , s.spacename      AS "Space Name"
             , n.contentid      AS "Page ID"
             , c.title          AS "Page Title"
             , um.username      AS "Invalid User Name"
             , um.user_key      AS "Invalid User Key"
          FROM notifications n
         INNER JOIN user_mapping um ON (n.username = um.user_key)
         INNER JOIN content c ON (n.contentid = c.contentid)
         INNER JOIN spaces s  ON (c.spaceid = s.spaceid)
         WHERE um.lower_username NOT IN ( SELECT lower_user_name 
                                            FROM cwd_user );
        
      2. Backup Database
        Delete all notifications for the invalid users:
        DELETE 
           FROM notifications 
          WHERE notificationid IN ( SELECT notificationid
                                      FROM notifications n
                                     INNER JOIN user_mapping um ON (n.username = um.user_key)
                                     INNER JOIN content c ON (n.contentid = c.contentid)
                                     INNER JOIN spaces s  ON (c.spaceid = s.spaceid)
                                     WHERE um.lower_username NOT IN ( SELECT lower_user_name 
                                                                        FROM cwd_user ) );
        

      For space watchers

      1. Find the invalid users:
        select * from notifications WHERE username in (select user_key from user_mapping where lower_username NOT IN ( SELECT lower_user_name FROM cwd_user ));
        
      2. Delete all notifications for invalid users:
         delete from notifications WHERE username in (select user_key from user_mapping where lower_username NOT IN ( SELECT lower_user_name FROM cwd_user ));
        

      Confirm the workaround worked

      1. Reload one of the affected pages and check its Watchers
      2. If the affected page still shows the issue then Flush Cache (Confluence Administration > General Configuration > Cache Management > Flush all)

              346a06beb37e ajoshi7 (Inactive)
              fsim Foo Sim (Inactive)
              Votes:
              101 Vote for this issue
              Watchers:
              97 Start watching this issue

                Created:
                Updated:
                Resolved: