Resolving Duplicate Attachments in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Duplicate attachments in Jira can occur during data migrations from external tools, such as TestRail to Xray, using custom scripts. This issue is characterized by multiple files with the same name within a Jira issue, which can cause errors or migration blocks.
Diagnosis
By Database Query: Run the following SQL query to identify issues with duplicate attachments:
1
2
3
4
5
6
7
SELECT p.pkey AS project_key, ji.issuenum, f.filename, COUNT(f.filename) AS count
FROM fileattachment f
JOIN jiraissue ji ON f.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY p.pkey, ji.issuenum, f.filename
HAVING COUNT(f.filename) > 1
ORDER BY p.pkey, ji.issuenum;Cause
The duplicates are likely a result of the data migration process. During this process, the migration script creates multiple attachments with the same name within the same issue in Jira. This problem is not related to installed plugins or Jira itself but rather the migration script used.
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Backup Database and Filesystem: Ensure you have a recent backup of both your database and filesystem to prevent any data loss during the process.
Execute Query: Run the Next Query according with the database:
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34-- Update the filenames for duplicates WITH DuplicateFiles AS ( SELECT f.id, f.filename, ROW_NUMBER() OVER (PARTITION BY ji.id, f.filename ORDER BY f.id) AS rn FROM fileattachment f JOIN jiraissue ji ON f.issueid = ji.id JOIN project p ON ji.project = p.id GROUP BY ji.id, f.filename, f.id HAVING COUNT(f.filename) > 1 ) UPDATE fileattachment SET filename = ( SELECT CONCAT( LEFT(f.filename, CHAR_LENGTH(f.filename) - CHAR_LENGTH(SUBSTRING_INDEX(f.filename, '.', -1)) - 1), '-', df.rn, '.', SUBSTRING_INDEX(f.filename, '.', -1) ) FROM DuplicateFiles df WHERE df.id = fileattachment.id ) WHERE id IN (SELECT id FROM DuplicateFiles);Oracle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31MERGE INTO fileattachment fa USING ( SELECT f.id, f.filename, ROW_NUMBER() OVER (PARTITION BY ji.id, f.filename ORDER BY f.id) AS rn FROM fileattachment f JOIN jiraissue ji ON f.issueid = ji.id JOIN project p ON ji.project = p.id GROUP BY ji.id, f.filename, f.id HAVING COUNT(f.filename) > 1 ) df ON (fa.id = df.id) WHEN MATCHED THEN UPDATE SET filename = ( CASE WHEN df.rn = 1 THEN df.filename -- Keep the first occurrence unchanged ELSE SUBSTR(df.filename, 1, INSTR(df.filename, '.', -1) - 1) || '-' || df.rn || '.' || SUBSTR(df.filename, INSTR(df.filename, '.', -1) + 1) END ) WHERE df.rn > 1;Note: Involve a DBA to verify and execute this query.
Validate the information:
Testing: After executing the script, test to ensure all attachments are accessible with their new filenames.
Feedback: Ensure that the renaming resolves duplicate issues without impacting other functionalities.
Was this helpful?