SQL equivalents for Jira server's workflow integrity checks
The workflow integrity checker is timing out when running in the browser, preventing it from running.
The instance is of a significant size and checks are not able to complete within the timeout period. We have a suggestion to fix this with a progress bar as tracked in - JRA-17593Getting issue details... STATUS .
The SQL checks for workflows can be done by manually executing them against the database.
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
Workflow entry states are correct
SELECT jiraissue.id issue_id, jiraissue.workflow_id, os_wfentry.* FROM jiraissue JOIN os_wfentry ON jiraissue.workflow_id = os_wfentry.id WHERE os_wfentry.state IS NULL OR os_wfentry.state = 0;
If this returns any records, for example below, they will need to be updated to be valid:
issue_id | workflow_id | id | name | initialized | state ----------+-------------+-------+------------------------------+-------------+------- 10023 | 10023 | 10023 | jdg-workflow-2-1377070570282 | | 0
To fix this example, run the following SQL - we need to update the
stateto be 1 based on the
UPDATE os_wfentry SET state = 1 WHERE id IN (SELECT os_wfentry.id FROM jiraissue JOIN os_wfentry ON jiraissue.workflow_id = os_wfentry.id WHERE os_wfentry.state IS NULL OR os_wfentry.state = 0);
Jira Issues with Null Status
SELECT jiraissue.id, jiraissue.issuenum, jiraissue.issuestatus, jiraissue.project, jiraissue.issuetype, currentStep.step_id FROM jiraissue jiraissue JOIN os_currentstep currentStep ON jiraissue.workflow_id = currentStep.entry_id WHERE jiraissue.issuestatus IS NULL;
And can be fixed with the following:
UPDATE jiraissue SET issuestatus = (SELECT state FROM os_wfentry WHERE id = workflow_id) WHERE issuestatus IS NULL;
Workflow current step entries
select concat(concat(P.pkey,'-'),I.issuenum) from jiraissue I join project P on P.id = I.project left join os_currentstep C on C.entry_id = I.workflow_id where C.id is null;
Running the query below will generate the inserts for all issues missing the valid workflow step entry:
select concat(concat('insert into os_currentstep values ((select max(id)+1 from os_currentstep),',workflow_id),',1,0,'''',now(),null,null,''open'',null)') from jiraissue where workflow_id not in (select entry_id from os_currentstep);
- Copy all results and run the inserts to add the missing rows.