Component delete operation leaves orphaned records in nodeassociation table due to non-atomic writes

Summary

Deleting components removes data from component table, but sometimes it doesn't delete corresponding references from nodeassociation table since the operation is not atomic.
In that case, those records become orphans. Later loading all data related to the issue will lead to the error, since the corresponding component doesn't exist.

Steps to Reproduce

There are two separate ways to trigger the problem:

1. A race condition
   • Trigger component deletion from Jira admin UI
   • Now Jira deletes all related nodeassociation entries, followed by the component itself
   • If a user edits an issue and assigns the component being deleted to it after Jira started deleting nodeassociation entries but before it deleted the component, it will result in an orphaned nodeassociation entry
   • The more issues assigned to the component, the higher the chances are for the problem to occur, as deleting nodeassociation entries will take more time.
2. A regression introduced by JRASERVER-10507 in Jira 8.1.0
   • Create a component and assign it to an issue
   • Archive the component
   • Delete the component

Expected Results

Jira doesn't have unreferenced component data in nodeassociation table.

Actual Results

Jira has unreferenced component data in nodeassociation table.

mysql> select * from nodeassociation where SINK_NODE_ENTITY='Component' and SINK_NODE_ID not in (Select ID from component);
+----------------+--------------------+--------------+------------------+------------------+----------+
| SOURCE_NODE_ID | SOURCE_NODE_ENTITY | SINK_NODE_ID | SINK_NODE_ENTITY | ASSOCIATION_TYPE | SEQUENCE |
+----------------+--------------------+--------------+------------------+------------------+----------+
|          10038 | Issue              |        10000 | Component        | IssueComponent   |     NULL |
|          10039 | Issue              |        10002 | Component        | IssueComponent   |     NULL |
|          13886 | Issue              |        10001 | Component        | IssueComponent   |     NULL |
|          10356 | Issue              |        10000 | Component        | IssueComponent   |     NULL |
|          10376 | Issue              |        10002 | Component        | IssueComponent   |     NULL |
|          10377 | Issue              |        10002 | Component        | IssueComponent   |     NULL |
...
27 rows in set (1.05 sec)

Notes

• Additionally, in the log file following errors are recorded:

  2008-09-11 14:26:20,785 http-8443-Processor40 ERROR [jira.issue.statistics.ComponentStatisticsMapper] Indexes may be corrupt - unable to retrieve component with id '10211'.
  2008-09-11 14:27:41,830 http-8443-Processor40 ERROR [jira.issue.statistics.ComponentStatisticsMapper] Indexes may be corrupt - unable to retrieve component with id '10000'.
  2008-09-11 14:27:41,830 http-8443-Processor40 ERROR [jira.issue.statistics.ComponentStatisticsMapper] Indexes may be corrupt - unable to retrieve component with id '10001'.
  2008-09-11 14:27:41,830 http-8443-Processor40 ERROR [jira.issue.statistics.ComponentStatisticsMapper] Indexes may be corrupt - unable to retrieve component with id '10002'.
  2008-09-11 14:27:41,830 http-8443-Processor40 ERROR [jira.issue.statistics.ComponentStatisticsMapper] Indexes may be corrupt - unable to retrieve component with id '10027'. 
  

• • Note that the problem is not related to the Lucene Index.
• We should add an extra check to Integrity Checker that would allow users to find and delete the invalid components. Also, if possible, the delete operation should be improved

Workaround

1. Delete the orphaned rows manually:

   delete from nodeassociation where source_node_entity = 'Issue' and sink_node_entity = 'Component' and sink_node_id not in (select id from component);

1. Reindex Jira (background reindex in Server will do; full lock & reindex in DC). No restart's required.