Issue Types missing, blank, or duplicated in Issue Type Schemes in Jira server
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Please ensure that a backup has been done prior to the change below. Below modification is not guaranteed to be supported by Atlassian and any changes should be done in own risk
Since JIRA 6.2, new projects are not created using the default issue type scheme. Depending on which project template you choose, a fresh set of schemes will be created for you. For more information please refer to Creating new project always creates a new issue type scheme.
Solution
Symptoms
Some Issue Types are missing from the "Default Issue Type Scheme". This may cause a loss of functionality for issue-related functions (i.e. moving or creating issues), resulting in 500 errors.
Cause #1
An issue type is missing from the optionconfiguration table. To check this, run the below query.
1
SELECT * FROM optionconfiguration WHERE optionid NOT IN (SELECT id FROM issuetype);Databasesand table names may becase sensitive and use different casing depending on the OS or DBMS used. Therefore if the query doesn't work in the first go, you might want to check the Tables and Columns of JIRA's database to see whether they use a different case.
One or more rows returned?
Try the steps on Workaround #3.
No rows returned?
Check Cause #2.
Cause #2
An issue type exists but is not present on the optionconfiguration table. To check this, run the below query.
1
SELECT * FROM issuetype WHERE id NOT IN (SELECT optionid FROM optionconfiguration);Databasesand table names may becase sensitive and use different casing depending on the OS or DBMS used. Therefore if the query doesn't work in the first go, you might want to check the Tables and Columns of JIRA's database to see whether they use a different case.
One or more rows returned?
Try the steps on Workaround #1.
Cause #3
Issue types exist and the Issue Type scheme exists, but the database reference linking the configurations are missing. These are stored within the fieldconfigschemeissuetype and we can confirm if there is a problem by running the following SQL query:
1
select fit.id,fit.fieldconfigscheme,fs.id as fieldconfigid,fs.configname,fs.fieldid from fieldconfigschemeissuetype fit full outer join fieldconfigscheme fs on fit.fieldconfiguration=fs.id where fit.fieldconfiguration is null;If any results are returned, it means there is a missing reference for that returned result
ℹ️ As Full Outer Join is not supported in MySQL DB, you can use the query below:
1
2
3
4
5
6
SELECT * FROM fieldconfigschemeissuetype fit
LEFT JOIN fieldconfigscheme fs ON fit.fieldconfiguration=fs.id
UNION
SELECT * FROM fieldconfigschemeissuetype fit
RIGHT JOIN fieldconfigscheme fs ON fit.fieldconfiguration=fs.id
where fit.fieldconfiguration is null;One or more rows returned?
Try the steps on Workaround #2.
Workaround #1
Currently, the workaround involves data manipulation directly on JIRA's database. The related tables are titled issuetype and optionconfiguration.
Insert a new row into the optionconfiguration table, with the missing issue type on the column optionid and scheme on fieldconfig
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Ensure that the
optionidcolumn from theoptionconfigurationtable is the same asidcolumn from theissuetypetable.Ensure that the Default Issue Type Scheme is represented by the value '10000' in the "fieldconfig" column of the
optionconfigurationtable.Restart JIRA after editing.
For example, running the following code will pull up all the necessary information for the issue type 'Bug'
1
2
3
SELECT * FROM issuetype
LEFT OUTER JOIN optionconfiguration ON issuetype.id = optionconfiguration.optionid
WHERE pname='Bug';
id | sequence | pname | pstyle | description | iconurl | id | fieldid | optionid | fieldconfig | sequence |
1 | 1 | Bug | A problem which impairs or prevents the functions of the product. | /images/icons/bug.gif | 10030 | issuetype | 1 | 10001 | 1 | |
1 | 1 | Bug | A problem which impairs or prevents the functions of the product. | /images/icons/bug.gif | 10034 | issuetype | 1 | 10000 | 0 |
ℹ️ When inserting a new row into the optionconfiguration table, a unique id must be given.
Based on the example above, the SQL Query will be something like this (the third value (1) is the Bug's ID):
1
2
3
4
5
6
7
8
INSERT INTO optionconfiguration
VALUES (
(select seq_id from sequence_value_item where seq_name = 'OptionConfiguration'),
'issuetype',
1,
10000,
(select max(sequence) from optionconfiguration where fieldconfig = 10000)+1
);Please note the first value is bringing the next ID for this table from the sequence_value_item table, so we need to increment this table after that:
1
UPDATE sequence_value_item SET seq_id = (seq_id+1) WHERE seq_name = 'OptionConfiguration';Workaround #2
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
We'll need to add the missing reference back into the table:
Shutdown JIRA
Run the SQL query again to get the needed configuration details:
1select fit.id,fit.fieldconfigscheme,fs.id as fieldconfigid,fs.configname,fs.fieldid from fieldconfigschemeissuetype fit full outer join fieldconfigscheme fs on fit.fieldconfiguration=fs.id where fit.fieldconfiguration is null;
Take note of the
fieldconfigidreturnedInsert the following into the table:
1insert into fieldconfigschemeissuetype values ((select max(id)+1 from fieldconfigschemeissuetype),null,XXXXXX,XXXXXX);ℹ️ replace both of the XXXXX values with the
fieldconfigidfound in the first query.Restart JIRA
Workaround #3
Remove the invalid entry from the optionconfiguration table
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Usually, there are entries for issue types on the optionconfiguration table which is not on the issuetype table.
Delete the invalid entries;
1DELETE FROM optionconfiguration WHERE optionid NOT IN (SELECT id FROM issuetype);Run the query on Cause #2 again to make sure all invalid entries were removed;
Restart JIRA
Was this helpful?