Watch count is out of sync - unable to remove watchers

Summary

The count of jiraissue watches not always match the real amount of users watching the issue. Performing tasks like Delete will fail if the count is 0 and listings of Watches in the issue Search will be incorrect.

Environment

JIRA

Steps to Reproduce

1. Create a mail handler with the option to include CC field as watchers
2. Send an email to the mail handler including valid user email addresses in the CC field
   Expected Result: CC users are added as watchers and jiraissue.watches = amount of email addresses in the email (To + CC)
   Actual Result: CC users are added as watchers but the jiraissue.watches has 1 less count and is out of sync.
   There could be more causes for the problem.

To find if an instance is affected, run the following query in the database that returns the amount of issues affected.

select count(*)
from (select count(*) as real_watchers_count, i.watches as issue_watches, i.id as issue_id from userassociation a left join jiraissue i on a.sink_node_id = i.id where association_type like 'WatchIssue' group by a.sink_node_id, i.id, i.watches) tt1 
where tt1.real_watchers_count > tt1.issue_watches;

To find which specific issues are affected:

select tt1.real_watchers_count, tt1.issue_watches, tt1.issue_id, (p.pkey || '-' ||  i.issuenum) AS issue, i.summary
from jiraissue i inner join project p on i.project = p.id
inner join (select count(*) as real_watchers_count, i.watches as issue_watches, i.id as issue_id from userassociation a join jiraissue i on a.sink_node_id = i.id where association_type like 'WatchIssue' group by a.sink_node_id, i.id, i.watches) tt1 on tt1.issue_id = i.id
where tt1.real_watchers_count > tt1.issue_watches;

Notes

Previously, Cloning issues was causing this problem: JRA-34394 but it was fixed and an upgrade task ran to correct all counts out of sync.

Workaround

This query syncs them back:

UPDATE jiraissue i set watches = (select count(*) from userassociation where association_type like 'WatchIssue' and sink_node_id = i.id);

on sites with many thousands of issues, this query may take a long time to complete. Do a DB backup before running.