Have the Database Integrity Checker check for inconsistencies in the fieldconfigschemeissuetype table

Inconsistencies in the fieldconfigschemeissuetype table can lead to some unexpected behavior in Jira, like:

• Other fields showing up in Screens instead of the ones configured
• Other fields context showing up when trying to edit another's context
• Admins unable to edit some fields because there are duplicate field configurations (NonUniqueResultException)

Entries in the fieldconfigschemeissuetype match together fieldconfigscheme and fieldconfiguration for the same custom field, and this is done by each respective table's fieldid column.

Any entry that doesn't match by the fieldid is a potential issue and may cause the duplicate error:

select distinct 
  fcsit.id as "fcsit.id", 
  fcsit.issuetype as "fcsit.issuetype", 
  fcs.id as "fcs.id", 
  fcs.fieldid as "fcs.fieldid",
  fc.id as "fc.id", 
  fc.fieldid as "fc.fieldid",
  fc_fix.id as "fc_fix.id",
  fc_fix.fieldid as "fc_fix.fieldid"
from fieldconfigschemeissuetype fcsit
join fieldconfigscheme fcs 
  on fcs.id = fcsit.fieldconfigscheme
join fieldconfiguration fc 
  on fc.id = fcsit.fieldconfiguration
left join fieldconfiguration fc_fix 
  on fc_fix.fieldid = fcs.fieldid 
  and fc_fix.id not in (select fieldconfiguration from fieldconfigschemeissuetype)
where fcs.fieldid <> fc.fieldid
order by fcs.fieldid, fc.fieldid;

The key clause is fcs.fieldid <> fc.fieldid, and the left join to "fc_fix" tries to match an orphaned fieldconfiguration that would likely be the correct one for each mismatching entry.

This validation was motivated by:

• JRASERVER-76312 — Custom Fields swapped in Screens and NonUniqueResultException error in the logs after Jira upgrade