Multiple entries for the same issue type under "Default Issue Type Scheme"
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
Problem
There are multiple entries for the same issue type on the Default Issue Type Scheme and it is not possible to delete or remove those through the interface.
Diagnosis
Run this query to confirm there are duplicate issue types on the Default Issue Type Scheme. It will show how many entries exist for each issue type.
1
SELECT COUNT(oc.id), it.pname FROM optionconfiguration oc JOIN issuetype it ON oc.optionid = it.id WHERE oc.fieldconfig = '10000' GROUP BY it.pname HAVING COUNT(it.id) > 1;Cause
Unknown, at the moment. It could be caused by direct manipulation of JIRA's database or any other kind of data corruption.
Solution
Resolution
Shutdown JIRA;
Check for the highest ID among the multiple entries:
1SELECT it.pname, MAX(oc.id) FROM optionconfiguration oc JOIN issuetype it ON oc.optionid = it.id WHERE oc.fieldconfig = 10000 GROUP BY it.pname HAVING count(it.id) > 1;Delete entries with those IDs from the
optionconfigurationtable:1DELETE FROM optionconfiguration WHERE id IN (SELECT MAX(oc.id) FROM optionconfiguration oc JOIN issuetype it ON oc.optionid = it.id WHERE oc.fieldconfig = 10000 GROUP BY it.pname HAVING count(it.id) > 1);It is extremely important that you must take a snapshot/backup of the database prior to executing the
DELETEqueries onto the production database.Repeat steps 2 and 3 until step 1 returns an empty set.
ℹ️ This is required in some cases where we have more than 2 entries for each issue type;
Start JIRA and check if the multiple entries are gone;
Was this helpful?