Jira has a pretty limited support for breaking issues down into smaller components. Rather than support arbitrary hierarchies, if you want more levels of hierarchy, you need to buy plugins:
|Jira Core||Jira Software||Jira Portfolio|
|Levels of structure|
Scenario: Jira Portfolio comes and goes
Let's imagine that, feeling the need for higher-level structure, your company forked out for Jira Portfolio and grouped Epics into Initiatives. For instance, a bunch of IT-related Epics are grouped into a Refine IT Processes and Procedures Initiative:
An 'Initiative' is modelled in Jira as just another issue type, so we can view our Initiative as an issue, and see its child Epics:
Then corporate sloth reasserts itself, and the elaborate high-level structures fall into disrepair. Eventually the license for Portfolio lapses, or Portfolio is disabled.
But that's okay, because at least we still have our Jira Initiatives with linked Epics, right? After Portfolio's license lapsed, lets look again at our Initiative issue:
Oops. Initiatives lose their Child issues section as soon as Portfolio isn't validly licensed.
Where did our Initiative's child links go?
All the initial evidence suggests that the Initiative's Child links should have been preserved:
- the 'Child issues' section looks very similar to a standard Issue links section on issues
there is even a special Portfolio link type in the database
and sometimes you will actually see
issuelinks of this Portfolio link type:
..despite all this, the Child issues section is not done with the standard
issuelink table. I suspect that it was at first, but then the developers hit some limitation and moved on.
Querying JSON in the entity_property table
The Child issues relations are in fact stored in the
entity_property table. Specifically, as
entity_property entries on the child issues, e.g. IT-115:
To find all parent-child relations, we can use Postgres' JSON support:
This information was inferred by the eternally useful technique of using database diffs to see what JIRA is doing.
Restoring the links
So we have found where Portfolio stores the Initiative's links: in
entity_property . That is nice, but we do actually want issue links back in
issuelink where they belong. Let's fix this with some SQL.
Database surgery ahead! Make sure you understand what is going on, and take a backup beforehand.
First, we need a link type. Well, we already have one – it's just invisible because of that custom
To make this link type 'normal' just remove the pstyle:
Next we need to create a bunch of
issuelink records, one for each of those parent relations stored in JSON in
issuelink table looks like this:
So we just need to insert the parent-child IDs we obtained from
Now for a few tedious details. You'll note the
id field - we'll need to create our new records with monotonically increasing values. Furthermore, Jira's crusty database library Ofbiz tracks the maximum ID of each other table's ID in its
sequence_value_item table, to the nearest 10:
So our job is to calculate new IDs, then update
Here is some SQL that does the job, including fixing the
issuelinktype if you hadn't already done so above.
After running the above, you will have to restart Jira for the changes to take effect.
After the restart, Initiative issues will have a new is parent of link section, duplicating the links seen in the Portfolio-generated Child issues section:
then when you disable Portfolio, the Child issues section disappears, and you are where you want to be.