Description

I have noticed that in one of our JIRA instances with 70k+ issues, a lot of them have wrong count of watchers which leads to wrong search results (we prioritize some items by amount of watchers because we have voting disabled).

I have verified that the watches count differs from the watchissue user associations in the database and in an XML export. Even after re-importing the XML data, the count gets out of sync with the actual amount of watchers.

After inspecting the JIRA source, the code seems quite stable except for some minor flaws.

I then found that our extensive use of the clone issue operation might have caused the issue and I was able to reproduce this on a JIRA 6.0.6 instance:

Besides fixing the bug in the clone issue operation, it would be good to provide a fix for this (probably a SQL update statement that counts the actual user watchissue associations and updates the count).

Thanks

Steps to produce:

1. Create a new issue and make sure you are added as a watcher to the issue (you are the only watcher for this ticket)
2. Clone this issue to another issue and make sure that you are added as a watcher for the new cloned issue (you are the only watcher for this ticket)
3. Go to the Issue Navigator and add the column Watchers to the issue navigator
4. You will notice that for the cloned issue, there will be two watchers, and when you try to open the issue, you will only see one user.

Workaround

You can conduct either of the following for workaround:

• Update 'watches' column of 'jiraissue' table and run re-index
• Upgrade JIRA to 6.3.3 or later, which automatically corrects the unsyncronized watchers number

Workaround 2:

If even after the upgrade some issues are still showing the wrong number:

• Run the following SQL query to see if this applies to your case (if it returns any results, you can proceed):

  select * from jiraissue where id not in (select sink_node_id from userassociation where association_type = 'WatchIssue') and watches > 0;
  

• Create a backup from your JIRA, just in case ;
• Stop your JIRA;
• Run the following SQL query:

  update jiraissue set watches = 0 where id not in (select sink_node_id from userassociation where association_type = 'WatchIssue') and watches > 0;
  

• Start your JIRA;
• Perform a full re-index.

Notes

This upgrade task can take quite some time, especially depending upon the type of database used and also the issue / watch count. In some cases it takes 30+ minutes.