Database – Issue status and workflow

This page describes the database tables involved in issue workflow. It will be useful for people who wish to insert issues into the database manually or diagnose/fix corrupted databases.

Jira issues have both:

• A Status (Open, Closed, In Progress, and others).
• A Workflow step that governs which transitions are available.

Issue status

In the database, the Status (Open, Closed, and others) is stored in the jiraissue table:

1
2
mysql> select issuestatus from jiraissue where issuenum=1 and project = (select id from project where pkey='TP');
+-------------+
| issuestatus |
+-------------+
| 1           |
+-------------+
1 row in set (0.00 sec)

mysql> select pname from issuestatus, jiraissue where issuestatus.id=jiraissue.issuestatus and issuenum=1 and project = (select id from project where pkey='TP');
+-------+
| pname |
+-------+
| Open  |
+-------+
1 row in set (0.00 sec)

Issue workflow step

Originally Jira issues only had a status. Then, Workflow was added in version 2.0 so that transitions between statuses could be customized.

An issue's workflow step is stored in new tables that are referenced from jiraissue by the workflow_id:

1
2
mysql> select * from OS_WFENTRY where ID=(select workflow_id from jiraissue where issuenum=1 and project = (select id from project where pkey='TP'));
+-------+------+-------------+-------+
| ID    | NAME | INITIALIZED | STATE |
+-------+------+-------------+-------+
| 10000 | jira | 0           | 1     |
+-------+------+-------------+-------+
1 row in set (0.02 sec)

The TP-1 issue's OS\_WFENTRY row indicates that the issue uses the jira (that is, default, built-in) workflow.

1
2
mysql> select * from OS_CURRENTSTEP where ENTRY_ID=(select workflow_id from jiraissue where issuenum=1 and project = (select id from project where pkey='TP'));
+-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+
| ID    | ENTRY_ID | STEP_ID | ACTION_ID | OWNER | START_DATE          | DUE_DATE | FINISH_DATE | STATUS | CALLER |
+-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+
| 10000 | 10000    | 1       | 0         |       | 2003-11-24 15:17:50 | NULL     | NULL        | Open   | NULL   |
+-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+
1 row in set (0.13 sec)

The issue's OS\_CURRENTSTEP row specifies the issue's current step. The only field really used is STEP_ID. This references a step definition in the workflow:

1
2
<step id="1" name="Open">

How status and step relate

An issue status and workflow step are kept in sync:

1
2
mysql> select issuestatus.pname status, issuestatus, OS_CURRENTSTEP.STEP_ID, OS_CURRENTSTEP.STATUS from
   issuestatus, jiraissue, OS_CURRENTSTEP where
   issuestatus.id=jiraissue.issuestatus and jiraissue.workflow_id=OS_CURRENTSTEP.ENTRY_ID;
+-------------+-------------+---------+----------+
| status      | issuestatus | STEP_ID | STATUS   |
+-------------+-------------+---------+----------+
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
...
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
| Open        | 1           | 1       | Open     |
| In Progress | 3           | 3       | Underway |
| Closed      | 6           | 6       | Closed   |
+-------------+-------------+---------+----------+
32 rows in set (0.00 sec)

mysql>

Status and step are kept in sync with a workflow post-function UpdateIssueStatusFunction that updates the status whenever the step changes.

If the step gets out of sync with the status, then incorrect (or no) workflow operations appear on the issue page. For example, if OS\_CURRENTSTEP.STEP\_ID was 6 ("Closed") when jiraissue.issuestatus was 1 ("Open"), then the issue would have only one transition ("Reopen issue") which would break if anyone clicked on it.

Summary of issue status and workflow

• For each jiraissue row, there is a OS_CURRENTSTEP and OS_WFENTRY row.
• OS_WFENTRY specifies the applicable workflow. OS_CURRENTSTEP specifies the step in that workflow.
• The relations are:
  • jiraissue.WORKFLOW\_ID == OS\_WFENTRY.ID
  • jiraissue.WORKFLOW\_ID == OS\_CURRENTSTEP.ENTRY\_ID