Summary

Accessing a Status in Edit workflow mode does not show options like 'Edit', 'Remove Status', 'Properties', etc for that status. Given below screenshot shows working scenario (WAITING FOR SUPPORT) and 'problematic' scenario (FREEZE). When 'WAITING FOR SUPPORT' status is selected, a window pops-up which contains options like 'Edit', 'Remove Status', 'Properties' for that status. However, when FREEZE status is selected, no window pop-up for that status.

Diagnosis

In Jira, status are stored in table: 'issuestatus' and workflow related details are stored in table: 'jiraworkflows'. 'jiraworkflows' table has a column: Descriptor, which stores the content of workflow in XML format. When a Workflow is exported to an XML file, it is the content of the 'Descriptor' column in 'jiraworkflows' table which gets exported to the XML file. Also, when viewing a Workflow from Project Settings or from Settings --> Issues --> Workflow page, Jira creates the Workflow diagram from XML data present in 'Descriptor' column of 'jiraworkflows' table.

• Run SQL query against the Jira database to get the Workflow XML data from 'jiraworkflows' table.

  select descriptor from jiraworkflows where workflowname = '<Workflow name>'

  replace <workflow name> with 'problematic' workflow name

• Search for the 'problematic' status from the output of above query to confirm the Status is present in XML data. Lets say 'Problematic' status is 'Freeze' then search should show

  <step id="17" name="Freeze">                                                                                                    +
         <meta name="jira.status.id">10100</meta>  

• Run SQL query against the Jira Database to see if there is any entry for 'problematic' status in issuestatus table
  

  select * from issuestatus where pname = '<Status name>'

  replace <Status name> with 'Problematic' Status name. SQL query should give no result.

Cause

One of the reason is, during migration of Jira from one instance to another or from Jira Cloud to Jira on-prem, issuestatus table sometime is not restored completely. Due to which some status are missing from issuestatus table. The status details are still present in the XML data in jiraworkflows table and thus it shows up in Workflow diagram.

Solution

1. Stop Jira service

2. Take a backup of Jira database

    Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

3. Search for the 'problematic' status in XML data retrieved from below SQL query

   select descriptor from jiraworkflows where workflowname = '<Workflow name>'

   replace <workflow name> with 'problematic' workflow name

4. Make a note of 'jira.status.id' associated with the Status

5. Run below query to insert the status related detail in issuestatus table

   INSERT INTO issuestatus values (<jira.status.id>, (select max(sequence)+1 from issuestatus), '<status-name>',' ', ' ');

   replace <jira.status.id> with jira.status.id noted in Step #2 and <status-name> with the 'Problematic' Status name

6. Start Jira service