Attachment link in history redirects back to the wrong attachment after a project import

A link will created under the history tab for the imported attachment which lead to wrong attachment. The situation occurs when user try to move some project from one instance to another.

Steps to reproduce:

1. Attach an attachment 1 to a ticket in instance A. The first attachment id in database will be 10000.
2. Attach an attachment 2 to a ticket in instance B. The first attachment id in database will be 10000 as well.
3. Export instance A as xml backup and project import into instance B.
4. A link will created for attachment 1 in instance B under the history tab. However, when click upon to the link, user will get to attachment 2 instead of attachment 1.

NOTE: Check in the database where the both attachments in change history table still retain the same value which are 10000 respectively. However, the fileattachment table has a different value for both attachments.

A link will created under the history tab for the imported attachment which lead to wrong attachment. The situation occurs when user try to move some project from one instance to another.

Steps to reproduce:

1. Attach an attachment 1 to a ticket in instance A. The first attachment id in database will be 10000.
2. Attach an attachment 2 to a ticket in instance B. The first attachment id in database will be 10000 as well.
3. Export instance A as xml backup and project import into instance B.
4. A link will created for attachment 1 in instance B under the history tab. However, when click upon to the link, user will get to attachment 2 instead of attachment 1.

NOTE: Check in the database where the both attachments in change history table still retain the same value which are 10000 respectively. However, the fileattachment table has a different value for both attachments.

Workaround

PostgreSQL:

1. Backup JIRA (recommended to test in a staging environment)
2. Run the SQL queries below to retrieved the wrong attachment links:

   	select 
   	CI.id as "Changeitem ID",
   	concat(P.pkey,'-',I.issuenum) as "Issue Key", 
   	(select id from fileattachment where filename = CI.newstring) as "Correct Attachment ID",
   	CI.newstring as "Attachment name",
   	CI.newvalue as "Current Attachment ID",
   	A.filename as "Wrong Attachment name"
   	from changeitem CI
   	join fileattachment A on CI.newvalue = cast(A.id as text)
   	join changegroup CG on CI.groupid = CG.id
   	join jiraissue I on I.id = CG.issueid
   	join project P on P.id = I.project
   	where CI.field = 'Attachment'
   	and CG.issueid != A.issueid;
   

3. Run the SQL queries below to update the newvalue column ID to the correct Attachment ID:

   update changeitem set newvalue = <correct_attachment_ID> where id = <changeitem_ID>;
   

4. Restart JIRA.

Oracle:

1. Backup JIRA (recommended to test in a staging environment)
2. Run the SQL query below:

   MERGE INTO changeitem ci
      USING ((select * from (select concat(P.pkey,concat('-',I.issuenum)) as ISSUENUM, ci.id,A.id as "WRONG ATT ID",
   (select LISTAGG(A1.id,',') WITHIN GROUP (ORDER BY A1.id DESC) from fileattachment A1 where to_char(A1.created,'dd-mon-yyyy hh')=to_char(CG.created,'dd-mon-yyyy hh') and A1.issueid=CG.issueid and A1.filename=to_char(CI.newstring)) as "CORRECT ATT ID", 
   CI.newstring as "ATT NAME", to_char(CG.created, 'dd-mon-yyyy hh:mi:ss PM') as "CREATED"
   from changeitem CI
   join fileattachment A on to_char(CI.newvalue) = to_char(A.id)
   join changegroup CG on CI.groupid = CG.id
   join jiraissue I on I.id = CG.issueid
   join project P on P.id = I.project
   where CI.field = 'Attachment'
   and CG.issueid <> A.issueid 
   order by ISSUENUM desc))) wids
      ON (CI.field = 'Attachment' and ci.id=wids.id)
      WHEN MATCHED THEN UPDATE SET CI.newvalue = wids."CORRECT ATT ID"
   

3. Restart JIRA.