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 JRASERVER-17593 - Use progress indicator for Integrity Checker to avoid a reverse-proxy timing out the request (implemented in 9.11.0 and later)

Workaround

The SQL checks for workflows can be done by manually executing them against the database. 

Workflow entry states are incorrect

1. This will check for any workflows that have an invalid state, as a result of JRASERVER-4241 - Issues with no workflow actions after upgrade.

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

   MySQL equivalent for the above query that helped:

   UPDATE os_wfentry as o1 
   inner join (
       (SELECT o2.id 
           FROM jiraissue as ji
           JOIN os_wfentry as o2
           ON ji.workflow_id = o2.id
           WHERE o2.state IS NULL OR o2.state = 0)
   ) AS o3 on o1.id = o3.id
   SET o1.state = 1; 

Jira Issues with Null Status

1. This will check if any issues have an invalid issue status, as from JRASERVER-7428 - Workflow Integrity Check needs an additional check:

   SELECT jiraissue.id, 
          jiraissue.issuenum, 
          jiraissue.issuestatus, 
          jiraissue.project, 
          jiraissue.issuetype, 
          currentStep.step_id 
   FROM   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 - Migrating Issues to a new Workflow fails if a Workflow Entry does not have any Steps:

   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 not exists (select * from os_currentstep where entry_id=workflow_id);

3. Copy all results and run the inserts to add the missing rows.