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.

SELECT * FROM optionconfiguration WHERE optionid NOT IN (SELECT id FROM issuetype);

Cause #2

An issue type exists but is not present on the optionconfiguration table. To check this, run the below query.

SELECT * FROM issuetype WHERE id NOT IN (SELECT optionid FROM optionconfiguration);

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:

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;

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

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';

 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 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

We'll need to add the missing reference back into the table:

1. Shutdown JIRA
2. Run the SQL query again to get the needed configuration details:
   

   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;

3. Take note of the fieldconfigid returned

4. Insert 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 fieldconfigid found in the first query.

5. Restart JIRA

Workaround #3

Remove the invalid entry from the optionconfiguration table

Usually, there are entries for issue types on the optionconfiguration table which is not on the issuetype table.

1. Delete the invalid entries;

   DELETE FROM optionconfiguration WHERE optionid NOT IN (SELECT id FROM issuetype);

2. Run the query on Cause #2 again to make sure all invalid entries were removed;
3. Restart JIRA