How to restore links from Initiatives to Epics, after disabling Jira Portfolio. |
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 | Issues Sub-tasks | Epics Issues (Stories) Sub-tasks | Programs Initiatives Epics Issues Sub-tasks |
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.
All the initial evidence suggests that the Initiative's Child links should have been preserved:
there is even a special Portfolio link type in the database
jira=> select * From issuelinktype where pstyle ='jira_jpos_parent_child'; ┌───────┬───────────────────┬─────────────┬──────────────┬────────────────────────┐ │ id │ linkname │ inward │ outward │ pstyle │ ├───────┼───────────────────┼─────────────┼──────────────┼────────────────────────┤ │ 10830 │ Parent-Child Link │ is child of │ is parent of │ jira_jpos_parent_child │ └───────┴───────────────────┴─────────────┴──────────────┴────────────────────────┘ (1 row) |
and sometimes you will actually see issuelinks of this Portfolio link type:
jira=> select count(*) from issuelink where linktype in (select id from issuelinktype where pstyle='jira_jpos_parent_child'); ┌───────┐ │ count │ ├───────┤ │ 27 │ └───────┘ (1 row) |
..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.
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:
jira=> select * from entity_property where entity_name='IssueProperty' and entity_id=(select jiraissue.id from jiraissue JOIN project ON project.id=jiraissue.project WHERE project.pkey='IT' and issuenum=115);
┌─[ RECORD 1 ]─┬───────────────────────────────────────┐
│ id │ 26614 │
│ entity_name │ IssueProperty │
│ entity_id │ 207404 │
│ property_key │ jpo-issue-properties │
│ created │ 2017-06-27 16:31:25.103+00 │
│ updated │ 2017-06-27 18:50:42.744+00 │
│ json_value │ {"team_id":"26","parent_id":"207453"} │
└──────────────┴───────────────────────────────────────┘ |
To find all parent-child relations, we can use Postgres' JSON support:
jira=> select entity_id AS child_id, (json_value::json->>'parent_id')::numeric AS parent_id from entity_property where entity_name='IssueProperty' and property_key='jpo-issue-properties' limit 10; ┌──────────┬───────────┐ │ child_id │ parent_id │ ├──────────┼───────────┤ │ 271643 │ ␀ │ │ 271621 │ ␀ │ │ 271611 │ ␀ │ │ 270022 │ 246074 │ │ 270021 │ 270020 │ │ 246076 │ 270020 │ │ 267996 │ 267715 │ │ 267931 │ 261600 │ │ 267894 │ 267737 │ │ 267807 │ 267731 │ └──────────┴───────────┘ (10 rows) |
This information was inferred by the eternally useful technique of using database diffs to see what JIRA is doing.
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 pstyle :
jira=> select * From issuelinktype where pstyle ='jira_jpos_parent_child'; ┌───────┬───────────────────┬─────────────┬──────────────┬────────────────────────┐ │ id │ linkname │ inward │ outward │ pstyle │ ├───────┼───────────────────┼─────────────┼──────────────┼────────────────────────┤ │ 10830 │ Parent-Child Link │ is child of │ is parent of │ jira_jpos_parent_child │ └───────┴───────────────────┴─────────────┴──────────────┴────────────────────────┘ |
To make this link type 'normal' just remove the pstyle:
jira=> update issuelinktype set pstyle=null where pstyle='jira_jpos_parent_child'; |
Next we need to create a bunch of issuelink records, one for each of those parent relations stored in JSON in entity_property
The issuelink table looks like this:
jira=> select * From issuelink limit 5; ┌────────┬──────────┬────────┬─────────────┬──────────┐ │ id │ linktype │ source │ destination │ sequence │ ├────────┼──────────┼────────┼─────────────┼──────────┤ │ 10050 │ 10010 │ 14415 │ 14414 │ ␀ │ │ 10051 │ 10010 │ 14354 │ 11616 │ ␀ │ │ 10052 │ 10010 │ 14418 │ 14302 │ ␀ │ │ 149751 │ 10230 │ 188337 │ 188267 │ ␀ │ │ 10054 │ 10010 │ 14443 │ 14440 │ ␀ │ └────────┴──────────┴────────┴─────────────┴──────────┘ (5 rows) |
So we just need to insert the parent-child IDs we obtained from entity_property above.
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:
jira=> select * from sequence_value_item where seq_name='IssueLink'; ┌───────────┬────────┐ │ seq_name │ seq_id │ ├───────────┼────────┤ │ IssueLink │ 244210 │ └───────────┴────────┘ (1 row) jira=> select max(id) from issuelink; ┌────────┐ │ max │ ├────────┤ │ 244178 │ └────────┘ (1 row) |
So our job is to calculate new IDs, then update sequence_value_item afterwards.
Here is some SQL that does the job, including fixing the issuelinktype if you hadn't already done so above.
-- https://www.redradishtech.com/pages/viewpage.action?pageId=14483457
begin;
create temp sequence issuelink_seq start with 99999; -- https://stackoverflow.com/questions/37057643/postgresql-starting-a-sequence-at-maxthe-column1
select setval('issuelink_seq', (select max(id::bigint)+1 from issuelink));
WITH jpolinks AS (
select entity_id AS child_id, (json_value::json->>'parent_id')::numeric AS parent_id from entity_property where entity_name='IssueProperty' and property_key='jpo-issue-properties')
, newissuelinks AS (
select nextval('issuelink_seq') AS id
, jpolinktype.id AS linktype
, parent_id AS source
, child_id as destination
, null::numeric AS sequence
from jpolinks
CROSS JOIN (select id from issuelinktype where linkname='Parent-Child Link') jpolinktype
where parent_id is not null -- the parent_id JSON is sometimes empty
and not exists (select * From issuelink where source=parent_id and destination=child_id and linktype=jpolinktype.id) -- don't double-insert
)
insert into issuelink select * from newissuelinks;
update sequence_value_item set seq_id=nextval('issuelink_seq') where seq_name='IssueLink';
update issuelinktype set pstyle=null where pstyle='jira_jpos_parent_child';
commit; |
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.
After uninstalling Portfolio, you might find your atlassian-jira.log file being filled with errors on each page load:
2019-11-11 00:59:59,234 ajp-nio-127.0.0.100-8009-exec-4059 WARN jturner 59x3059696x3 56p7sr 10.36.2.100 /browse/CC-5353 [c.a.r.j.p.w.children.condition.ChildIssuesPanelVisibilityCondition] Exception when evaluating if child issues panel should be displayed.
com.atlassian.rm.common.env.EnvironmentPortfolio1NotAvailableException: com.atlassian.rm.common.bridges.api.plugins.PluginNotAvailableException
at com.atlassian.rm.common.env.license.JiraLicenseService.getLicenseInformation(JiraLicenseService.java:36)
at com.atlassian.rm.common.env.license.JiraLicenseService.isLicensed(JiraLicenseService.java:45)
at com.atlassian.rm.jpo.plugin.webpanels.children.condition.ChildIssuesPanelVisibilityCondition.isLicensed(ChildIssuesPanelVisibilityCondition.java:81)
at com.atlassian.rm.jpo.plugin.webpanels.children.condition.ChildIssuesPanelVisibilityCondition.shouldChildrenBeVisible(ChildIssuesPanelVisibilityCondition.java:68) at com.atlassian.rm.jpo.plugin.webpanels.children.condition.ChildIssuesPanelVisibilityCondition.shouldDisplay(ChildIssuesPanelVisibilityCondition.java:54)
at com.atlassian.plugin.web.DefaultWebInterfaceManager.filterFragmentsByCondition(DefaultWebInterfaceManager.java:154)
at com.atlassian.plugin.web.DefaultWebInterfaceManager.getDisplayableWebPanelDescriptors(DefaultWebInterfaceManager.java:117)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498) ... |
In Jira's Manage apps admin page you will find these two vestigial plugins:
Uninstall them and your Jira should be back to normal. Jira's plugin system lets plugin authors package reusable parts as separate plugins, but isn't smart enough to uninstall them when the parent plugin is uninstalled.