Documentation for JIRA 4.1. Documentation for other versions of JIRA is available too.

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Current »

This page shows how to examine each of a JIRA issue's fields via SQL. We will use JRA-3166 as a sample issue in our queries.

Simple fields

Most fields in JIRA are kept in the jiraissue table:

mysql> desc jiraissue;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| ID                   | decimal(18,0) | NO   | PRI |         |       |
| pkey                 | varchar(255)  | YES  | MUL | NULL    |       |
| PROJECT              | decimal(18,0) | YES  | MUL | NULL    |       |
| REPORTER             | varchar(255)  | YES  |     | NULL    |       |
| ASSIGNEE             | varchar(255)  | YES  | MUL | NULL    |       |
| issuetype            | varchar(255)  | YES  |     | NULL    |       |
| SUMMARY              | varchar(255)  | YES  |     | NULL    |       |
| DESCRIPTION          | longtext      | YES  |     | NULL    |       |
| ENVIRONMENT          | longtext      | YES  |     | NULL    |       |
| PRIORITY             | varchar(255)  | YES  |     | NULL    |       |
| RESOLUTION           | varchar(255)  | YES  |     | NULL    |       |
| issuestatus          | varchar(255)  | YES  |     | NULL    |       |
| CREATED              | datetime      | YES  |     | NULL    |       |
| UPDATED              | datetime      | YES  |     | NULL    |       |
| DUEDATE              | datetime      | YES  |     | NULL    |       |
| VOTES                | decimal(18,0) | YES  |     | NULL    |       |
| TIMEORIGINALESTIMATE | decimal(18,0) | YES  |     | NULL    |       |
| TIMEESTIMATE         | decimal(18,0) | YES  |     | NULL    |       |
| TIMESPENT            | decimal(18,0) | YES  |     | NULL    |       |
| WORKFLOW_ID          | decimal(18,0) | YES  |     | NULL    |       |
| SECURITY             | decimal(18,0) | YES  |     | NULL    |       |
| FIXFOR               | decimal(18,0) | YES  |     | NULL    |       |
| COMPONENT            | decimal(18,0) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

They can be retrieved with a regular select:

mysql> select id, pkey, project, reporter, assignee, issuetype, summary from jiraissue where pkey='JRA-3166';
+-------+----------+---------+-----------+----------+-----------+---------------------------------+
| id    | pkey     | project | reporter  | assignee | issuetype | summary                         |
+-------+----------+---------+-----------+----------+-----------+---------------------------------+
| 16550 | JRA-3166 |   10240 | mvleeuwen | NULL     | 2         | Database consistency check tool |
+-------+----------+---------+-----------+----------+-----------+---------------------------------+

User details

Say we wish to find out the email address and other details about our reporter, mvleeuwen. First we find this user's ID:

mysql> select id from userbase where username='mvleeuwen';
+-------+
| id    |
+-------+
| 13841 |
+-------+

Then use it to look up 'properties' of this userbase record (stored in propertysets. Each property has a record in the propertyentry table specifying its name and type, and a record in one of propertystring, propertydecimal, propertydate, propertytext, propertydata or propertynumber, depending on the type.

mysql> desc propertyentry;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| ID           | decimal(18,0) | NO   | PRI |         |       |
| ENTITY_NAME  | varchar(255)  | YES  |     | NULL    |       |
| ENTITY_ID    | decimal(18,0) | YES  | MUL | NULL    |       |
| PROPERTY_KEY | varchar(255)  | YES  |     | NULL    |       |
| propertytype | decimal(9,0)  | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+

mysql> select * from propertyentry where ENTITY_NAME='OSUser' and ENTITY_ID=(select id from userbase where username='mvleeuwen');
+-------+-------------+-----------+--------------+--------------+
| ID    | ENTITY_NAME | ENTITY_ID | PROPERTY_KEY | propertytype |
+-------+-------------+-----------+--------------+--------------+
| 18352 | OSUser      |     13841 | email        |            5 |
| 18353 | OSUser      |     13841 | fullName     |            5 |
+-------+-------------+-----------+--------------+--------------+

So email and fullName are of type 5. which means the propertystring table. Here is the list of propertytype to table mappings:

propertyentry.propertytype value

Table value is stored in

Used for

1

propertynumber

Boolean values, eg. user preferences

5

propertystring

Most fields, eg. full names, email addresses

6

propertytext

Large blocks of text, eg. the introduction text, HTML portletconfigurations

2/3 (question)

propertydecimal

Unused in JIRA

7

propertydate

Unused in JIRA

10

propertydata

Unused in JIRA

So the email and fullName properties are strings, and so can be found in the propertystring table:

mysql> select * from propertystring where id in (18352, 18353);
+-------+---------------------+
| ID    | propertyvalue       |
+-------+---------------------+
| 18352 | lemval@zonnet.nl    |
| 18353 | Michael van Leeuwen |
+-------+---------------------+

Components and versions

Since each issue can have multiple components/versions, there is a join table between jiraissue and version/component tables called nodeassociation:

mysql> desc nodeassociation;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| SOURCE_NODE_ID     | decimal(18,0) | NO   | PRI |         |       |
| SOURCE_NODE_ENTITY | varchar(60)   | NO   | PRI |         |       |
| SINK_NODE_ID       | decimal(18,0) | NO   | PRI |         |       |
| SINK_NODE_ENTITY   | varchar(60)   | NO   | PRI |         |       |
| ASSOCIATION_TYPE   | varchar(60)   | NO   | PRI |         |       |
| SEQUENCE           | decimal(9,0)  | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+

mysql> select distinct SOURCE_NODE_ENTITY from nodeassociation;
+--------------------+
| SOURCE_NODE_ENTITY |
+--------------------+
| Issue              |
| Project            |
+--------------------+

mysql> select distinct SINK_NODE_ENTITY from nodeassociation;
+-----------------------+
| SINK_NODE_ENTITY      |
+-----------------------+
| IssueSecurityScheme   |
| PermissionScheme      |
| IssueTypeScreenScheme |
| NotificationScheme    |
| ProjectCategory       |
| FieldLayoutScheme     |
| Component             |
| Version               |
+-----------------------+

mysql> select distinct ASSOCIATION_TYPE from nodeassociation;
+------------------+
| ASSOCIATION_TYPE |
+------------------+
| IssueVersion     |
| IssueFixVersion  |
| IssueComponent   |
| ProjectScheme    |
| ProjectCategory  |
+------------------+

So to get fix-for versions of an issue, run:

mysql> select * from projectversion where id in (
    select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueFixVersion' and SOURCE_NODE_ID=(
        select id from jiraissue where pkey='JRA-5351')
    );
+-------+---------+-------+-------------+----------+----------+----------+------+-------------+
| ID    | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL  | RELEASEDATE |
+-------+---------+-------+-------------+----------+----------+----------+------+-------------+
| 11614 |   10240 | 3.6   | NULL        |      131 | NULL     | NULL     | NULL | NULL        |
+-------+---------+-------+-------------+----------+----------+----------+------+-------------+

Similarly with affects versions:

mysql> select * from projectversion where id in (
    select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueVersion' and SOURCE_NODE_ID=(
        select id from jiraissue where pkey='JRA-5351')
    );
+-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+
| ID    | PROJECT | vname               | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL  | RELEASEDATE         |
+-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+
| 10931 |   10240 |  3.0.3 Professional | NULL        |       73 | true     | NULL     | NULL | 2004-11-19 00:00:00 |
| 10930 |   10240 |  3.0.3 Standard     | NULL        |       72 | true     | NULL     | NULL | 2004-11-19 00:00:00 |
| 10932 |   10240 |  3.0.3 Enterprise   | NULL        |       74 | true     | NULL     | NULL | 2004-11-19 00:00:00 |
+-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+

and components:

mysql> select * from component where id in (
    select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueComponent' and SOURCE_NODE_ID=(
        select id from jiraissue where pkey='JRA-5351')
    );
+-------+---------+---------------+-------------+------+------+--------------+
| ID    | PROJECT | cname         | description | URL  | LEAD | ASSIGNEETYPE |
+-------+---------+---------------+-------------+------+------+--------------+
| 10126 |   10240 | Web interface | NULL        | NULL | NULL |         NULL |
+-------+---------+---------------+-------------+------+------+--------------+


JIRA issue links are stored in the issuelink table, which simply links the IDs of two issues together, and records the link type:

mysql> desc issuelink;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID          | decimal(18,0) | NO   | PRI |         |       | 
| LINKTYPE    | decimal(18,0) | YES  | MUL | NULL    |       | 
| SOURCE      | decimal(18,0) | YES  | MUL | NULL    |       | 
| DESTINATION | decimal(18,0) | YES  | MUL | NULL    |       | 
| SEQUENCE    | decimal(18,0) | YES  |     | NULL    |       | 
+-------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

For instance, to list all links between TP-1 and TP-2:

mysql> select * from issuelink where SOURCE=(select id from jiraissue where pkey='TP-1') and DESTINATION=(select id from jiraissue where pkey='TP-2');
+-------+----------+--------+-------------+----------+
| ID    | LINKTYPE | SOURCE | DESTINATION | SEQUENCE |
+-------+----------+--------+-------------+----------+
| 10020 |    10000 |  10000 |       10010 |     NULL | 
+-------+----------+--------+-------------+----------+
1 row in set (0.00 sec)

Link types are defined in issuelinktype. This query prints all links in the system with their type:

mysql> select j1.pkey, issuelinktype.INWARD, j2.pkey from jiraissue j1, issuelink, issuelinktype, jiraissue j2 where j1.id=issuelink.SOURCE and j2.id=issuelink.DESTINATION and issuelinktype.id=issuelink.linktype;
+-------+---------------------+-------+
| pkey  | INWARD              | pkey  |
+-------+---------------------+-------+
| TP-4  | jira_subtask_inward | TP-5  | 
| TP-4  | jira_subtask_inward | TP-7  | 
| TP-4  | jira_subtask_inward | TP-8  | 
| TP-11 | jira_subtask_inward | TP-12 | 
| TP-4  | jira_subtask_inward | TP-6  | 
| TP-1  | is duplicated by    | TP-2  | 
+-------+---------------------+-------+
6 rows in set (0.00 sec)

Subtasks

As shown in the last query, JIRA records the issue-subtask relation as a link. The "subtask" link type is hidden in the user interface (indicated by the 'pstyle' value below), but visible in the database:

mysql> select * from issuelinktype;
+-------+-------------------+---------------------+----------------------+--------------+
| ID    | LINKNAME          | INWARD              | OUTWARD              | pstyle       |
+-------+-------------------+---------------------+----------------------+--------------+
| 10000 | Duplicate         | is duplicated by    | duplicates           | NULL         | 
| 10001 | jira_subtask_link | jira_subtask_inward | jira_subtask_outward | jira_subtask | 
+-------+-------------------+---------------------+----------------------+--------------+
2 rows in set (0.00 sec)

This means it is possible to convert an issue to a subtask, or vice-versa, by tweaking issuelink records.

Custom fields have their own set of tables. For details, see Custom fields

  • No labels



Log a request with our support team.



Raise an issue for our developers.



See answers from the community.



Tweet, blog and update our documentation.

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.