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' );

 

Screen Shot 2024-02-27 at 17.08.46.png

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.