Support for Server products ended Feb. 15, 2024. Learn what this means for you.
Database – Issue fields
This page shows how to examine each of a Jira issue's fields via SQL.
Simple fields
Most fields in Jira are kept in the jiraissue table:
1 2mysql> desc jiraissue; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | NULL | | | pkey | varchar(255) | YES | | NULL | | | issuenum | decimal(18,0) | YES | MUL | NULL | | | PROJECT | decimal(18,0) | YES | MUL | NULL | | | REPORTER | varchar(255) | YES | MUL | NULL | | | ASSIGNEE | varchar(255) | YES | MUL | NULL | | | CREATOR | varchar(255) | YES | | 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 | MUL | NULL | | | UPDATED | datetime | YES | MUL | NULL | | | DUEDATE | datetime | YES | MUL | NULL | | | RESOLUTIONDATE | datetime | YES | MUL | NULL | | | VOTES | decimal(18,0) | YES | MUL | NULL | | | WATCHES | decimal(18,0) | YES | MUL | 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 | MUL | 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:
1 2mysql> select id, issuenum, project, reporter, assignee, issuetype, summary from jiraissue where issuenum=3166 and project = (select id from project where pkey='JRA'); +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | id | issuenum | project | reporter | assignee | issuetype | summary | +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | 16550 | 3166 | 10240 | mvleeuwen | NULL | 2 | Database consistency check tool | +-------+----------+---------+-----------+----------+-----------+---------------------------------+
User details
For example, we want to find out the email address and other details about our reporter mvleeuwen.
1 2select user_name, directory_id, display_name, email_address from cwd_user where user_name = 'mvleeuwen';
Normally this should return a single row, however, Jira allows you to set up multiple user directories and it is possible that two or more directories contain the same username.
For more information, go to User and Group Tables page.
Components and versions
Because each issue can have multiple components or versions, there is a join table between jiraissue and
version/component tables called nodeassociation:
1 2mysql> 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 the following:
1 2mysql> 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 issuenum=5351 and project = (select id from project where pkey='JRA')); +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ | ID | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL | STARTDATE | RELEASEDATE | +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ | 11614 | 10240 | 3.6 | NULL | 131 | NULL | NULL | NULL | NULL | NULL | +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+
Similarly with affects versions:
1 2mysql> 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 issuenum=5351 and project = (select id from project where pkey='JRA'))); +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ | 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 | +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+
Similarly with components:
1 2mysql> 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 issuenum=5351 and project = (select id from project where pkey='JRA'))); +-------+---------+---------------+-------------+------+------+--------------+ | ID | PROJECT | cname | description | URL | LEAD | ASSIGNEETYPE | +-------+---------+---------------+-------------+------+------+--------------+ | 10126 | 10240 | Web interface | NULL | NULL | NULL | NULL | +-------+---------+---------------+-------------+------+------+--------------+
Issue links
Jira issue links are stored in the `issuelink` table, which simply links the IDs of two issues together
and records the link type.
1 2mysql> 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:
1 2mysql> select * from issuelink where SOURCE=(select id from jiraissue where issuenum=1 and project=(select id from project where pkey='TP')) and DESTINATION=(select id from jiraissue where issuenum=2 and project=(select id from project where pkey='TP')); +-------+----------+--------+-------------+----------+ | 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:
1 2mysql> 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 "sub-task" link type is hidden
in the user interface (indicated by the pstyle value below), but visible in the database:
1 2mysql> select * from issuelinktype; +-------+-------------------+---------------------+----------------------+--------------------+ | ID | LINKNAME | INWARD | OUTWARD | pstyle | +-------+-------------------+---------------------+----------------------+--------------------+ | 10000 | Blocks | is blocked by | blocks | NULL | | 10001 | Cloners | is cloned by | clones | NULL | | 10002 | Duplicate | is duplicated by | duplicates | NULL | | 10003 | Relates | relates to | relates to | NULL | | 10100 | jira_subtask_link | jira_subtask_inward | jira_subtask_outward | jira_subtask | | 10200 | Epic-Story Link | has Epic | is Epic of | jira_gh_epic_story | +-------+-------------------+---------------------+----------------------+--------------------+ 6 rows in set (0.00 sec)
This means that it is possible to convert an issue to a sub-task, or vice versa, by tweaking issuelink records.
Custom fields have their own set of tables. For details, see Database - Custom fields.
Rate this page: