Detect and change the comment id's of Jira issue comment links

Issue Summary

After migrating from Jira Server/DC to Cloud, links in the format:
https://jira.example.com/browse/PROJ-123?focusedCommentId=33785421&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-33785421
will break as the comment id's are not mapped.

If you have hardcoded links to comments, and you migrate over to the Cloud those links will be broken.

Scenario

• Jira Server/DC -> Jira Cloud migration
• Jira Cloud -> Jira Cloud migration

Affects products

• Jira, where an issue description, comment or custom field contains a link to a direct Jira comment
• Confluence, where body content contains a link to a direct Jira comment

Steps to Reproduce

Jira server

1. Create a Jira issue
2. Add a comment
3. Copy the link using the link/chain icon when hovering over the comment, or hover over the time date stamp of the comment (newer versions of Jira). It will be in format:
   https://jira.example.com/browse/PROJ-123?focusedCommentId=33785421&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-33785421
4. Paste this content into:
   1. Jira
      1. issue description
      2. issue comment
      3. issue text custom field
   2. Confluence
      1. page
      2. comment
      3. blog post
5. (assuming link fixing has been attempted) Copy the link, it should be something like this for Cloud https://example.atlassian.net/browse/PROJ-123?focusedCommentId=33785421&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-33785421
6. Click on link in Jira cloud -> Points back to Jira server

Even if Atlassian support run scripts to fix links, comment id's are not mapped. Fixes will take user to the context of the issue, but not to the specific comment.

Expected Results

Links would be updated to the exact comment as intended by content author.

Actual Results

Without link fixing - users are taken back to Jira server (if still active)
With link fixing - users are taken to the correct issue, but not to the specific comment.

Notes

• Comment mapping is onerous as there may be millions upon millions of comments.
• Unlike other mappings, mapping based purely on content is not viable, as an automated comment can produce the same text many times (including the same issue), and it will be necessary to map the right comment to the right project, issue, user AND timestamp amongst other signals.

Scope

To determine the approximate size of the issue, the following SQL query (PostgreSQL) can be run:

SELECT tmp.pkey, tmp.location, COUNT(1)
FROM (
  SELECT
      p.pkey, regexp_matches(i.description, '[\s,]([A-Z][A-Z]+-\d+)[\s,\.]', 'g')
      , 'description' AS location
  FROM jiraissue i
      JOIN project p ON p.id = i.project
  GROUP BY p.pkey, regexp_matches(i.description, '[\s,]([A-Z][A-Z]+-\d+)[\s,\.]', 'g')
  UNION ALL
  SELECT
      p.pkey, regexp_matches(a.actionbody, '[\s,]([A-Z][A-Z]+-\d+)[\s,\.]', 'g')
      , 'comment' AS location
  FROM jiraaction a
      JOIN jiraissue i ON i.id = a.issueid
          JOIN project p ON p.id = i.project
  GROUP BY p.pkey, regexp_matches(a.actionbody, '[\s,]([A-Z][A-Z]+-\d+)[\s,\.]', 'g')
) AS tmp
GROUP BY tmp.pkey, tmp.location
ORDER BY count DESC; 

Workaround

We don't have a fix for these links yet.