症状

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:

JRA-17593 - 課題詳細を取得中... ステータス

• 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.

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 on jiraissue:

 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 Jira server's workflow integrity checks.

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);