Issues that are found in the Jira database are not accessible through Jira UI
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
In this scenario, the UI shows the following message while trying to view an issue. This happens despite being able to look at a number of other issues in the same project and having admin privileges to view all the issues including archived ones. The issue can be seen to exist in the database while querying the jiraissue table along with the jiraaction and changegroup tables.
Environment
All versions of Jira Core 8.x
Diagnosis
The following are done to verify if the issue is reachable or can be modified
Verify if the issues are archived from the
<baseurl>/secure/BrowseArchivedIssues!default.jspaVerify if the issues can be indexed by an admin.
1
curl -D- -u <user>:<pass> -X POST --data '{"issueId":"<issueid>"}' -H "Content-Type: application/json" <baseurl>/rest/api/2/reindex/issueVerify if the issue can be deleted, if its fine to be deleted after checking there are nothing associated with it from the jiraaction and changegroup tables.
1
curl -D- -u <user>:<pass> -X DELETE <baseurl>/rest/api/2/issue/<issue id>Cause
It was noticed that, these unreachable issues have been associated for some reason with an issue security scheme that is not associated with the project to which these issues belong. This could have happened from a database corruption or from a database restore or database failure while migrating the issue security levels with a third party plugin. Recently this has been found to happen due to rest api allowing wrong security scheme to be associated with a issue JRASERVER-74605 - Jira rest api allows assigning of a security level from a unrelated issue security scheme
For example, JIRA Security Issue Security Scheme is an issue security scheme that is associated with only the project JIRA Application Security and not for any other project. But for some reason, in the database there are some issues from project Bamboo_builds with security level from this issue security scheme. So some of these issues from Bamboo_builds project that are wrongly associated with the issue security levels from the above unrelated issue security scheme fail the permission check. So they throw the error and cannot be viewed. This can be verified by querying the database for these issues for their associated issue security scheme and what the correct security scheme should be, based on the issue security scheme configured for the project.
1
2
3
4
5
6
with cte as (select p.id as projectid, p.pkey || '-' || ji.issuenum as issue,ji.security as security from jiraissue ji join project p on ji.project=p.id and p.pkey || '-' || ji.issuenum in ('<affected issuekey>'))
select T1.issue, T1.IssueSecurityScheme as AssociatedIssueSecurityScheme,T1.issuesecuritylevel as AssociatedIssueSecurityLevel, T2.IssueSecurityScheme as CorrectIssueSecurityScheme from
(select cte.issue as issue,ssl.name as issuesecuritylevel,iss.name as IssueSecurityScheme from cte join schemeissuesecuritylevels ssl on ssl.id = cte.security join issuesecurityscheme iss on ssl.scheme = iss.id) as T1
join
(select cte.issue as issue,iss.name as IssueSecurityScheme from cte join nodeassociation na on na.source_node_id = cte.projectid join issuesecurityscheme iss on na.sink_node_id = iss.id where source_node_entity = 'Project' and sink_node_entity = 'IssueSecurityScheme') as T2
on T1.issue = T2.issue and T1.IssueSecurityScheme <> T2.IssueSecurityScheme;To search for all issues with a security level from the security scheme not associated with their project, use the query below.
For PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with issue_sec as(
select p.pkey || '-' || ji.issuenum as "Issue", i.name as "Assigned Security Scheme", s.name as "Assigned Security Level", p.pname as Project
from jiraissue ji
join project p on ji.project=p.id
join schemeissuesecuritylevels s on s.id = ji.security
join issuesecurityscheme i on s.scheme = i.id
),
project_sec as(
select p.pname as Project, i.name as "Associated Issue Security Scheme"
from project p
join nodeassociation n on p.id = n.source_node_id
join issuesecurityscheme i on i.id = n.sink_node_id
where n.source_node_entity = 'Project' and n.sink_node_entity='IssueSecurityScheme'
),
all_sec as(
select issue_sec."Issue", issue_sec."Assigned Security Scheme", issue_sec."Assigned Security Level", project_sec."Associated Issue Security Scheme"
from issue_sec right join project_sec on issue_sec.Project = project_sec.Project
)
select "Issue", "Assigned Security Scheme", "Associated Issue Security Scheme"
from all_sec
where "Assigned Security Scheme" != "Associated Issue Security Scheme"For MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with issue_sec as(
select concat(p.pkey, '-', ji.issuenum) as "Issue", i.name as "Assigned Security Scheme", s.name as "Assigned Security Level", p.pname as Project
from jiraissue ji
join project p on ji.project=p.id
join schemeissuesecuritylevels s on s.id = ji.security
join issuesecurityscheme i on s.scheme = i.id
),
project_sec as(
select p.pname as Project, i.name as "Associated Issue Security Scheme"
from project p
join nodeassociation n on p.id = n.source_node_id
join issuesecurityscheme i on i.id = n.sink_node_id
where n.source_node_entity = 'Project' and n.sink_node_entity='IssueSecurityScheme'
),
all_sec as(
select issue_sec.`Issue`, issue_sec.`Assigned Security Scheme`, issue_sec.`Assigned Security Level`, project_sec.`Associated Issue Security Scheme`, STRCMP(`Assigned Security Scheme`,`Associated Issue Security Scheme`) AS "Needs fixing"
from issue_sec right join project_sec on issue_sec.Project = project_sec.Project
)
select `Issue`, `Assigned Security Scheme`, `Associated Issue Security Scheme`
from all_sec
where `Needs fixing` !=0Example result. It shows the issues with their key that has the current assigned security scheme (which is wrong according to the scheme) and the scheme that should be associated.
1
2
3
4
5
6
7
8
9
10
11
Issue |Assigned Security Scheme|Associated Issue Security Scheme|
-------+------------------------+--------------------------------+
ITSM-14|Ext |Users only |
ITSM-5 |Ext |Users only |
ITSM-26|Ext |Users only |
ITSM-3 |Ext |Users only |
ITSM-19|Ext |Users only |
ITSM-4 |Ext |Users only |
ITSM-15|Ext |Users only |
ITSM-13|Ext |Users only |
ITSM-9 |Ext |Users only |Solution
After identifying the mismatch in the issue security scheme corresponding to the issue and the project it belongs to, update those issues with an issue security level corresponding to its project.
The available issue security levels for a project can be found as follows.
1 2 3select p.pkey as Project,iss.name as IssueSecurityScheme,ssl.name as IssueSecurityLevels,ssl.ID as IssueSecurityLevelID from project p join (select sink_node_id, source_node_id from nodeassociation where source_node_entity = 'Project' and sink_node_entity = 'IssueSecurityScheme')na on na.source_node_id = p.id join issuesecurityscheme iss on na.sink_node_id = iss.id join schemeissuesecuritylevels ssl on iss.id = ssl.scheme;Update the issues which have wrong issue security level by running an update as follows.
Please make sure you have a back up of the database before performing any changes to it. Also validate and familiarize yourself with the procedure in a non-production environment first.
1update jiraissue set security = <issuesecuritylevelid from above query> where project = (select id from project where pkey = <project from above query>) and issuenum = <issue number of the issue>
Was this helpful?