SQL equivalents for Issue Relations Integrity Check in Jira Data Center
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
以下の項目もご覧になるといいでしょう。
このページの内容
症状
The workflow integrity checker is timing out when running in the browser or you're facing performance issues when triggering the check through the UI.
The information shared here has also been requested Feature Request below:
JRASERVER-74077 - 課題詳細を取得中... ステータス
An admin may also want to understand the queries behind the check or routinely check the results directly through the database in an efficient manner.
原因
The instance is of a significant size (i.e. Large or Enterprise scale per Jira Server sizing guide) and the Integrity Checker is not able to complete within the timeout period. We have a suggestion to fix this with a progress which is being tracked here:
- On the workaround section of the bug for steps to increase the timeout for it or how to bypass proxy it and correct directly to a node.
There are reports of resource constraint and this bug was created to document it:
JRASERVER-65265 - 課題詳細を取得中... ステータス
回避策
The SQL checks for Issue Relations can be done by executing a similar logic against the database.
データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。
All queries were written and tested on PostgreSQL.
Other DB products might need syntax adjustments and your setup might require database name, schema to be provided for table names.
If your database is case-sensitive and any query returns an error related to table not found, please, observe if in the database the queried table name is capitalized or in lower case.
Check Issue for Relation 'ParentProject'
Validation process by Jira
Jira runs a select on jiraissue to fetch all the projects associated to individual issues:
SELECT ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT, ARCHIVED, ARCHIVEDBY, ARCHIVEDDATE
FROM jiraissue;
For each issue it runs a check using project
field obtained from jiraissue
against project
table:
- Example query for issue with ID=10000:
SELECT pname, URL, LEAD, DESCRIPTION, pkey, pcounter, ASSIGNEETYPE,
AVATAR, ORIGINALKEY, PROJECTTYPE FROM public.project WHERE ID='10000';
Manual validation
This query returns all issues associated with nonexistent projects:
select * from jiraissue where project not in (select id from project);
You may save the result above to understand how many issues are found and to have a quick backup.
The fix action involves deleting the issue from jiraissue
table. This next delete statement removes the same records that would be found by the query above:
delete from jiraissue where project not in (select id from project);
Check Issue for Relation 'RelatedOSWorkflowEntry'
Validation process by Jira
Jira runs a select on jiraissue
to fetch the workflow_id
value for each issue from your instance:
SELECT ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT, ARCHIVED, ARCHIVEDBY, ARCHIVEDDATE
FROM jiraissue;
It iterates through the IDs obtained using a query like the one below:
- Example for
workflow_id=10808
found onjiraissue
:
SELECT NAME, INITIALIZED, STATE FROM public.OS_WFENTRY WHERE ID='10808';
Jira will create a new os_wfentry
record and update the affected jiraissue
records with the new ID
. The new record will have the highest ID, name='jira',
initialized=null
and state=0
.
These entries with
state=0
will later need to be corrected by another integrity checker item: Workflow entry states are correct. Manual steps can be found here: SQL equivalents for Workflow Integrity Check in Jira Data Center.
Manual validation
Query to fetch all jiraissue
records associated with an nonexistent workflow_id
value
:
SELECT ji.* FROM jiraissue ji
LEFT JOIN OS_WFENTRY we ON ji.WORKFLOW_ID = we.id
WHERE we.id IS NULL;
This query will generate the SQL statement to insert a new record on os_wfentry using the same ID
found on jiraissue
.
- This is slightly different than Jira's approach that will create a new record and update the issue ID. This is simpler than generating a new entry and keeping track of it to update
jiraissue
record. - You'll need to copy the contents created and run them to actually insert the records.
select concat('insert into OS_WFENTRY values (',ji.WORKFLOW_ID,',''jira'',null,0);')
FROM jiraissue ji LEFT JOIN OS_WFENTRY we ON ji.WORKFLOW_ID = we.id
WHERE we.id IS NULL;
Example of string generated:
insert into OS_WFENTRY values (1030000,'jira',null,0);
insert into OS_WFENTRY values (121212,'jira',null,0);
Check that all Issue Links are associated with valid issues
Validation process by Jira
Jira gets all issues and stores in memory:
SELECT ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT, ARCHIVED, ARCHIVEDBY, ARCHIVEDDATE
FROM jiraissue;
Then it gets the entire issuelink
table and stores it in memory.
SELECT ID, LINKTYPE, SOURCE, DESTINATION, SEQUENCE FROM issuelink;
With the contents of both tables, Jira checks if either source
or destination
values from issuelink
do not exist on jiraissue. If one or both of them are not found, the entry is deleted from issuelink
.
- Example where
issuelink.ID=10002
:
DELETE FROM issuelink WHERE ID='10002';
Manual validation
You may run the query below to get a list of all issuelink
records where either source
or destination
values do not exist on jiraissue.
SELECT il.*
FROM issuelink il
LEFT JOIN jiraissue ji1 ON il.source = ji1.ID
LEFT JOIN jiraissue ji2 ON il.destination = ji2.ID
WHERE ji1.ID IS NULL OR ji2.ID IS NULL;
Records from issuelink
found above can be deleted individually by the ID
,
mimicking the integrity checker:
DELETE FROM issuelink WHERE ID='10002';
Alternatively, you can run this query to delete all offending records directly.
It is still recommended to run the select statement first to understand how long the query takes on your instance and how many records will be affected.
delete FROM issuelink where ID in
(select il.ID
FROM issuelink il
LEFT JOIN jiraissue ji1 ON il.source = ji1.ID
LEFT JOIN jiraissue ji2 ON il.destination = ji2.ID
WHERE ji1.ID IS NULL OR ji2.ID IS NULL);