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’:

CODE

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:

CODE

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:

CODE

-- 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.