Deleting the results of an incorrect import in Jira
Situation: Use this page when you have performed an import into Jira, and have made a mistake in the mappings, so that requirements from Confluence are mapped with the wrong Jira issues.
Theoretical solution
When you import a project or an instance, Requirement Yogi automatically deletes all links from that project (or from the whole instance, if you are importing an entire instance), to recreate them when you reimport.
It doesn’t work well if you have previously imported data with wrong issue IDs. In that situation, the requirements are associated with issues outside the imported project.
Before you reimport the second time with the correct Jira issue IDs, you will want to remove the extraneous links.
How to detect the links to remove
You can use several strategies:
IDs are created sequentially. The import will have inserted those links in bulk. You could go to the table AO_42D05A_DBISSUELINK, look at the data, and remove all of those inserted by the import.
You could check all Issue IDs of the original import, and assume the links on those Jira issues are all wrong. This assumes that users have not legitimately created links on those issues, because you are at risk of deleting legitimate links.
We’ll dive into the second one.
The data model
The data model is described here: https://requirementyogi.atlassian.net/wiki/spaces/RY/pages/1891532801/Database+schema#Jira-database-schema
It can be summarized as:
JIRAISSUE → AO_42D05A_DBISSUELINK → AO_42D05A_DBREMOTEREQUIREMENT → Confluence,
JIRAISSUE → AO_42D05A_DBAUDITTRAIL
“ → “ represent foreign keys towards child records.
How to list, then delete, the links
This query will list all links which may or may not have been created by the last import named ‘KEY1’:
SELECT L.ID as ISSUELINK_ID,
L.ISSUEID as LINK_ISSUE_ID,
L.ISSUEKEY as EXPECTED_ISSUE_KEY,
P.PKEY || '-' || J.ISSUENUM as REAL_ISSUE_KEY,
L.RELATIONSHIP,
'--',
R.APPLINK,
R.SPACEKEY,
R.KEY,
R.BASELINE
FROM AO_42D05A_DBISSUELINK as L
LEFT JOIN AO_42D05A_DBREMOTEREQUIREMENT R ON R.ID = L.REQUIREMENT_ID
LEFT JOIN JIRAISSUE J ON J.ID = L.ISSUEID
LEFT JOIN PROJECT P ON P.ID = J.PROJECT
WHERE L.ISSUEID IN (
-- Subquery with criteria to customize
SELECT DESTINATIONVALUE
FROM AO_42D05A_DBBACKUPMAPPING M
WHERE TYPE = 'ISSUE'
AND BACKUPKEY = 'KEY1'
);
However , it can’t be assumed that all the links of 10100, for example, are wrong. Maybe users created them on purpose. It is the responsibility of the administrator who deletes the data to first check that those links are all illegitimate. Tune the “Subquery with criteria to customize” until the criteria are correct.
Once the criteria are correct, you can proceed to delete the links:
DELETE FROM AO_42D05A_DBISSUELINK WHERE ISSUEID IN (
SELECT DESTINATIONVALUE
FROM AO_42D05A_DBBACKUPMAPPING M
-- Don't forget to add the criteria from the previous subquery
-- Those criteria assume that all links on those issues are wrong
WHERE TYPE = 'ISSUE'
AND BACKUPKEY = 'KEY1'
);
You can also entirely delete the history of those issues:
-- Deletes the history for a given issue id
DELETE FROM AO_42D05A_DBAUDITTRAIL WHERE ISSUEID IN (
SELECT DESTINATIONVALUE
FROM AO_42D05A_DBBACKUPMAPPING M
-- Don't forget to add the criteria from the previous subquery
-- Those criteria assume that all history on those issues is wrong
WHERE TYPE = 'ISSUE'
AND BACKUPKEY = 'KEY1'
);
There is no need to delete the imported requirements, since they were not wrong when they were imported. Besides, the import will delete orphan requirements.
After this, you can perform the reimport and check that the new data is correct.