Summary

Jira notifications (batched or non-batched) are not sent to watchers of some tickets which were imported from another Jira instance using a 3rd party add-on.

Environment

Any Jira 7.x or 8.x version.

Diagnosis

• The tickets were imported from another Jira instance, using a 3rd party add-on, for example Configuration Manager for Jira (CMJ)
• Not all imported tickets are impacted, but only specific ones
• According to the notification scheme and permission scheme, the impacted watchers should receive notifications from the ticket, and have the permission to access the ticket. This can be confirmed:
  • Using the Notification helper in ⚙ > System > Notification helper, using the impacted ticket, an impacted user, and an event for which the watcher was supposed to receive a notifciation
  • Using the Permission helper in ⚙ > System > Permission helper, using the impacted ticket, an impacted user, and the Browse Projects permission

• Running the following SQL query reveals that there are invalid users who are part of the watcher's list. Invalid user = "user which user key does not exist in the app_user table". 

  •  Make sure to replace ABC and 123 with the project key and issue number. For example, if the issue key is TEST-1520, then replace ABC with TEST and 123 with 1520:

  •  If the query below returns any row, then this KB article applies to your situation:

    select *
    from userassociation
    where association_type = 'WatchIssue'
    AND source_name not in (select user_key from app_user)
    AND sink_node_id = (
    	select id from jiraissue
    	where project in (select id from project where pkey = 'ABC')
    	and issuenum in (123)
    )

• If you are using the 3rd party add-on ScriptRunner for Jira, you may also follow the steps below to diagnose this issue (instead of using the SQL query above):
  • Go to ⚙ > Manage Apps > Script Runner > Console

  • Run the following script in the console and check the log tab at the bottom after replacing "ABC-123" with an example of affected issue key:

    import com.atlassian.jira.component.ComponentAccessor
    import com.atlassian.jira.issue.IssueManager
    import com.atlassian.jira.issue.Issue
    
    def testIssueKey = "ABC-123"
    
    IssueManager issueManager = ComponentAccessor.getComponentOfType(IssueManager.class)
    log.warn("issueManager: "  + issueManager)
    
    
    Issue issue = issueManager.getIssueObject(testIssueKey)
    issueManager.getWatchersFor(issue).each{watcher -> 
    	log.warn("key           : " + watcher.getKey())
        log.warn("username      : " + watcher.getUsername())
        log.warn("name          : " + watcher.getName())
        log.warn("directoryId   : " + watcher.getDirectoryId())
        log.warn("isActive      : " + watcher.isActive())
        log.warn("emailAddress  : " + watcher.getEmailAddress())
        log.warn("displayName   : " + watcher.getDisplayName())
        log.warn("directoryUser : " + watcher.getDirectoryUser())
        log.warn("------------------------------------")
        
    };

  • If you find any result showing a user with a directory id of "-1" as shown in the example below, then this KB article applies to your situation:

    2021-12-13 15:58:05,844 WARN [runner.ScriptBindingsManager]: key           : julien2
    2021-12-13 15:58:05,844 WARN [runner.ScriptBindingsManager]: username      : julien2
    2021-12-13 15:58:05,844 WARN [runner.ScriptBindingsManager]: name          : julien2
    2021-12-13 15:58:05,844 WARN [runner.ScriptBindingsManager]: directoryId   : -1
    2021-12-13 15:58:05,845 WARN [runner.ScriptBindingsManager]: isActive      : false
    2021-12-13 15:58:05,845 WARN [runner.ScriptBindingsManager]: emailAddress  : ?
    2021-12-13 15:58:05,845 WARN [runner.ScriptBindingsManager]: displayName   : julien2
    2021-12-13 15:58:05,845 WARN [runner.ScriptBindingsManager]: directoryUser : com.atlassian.crowd.embedded.impl.ImmutableUser@eeb83c7f

Cause

The userassociation table which has the list of watchers for each Jira ticket contains the keys of users which don't exist in the Jira application. Since these users don't exist, when the Jira notification logic that tries to decide which users should receive a notification from the ticket breaks, and none of the watcher of the ticket receives a notification.

The exact root cause of the data inconsistency is unfortunately unknown. Since such issue has not been reported using Jira's native project import feature, but instead using a 3rd party add-on, we are suspecting that in some rare cases, the 3rd party add-on might import tickets and add invalid users to the watcher list of these tickets.

Solution

In order to fix all the watcher's data inconsistencies inside the userassociation table, we will need to delete all the invalid rows from this table, by following the steps below.  Please make sure to backup your Jira Database using your Database native backup tool before running the DELETE query below.

1. Stop the Jira application
2.  Backup your Jira Database

3. Run the DELETE query below

   DELETE from userassociation where association_type = 'WatchIssue' AND source_name not in (select user_key from app_user);

4. Start the Jira application