SQL equivalents for Jira server's workflow integrity checks

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

The workflow integrity checker is timing out when running in the browser, preventing it from running.

Cause

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-17593 - Getting issue details... STATUS .

Workaround

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

  1. This will check for any workflows that have an invalid state, as a result of  JRA-4241 - Getting issue details... STATUS .

    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; 
  2. 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
  3. To fix this example, run the following SQL - we need to update the state to be 1 based on the workflow_id. The workflow_id is actually os_wfentry.id

    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

  1. This will check if any issues have an invalid issue status, as from  JRA-7428 - Getting issue details... 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;


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

  1. This will check if any issues have an invalid workflow id, as from  JRASERVER-4539 - Getting issue details... STATUS :

    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;


  2. 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); 
  3. Copy all results and run the inserts to add the missing rows.

Last modified on Jul 8, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.