Issue Types missing, blank, or duplicated in Issue Type Schemes in Jira server
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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. The below modification is not guaranteed to be supported by Atlassian, and any changes should be made at your 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 query below:
SELECT * FROM optionconfiguration WHERE optionid NOT IN (SELECT id FROM issuetype);Databases and table names may be case-sensitive and use different casing depending on the OS or DBMS. Therefore, if the query doesn't work on the first try, you might want to check the Tables and Columns in 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 query below:
SELECT * FROM issuetype WHERE id NOT IN (SELECT optionid FROM optionconfiguration);Databases and table names may be case-sensitive and use different casing depending on the OS or DBMS. Therefore, if the query doesn't work on the first try, you might want to check the Tables and Columns in 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 is missing. These are stored within the fieldconfigschemeissuetype and we can confirm if there is a problem by running the following SQL query:
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:
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 manipulating data directly in Jira's database. The related tables are titled issuetype and optionconfiguration.
Insert a new row into the optionconfiguration table, with the missing issue type in the column optionid and scheme on fieldconfig
Warning: Backup your database before proceeding.
Before running any SQL in this article, create a full backup of your Jira database. This is a database-level operation with no undo other than restoring from backup. Only proceed if you're comfortable running SQL directly against the Jira database, and test on a staging or cloned environment first, wherever possible.
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'
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):
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 that 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:
UPDATE sequence_value_item SET seq_id = (seq_id+1) WHERE seq_name = 'OptionConfiguration';Workaround #2
Warning: Backup your database before proceeding.
Before running any SQL in this article, create a full backup of your database. This is a database-level operation with no undo other than a backup restore. Only proceed if you're comfortable running SQL directly against the database, and test on a staging or cloned environment first, wherever possible.
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:
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;
Take note of the
fieldconfigidreturnedInsert the following into the table:
insert 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
Warning: Backup your database before proceeding.
Before running any SQL in this article, create a full backup of your database. This is a database-level operation with no undo other than a backup restore. Only proceed if you're comfortable running SQL directly against the database, and test on a staging or cloned environment first, wherever possible.
Usually, there are entries for issue types on the optionconfiguration table, which is not on the issuetype table.
Delete the invalid entries:
DELETE FROM optionconfiguration WHERE optionid NOT IN (SELECT id FROM issuetype) AND fieldid = 'issuetype';Run the query on Cause #2 again to make sure all invalid entries were removed.
Restart Jira.
Was this helpful?