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 etc).
  • a workflow step, which governs which transitions are available

Issue status

In the database, the status (Open, Closed etc) is stored on the jiraissue table:

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

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

Issue workflow step

Originally JIRA issues only had a status. Then in version 2.0, workflow was added, so that transitions between statuses could be customized. An issue's workflow step is stored in new tables, referenced from jiraissue by the workflow_id:

mysql> select * from OS_WFENTRY where ID=(select workflow_id from jiraissue where pkey='TP-1');
+-------+------+-------------+-------+
| 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' (default, built-in) workflow.

mysql> select * from OS_CURRENTSTEP where ENTRY_ID=(select workflow_id from jiraissue where pkey='TP-1');
+-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+
| 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 |          |             | Open   |        |
+-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+
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:

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

The workflow definition for the built-in 'jira' workflow can be seen in atlassian-jira/WEB-INF/classes/jira-workflow.xml

How status and step relate

An issue's status and workflow step are kept in synch:

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 synch is with a workflow post-function (UpdateIssueStatusFunction), which updates the status whenever the step changes.

If the step gets out of synch with the status, then incorrect (or no) workflow operations appear on the issue page. Eg. 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
Was this page helpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport