Cleaning up links to non-existing Jira issues

Various “reindex” tools can clean up data. However, since the connection between two systems like Confluence and Jira is not a tight coupling, the software doesn’t try to aggressively delete links to non-existing Jira issues. There are too many legitimate scenarios where things could go wrong if we automated the deletion of links: The queue user not having access to permissions on real Jira issues; Jira being unreachable; Issues having been transferred to another system but still existing, etc.

There are situations which will be fixed in the future, where links should have been deleted, such as deleting an entire Jira project. As of 3.8, administrators need to do it manually.

The page below explains how to delete links to non-existing Jira project.

The data model

As shown in Database schema, the tables can be summarized as:

AO_32F7CE_DBREQUIREMENT → AO_32F7CE_DBLINK* → Actual Jira issues.

* with TYPE='JIRA' AND ORIGIN = false

SQL queries

select R.ID, R.SPACEKEY, R.KEY, R.BASELINE, R.STATUS,
       L."ID" as LINKID, L."TYPE", L."APP", L."DOCUMENTID", L."RELATIONSHIP"
from "AO_32F7CE_DBREQUIREMENT" as R
join "AO_32F7CE_DBLINK" as L on L."PARENT_ID" = R."ID" and L."TYPE" = 'JIRA'
where
DOCUMENTID like 'YOGI-%' -- The issue key
AND APP = '9f2d636e-c842-3388-8a66-17c1b951dd45' -- The applink id
;

If you can only see links which should be deleted, then you can proceed to the deletion:

DELETE FORM "AO_32F7CE_DBLINK"
WHERE "TYPE" = 'JIRA'
-- Don't forget to add your own criteria, if necessary
AND DOCUMENTID LIKE 'YOGI-%'
AND APP = '9f2d636e-c842-3388-8a66-17c1b951dd43'
;