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.

      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)

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

            Osimar M. (Osi) [Atlassian Support] made changes -
            Remote Link Original: This issue links to "Page (Confluence)" [ 513138 ]
            Cihan made changes -
            Description Original: {panel:bgColor=#e7f4fa}
            *NOTE:* This bug report is for *Confluence Server*. Using *Confluence Cloud*? [See the corresponding bug report|http://jira.atlassian.com/browse/CONFCLOUD-34473].
            {panel}
            h3. Also affects the current version of Confluence in all Cloud instances.
            h3. Steps to Reproduce in Self Hosted Confluence instances using LDAP:
             # Add an LDAP user as a watcher to a page
             # Remove the user from LDAP
             # Sync the LDAP directory in Confluence
             # View the page's *Manage Watchers*
             # Try to remove the Unknown User
             # Reload the page, and notice that the Unknown User still appears there:
             !watcher.jpg!

            h3. Workaround

            As a space administrator, remove the invalid user from the watchers list.
            h4. +*For a specific user*+
            If the user disappears and reappears then do:
            # Find the user:
            {code:sql}
            SELECT username FROM user_mapping WHERE user_key IN (SELECT username FROM notifications WHERE contentid = <PAGEID>);{code}
            PAGEID can be determined by going to *Ellipsis button > Page information* and grabbing the last number in the Browser URL.
            # Get your user_key by selecting the user from the previous query and searching with:
            {code:sql}
            SELECT user_key FROM user_mapping WHERE username = '<YOUR_USERNAME_HERE>';{code}
            This will also fix the problem for any other pages that have that deleted user as a watcher
            # +*Backup Database*+
            # Delete all notifications for the invalid user:
            {code:sql}
            DELETE FROM notifications WHERE username = '<YOUR_USER_KEY_HERE>';{code}

            h4. +For any invalid/deleted user regardless of the pages they're watching+
            # Find the invalid user list (this is to confirm that the listed users are actually invalid, before removing them):
            {code:sql}
            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 );
            {code}
            # +*Backup Database*+
            # Delete all notifications for the invalid users:
            {code:sql}
            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 ) );
            {code}

            h4. +Confirm the workaround worked+
            # Reload one of the affected pages and check its Watchers
            # If the affected page still shows the issue then Flush Cache (*Confluence Administration > General Configuration > Cache Management > Flush all*)
            New: {panel:bgColor=#e7f4fa}
            *NOTE:* This bug report is for {*}Confluence Server{*}. Using {*}Confluence Cloud{*}? [See the corresponding bug report|http://jira.atlassian.com/browse/CONFCLOUD-34473].
            {panel}
            h3. Also affects the current version of Confluence in all Cloud instances.
            h3. Steps to Reproduce in Self Hosted Confluence instances using LDAP:
             # Add an LDAP user as a watcher to a page
             # Remove the user from LDAP
             # Sync the LDAP directory in Confluence
             # View the page's *Manage Watchers*
             # Try to remove the Unknown User
             # Reload the page, and notice that the Unknown User still appears there:
            !watcher.jpg!

            h3. Workaround

            As a space administrator, remove the invalid user from the watchers list.
            h4. +*For a specific user*+
            If the user disappears and reappears then do:
             # Find the user:
            {code:sql}
            SELECT username FROM user_mapping WHERE user_key IN (SELECT username FROM notifications WHERE contentid = <PAGEID>);{code}
            PAGEID can be determined by going to *Ellipsis button > Page information* and grabbing the last number in the Browser URL.
             # Get your user_key by selecting the user from the previous query and searching with:
            {code:sql}
            SELECT user_key FROM user_mapping WHERE username = '<YOUR_USERNAME_HERE>';{code}
            This will also fix the problem for any other pages that have that deleted user as a watcher
             # +*Backup Database*+
             # Delete all notifications for the invalid user:
            {code:sql}
            DELETE FROM notifications WHERE username = '<YOUR_USER_KEY_HERE>';{code}

            h4. +For any invalid/deleted user regardless of the pages they're watching+
             # Find the invalid user list (this is to confirm that the listed users are actually invalid, before removing them):
            {code:sql}
            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 );
            {code}
             # +*Backup Database*+
            Delete all notifications for the invalid users:
            {code:sql}
            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 ) );
            {code}

            h4. +*For space watchers*+
             # Find the invalid users:
            {code:sql}
            select * from notifications WHERE username in (select user_key from user_mapping where lower_username NOT IN ( SELECT lower_user_name FROM cwd_user ));
            {code}
             # Delete all notifications for invalid users:
            {code:sql}
             delete from notifications WHERE username in (select user_key from user_mapping where lower_username NOT IN ( SELECT lower_user_name FROM cwd_user ));
            {code}

            h4. +Confirm the workaround worked+
             # Reload one of the affected pages and check its Watchers
             # If the affected page still shows the issue then Flush Cache ({*}Confluence Administration > General Configuration > Cache Management > Flush all{*})

            rmontag-ap added a comment -

            The suggested workaround does delete all unknown users of the pages, but how to delete the unknown users listed in "Watching this space"? They share the same problem that they cannot be deleted.

            rmontag-ap added a comment - The suggested workaround does delete all unknown users of the pages, but how to delete the unknown users listed in "Watching this space"? They share the same problem that they cannot be deleted.
            Hamza Tila made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 664027 ]

            Is this going to be addressed in a future 7.13 LTS release?

            Justin Andrews added a comment - Is this going to be addressed in a future 7.13 LTS release?
            SET Analytics Bot made changes -
            UIS Original: 41 New: 42

            A fix for this issue is available in Confluence Server and Data Center 7.17.0.
            Upgrade now or check out the Release Notes to see what other issues are resolved.

            James Whitehead added a comment - A fix for this issue is available in Confluence Server and Data Center 7.17.0. Upgrade now or check out the Release Notes to see what other issues are resolved.
            James Whitehead made changes -
            Resolution New: Fixed [ 1 ]
            Status Original: Waiting for Release [ 12075 ] New: Closed [ 6 ]
            SET Analytics Bot made changes -
            UIS Original: 43 New: 41
            SET Analytics Bot made changes -
            UIS Original: 49 New: 43

              346a06beb37e ajoshi7 (Inactive)
              fsim Foo Sim (Inactive)
              Affected customers:
              101 This affects my team
              Watchers:
              98 Start watching this issue

                Created:
                Updated:
                Resolved: