Custom Fields swapped in Screens and NonUniqueResultException error in the logs after Jira upgrade

Issue Summary

This is reproducible on Data Center: No (only seen in customer instances thus far)

Steps to Reproduce

1. Upgrade Jira to 9.7
2. Check if there are any results from the query below

   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"
   from fieldconfigschemeissuetype fcsit
   join fieldconfigscheme fcs 
     on fcs.id = fcsit.fieldconfigscheme
   join fieldconfiguration fc 
     on fc.id = fcsit.fieldconfiguration
   where fcs.fieldid <> fc.fieldid
   order by fcs.fieldid, fc.fieldid;
   

Expected Results

All fields appear in Screens are they are configured and it's possible to update their contexts freely.

Actual Results

1. Some other fields show up in screens instead of the fields configured.
2. It's not possible to update their context as the operation in Jira fails.
3. The query above also returns rows which match the fields involved in the swapping.
4. The below exception is thrown in the atlassian-jira.log file:

   2023-08-23 08:28:55,624+0000 https-jsse-nio-8443-exec-77 url: /secure/admin/EditCustomFieldDefaults!default.jspa, /secure/admin/views/customfields/editcustomfield-defaults.jsp; user: redacted-username ERROR redacted-username 503x12844411x30 sessionid 169.0.0.1 /secure/admin/EditCustomFieldDefaults!default.jspa [webwork.util.ValueStack] query="/customFieldHtml" {[id="null" type="5" values=""]} {[id="customFieldHtml" type="8" values=""]}
   java.lang.reflect.InvocationTargetException
   ...
   Caused by: com.querydsl.core.NonUniqueResultException: Only one result is allowed for fetchOne calls
   ...
   

Workaround

A known cause for this situation is mismatching entries in the fieldconfigschemeissuetype table: a fieldconfigscheme from field "A" is linked to a fieldconfiguration from field "B". (though the root cause of that is still under investigation)

To fix this, we may update the fieldconfigschemeissuetype.fieldconfiguration to a matching entry.

Ideally, you should refer to a backup from before the upgrade or before you noticed the symptom.

The safest and advised steps to perform the fix are:

1. Stop Jira on all nodes
2. Backup Jira's DB
3. Perform the DB updates
4. Start Jira one node at a time
5. Validate and adjust the custom fields' context if needed through the Admin UI on the affected fields
6. Perform a Full Reindex in a node (or Background Reindex only if single node)

Workaround 1: Fixing from a backup

Run this query on the affected instance DB:

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"
from fieldconfigschemeissuetype fcsit
join fieldconfigscheme fcs 
  on fcs.id = fcsit.fieldconfigscheme
join fieldconfiguration fc 
  on fc.id = fcsit.fieldconfiguration
where fcs.fieldid <> fc.fieldid
order by fcs.fieldid, fc.fieldid;

You'll get a list of all mismatching fieldconfigschemeissuetype:

 fcsit.id | fcsit.issuetype | fcs.id |    fcs.fieldid    | fc.id |    fc.fieldid     
----------+-----------------+--------+-------------------+-------+-------------------
   133530 |                 |  18276 | customfield_11439 | 18276 | customfield_17332
   133531 | 10205           |  45801 | customfield_11513 | 45801 | customfield_38610
   133532 | 10001           |  45801 | customfield_11513 | 45801 | customfield_38610
   133533 | 19100           |  45801 | customfield_11513 | 45801 | customfield_38610
   128014 | 10204           |  18266 | customfield_17329 | 18266 | customfield_13905
   128015 | 10105           |  18266 | customfield_17329 | 18266 | customfield_13905
(6 rows)

Notice how the fcs.id and fc.id are the same but the respective fieldid is different.

Restore a DB backup from before the upgrade or before the issue was observed and run the query for each of the fcsit.id reported in the first query.
(relying on a lower environment with recent Prod data may also suffice, as long as the records are there)

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"
from fieldconfigschemeissuetype fcsit
join fieldconfigscheme fcs 
  on fcs.id = fcsit.fieldconfigscheme
join fieldconfiguration fc 
  on fc.id = fcsit.fieldconfiguration
where fcs.fieldid <> fc.fieldid
order by fcs.fieldid, fc.fieldid;

Run the query below:

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 fcsit.id in (133530, 133531, 133532, 133533, 128014, 128015) /* fcsit.id list from the previous query */
order by fcs.fieldid, fc.fieldid;

Now update each affected fieldconfigschemeissuetype.fieldconfiguration with the original/previous value:

update fieldconfigschemeissuetype set fieldconfiguration = <original fc.id> where id = <affected fcsit.id>;

Do that for each entry.

Workaround 2: Fixing from a candidate entry

If no DB backup's available or if the fcsit.id don't match between the DB instances, you may fix the fieldconfigschemeissuetype entries with a potential candidate and then verify and adjust through the Admin UI if needed.
Eliminating the mismatch (and duplicate entries resulting from that) will make the Admin UI work again for the affected fields.

Run this query on the affected instance:

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",
  coalesce(fc_fix1.id, fc_fix2.id) as "fc_fix.id",
  coalesce(fc_fix1.fieldid, fc_fix2.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_fix1 
  on fc_fix1.fieldid = fcs.fieldid 
  and fc_fix1.id = (
    select 
      min(fc_fix_first.id)
    from fieldconfiguration fc_fix_first 
    where 
      fc_fix_first.fieldid = fc_fix1.fieldid 
      and fc_fix_first.id not in (
        select fieldconfiguration from fieldconfigschemeissuetype
      )
  )
left join fieldconfiguration fc_fix2
  on fc_fix2.fieldid = fcs.fieldid 
  and fc_fix2.id = (select min(fc_fix_first.id) from fieldconfiguration fc_fix_first where fc_fix_first.fieldid = fc_fix2.fieldid)
where fcs.fieldid <> fc.fieldid
order by fcs.fieldid, fc.fieldid, fcsit.id;

It's bringing a fc_fix candidate which is:

• A fieldconfiguration for the same fieldid as the fieldconfigscheme that isn't currently associated with any fieldconfigschemeissuetype
• The first/oldest of such fieldconfiguration if many exist

For each of the returned rows, use the "fc_fix" to update the fieldconfigschemeissuetype.

Example result:

 fcsit.id | fcsit.issuetype | fcs.id |    fcs.fieldid    | fc.id |    fc.fieldid     | fc_fix.id |  fc_fix.fieldid   
----------+-----------------+--------+-------------------+-------+-------------------+-----------+-------------------
   133530 |                 |  18276 | customfield_11439 | 18276 | customfield_17332 |     18277 | customfield_11439
   133531 | 10205           |  45801 | customfield_11513 | 45801 | customfield_38610 |     45803 | customfield_11513
   133532 | 10001           |  45801 | customfield_11513 | 45801 | customfield_38610 |     45803 | customfield_11513
   133533 | 19100           |  45801 | customfield_11513 | 45801 | customfield_38610 |     45803 | customfield_11513
   128014 | 10204           |  18266 | customfield_17329 | 18266 | customfield_13905 |     18267 | customfield_17329
   128015 | 10105           |  18266 | customfield_17329 | 18266 | customfield_13905 |     18267 | customfield_17329
(6 rows)

Example update:

update fieldconfigschemeissuetype set fieldconfiguration = 18277 where id in (133530);

update fieldconfigschemeissuetype set fieldconfiguration = 45803 where id in (133531, 133532, 133533);

update fieldconfigschemeissuetype set fieldconfiguration = 18267 where id in (128014, 128015);