Some issue links do not appear in the issuelink database table

[Gisela Lassahn]

Hi everybody,

we use Jira DC 8.20.3 and have an issue type "Paket" (package) above Epics in our Plans hierarchy. So we can link Packages and Epics via the custom field "Parent Link".

Now we have to remove such links for a certain project which only contains packages.
So I wanted to do a database selection on the database table issuelink to find out which packages are linked to which Epics. I've found out that some of these links are stored in the table issuelink and some are not.

My SQL was:

SELECT ps.pkey, jis.issuenum, ts.pname, pd.pkey, jid.issuenum, td.pname, lt.*
FROM jiraissue jis, jiraissue jid, issuelink l, project ps, project pd, issuelinktype lt, issuetype ts, issuetype td
WHERE jis.project = ps.id
AND jid.project = pd.id
AND jis.issuetype = ts.ID
AND jid.issuetype = td.id
AND l.SOURCE = jis.id
AND l.DESTINATION = jid.id
AND l.LINKTYPE = lt.ID
AND (ps.pkey = 'MPM' OR pd.pkey = 'MPM')

In Jira I can see Epics which show Parent Links to the project with the shortcut "MPM" which are NOT in the result of this query (though I see many other such links in the result).

Does anybody have an idea where the links are stored if they aren't in the issuelink table?

By the way: We also use "Xray for Jira" and this JQL also delivers Epics which do NOT appear when I use the SQL:
"Parent Link" in ProjectParentRequirements("MPM")

Many thanks and best regards
Gisela

[Nic Brough -Adaptavist-]

Yeah, stop reading the database, it isn't in the shape you are guessing it is.  It's the worst way you can possibly report on Jira.

What problem are you trying to solve with this query?  What are you actually looking for and why?

[Gisela Lassahn]

As I described before, I'm looking for parent links to packages from a certain project because we have to delete these links.
If I try this with JQL like "Parent Link" in ProjectParentRequirements("MPM") (which I can only do because we have Xray for Jira) I only see issues from projects where I have the rights to see them.
We have lots of projects and many permission schemes where I cannot be sure that  the group jira-administrators has rights to view the projects. I would have to review them all to be sure to find all linked issues.
So I tried it via the database.
If you have got a better idea for my problem you should share your idea instead of playing the schoolmaster and being so rude in your comments.

[Nic Brough -Adaptavist-]

I'm sorry I came across rude, it was not intended that way.  I apologise if I've caused any offence.

But the schoolmaster bit is still right.  You need to stop looking at the database, because it's a mess and you're going to be spending weeks trying to work out a query that probably won't give you the right answer.  Links are scattered through several tables.

Your JQL is the right way to do it, it will find all the links. 

But yes, the permission schemes would need to be checked for admin access.  I'm afraid that's a manual job, but it's not too painful as you have a couple of options that are a lot better to do manually than try to work out from the database.

1) You can look at each active scheme and either add a user directly to the browse project permission, or dig a little deeper and work out how to add a user to a role or group that will grant it for a project.

2) Run a filter and save it, with no question.  An empty filter, or one that just has "order by created" for example, returns all issues.  Useless in itself, but running it will tell you how many you can currently see at the top of the page, and you can compare that with the number in the system info page.  If they match, you know you can see all the issues.

After that, I usually stick a simple "filter statistics" gadget on a desktop and use the "all issues" filter for it, and "project" as the group-by.  This tells you what projects you can currently see (and have issues in them), so you can subtract the list from the list of projects you need to check to add yourself to.

In the database, I'm afraid you'd have to read each project for its permission scheme and things in the project roles, cross referencing them (up to 14 different times) into two or three layers of depth to get a user.  It's not a fun query.

[Gisela Lassahn]

Many thanks for your helpful hints!

I could not find the gadget you mentioned, only a gadget called "Two Dimensional Filter Statistics" which cannot be configured like you described.

But inspired by your hints I've found the following solution for me:

1. At first, I checked our 26 permission schemes manually and added the "Browse Project" permission for the administrator group "jira-administrators" where it was missing.
2. To be sure that I didn't forget a scheme, I filtered all issues I can see by the JQL "order by project" and counted the issues from the non-archived projects in the database via this SQL:
   SELECT COUNT(*)
   FROM jiraissue i, project p
   WHERE i.project = p.id
   and NOT EXISTS
   (
   SELECT 1
   from propertyentry pe
   WHERE pe.entity_id=p.id
   and property_key = 'jira.archiving.projects'
   )
3. The number from the database was bigger than the number of issues found by the filter (JQL).
   Therefore, I checked that there are no archived issues in our system but those from archived projects (via Issues -> Archived Issues).
4. To find out the reason for the difference, I reduced the shown columns from the filter to "key" only and exported the keys in a csv file.
5. I counted the issues from the non-archived projects from the database via this SQL:
   SELECT p.pkey, COUNT(*)
   FROM jiraissue i, project p
   WHERE i.project = p.id
   and NOT EXISTS
   (
   SELECT 1
   from propertyentry pe
   WHERE pe.entity_id=p.id
   and property_key = 'jira.archiving.projects'
   )
   GROUP BY p.pkey
   ORDER BY p.pkey
6. I put the contents of the exported csv file and the result of the SQL together as to tables in an excel file and tried to compare the numbers for the projects from both tables.
7. Therefore, I added a column D to the csv file contents containing a formula to get the project shortcuts at first.
   
8. In the other table with the SQL results, I added two columns like this:
   
   Column C contains a formula to count the issues from the csv table and column D the difference.
9. I used the excel filter function to find out which projects had a difference in the numbers. It was only one project. We use it for education purposes, and it has issues which are hidden via issue security.
10. So, finally, I am sure that (at the moment) I can see all issues I have to see.
11. In a final step, I repeated the JQL 
    "Parent Link" in ProjectParentRequirements("MPM")
    to be sure that there were no links left to delete.

(My Excel is working with German functions. I'm not sure how they are called in English, but I hope it still is possible to follow my steps.)