Issue Summary

Upgrade task 'AUT_0025_DeleteLeftOverCustomFields' failed with foreign key constraint violation

Steps to Reproduce

1. On a JSM version below 10.6.0, create/configure a custom field for a Service Management request type.
2. Hide the field from the request type and set a default value.
3. Delete the custom field from the custom fields page.
4. Upgrade the instance to JSM 10.6.1 with zero downtime or manual upgrade.

Expected Results

Upgrade completed without any specific error around the AUT_0025_DeleteLeftOverCustomFields upgrade task. For upgrading with zero downtime, it's possible to finalize the upgrade.

Actual Results

After clicking on the "Finalize the upgrade" button, one of the upgrade tasks failed and the "Rerun upgrade task" button appears. There's an error with the AUT_0025_DeleteLeftOverCustomFields upgrade task in the atlassian-jira.log.

2025-06-12 10:27:41,284+0200 Caesium-1-4 INFO ServiceRunner     [c.a.s.b.upgrade.tasks.AUT_0025_DeleteLeftOverCustomFields] Starting upgrade task 'AUT_0025_DeleteLeftOverCustomFields' (AUT_0025_DeleteLeftOverCustomFields), build number: 25
2025-06-12 10:27:41,285+0200 Caesium-1-4 INFO ServiceRunner     [c.a.s.b.upgrade.tasks.AUT_0025_DeleteLeftOverCustomFields] Commencing the AUT_0025 - removing leftover custom fields from viewportfield database
2025-06-12 10:27:41,297+0200 Caesium-1-4 INFO ServiceRunner     [c.a.s.b.upgrade.tasks.AUT_0025_DeleteLeftOverCustomFields] Removing unused reference 'customfield_10200' from viewportfield database
2025-06-12 10:27:41,300+0200 Caesium-1-4 ERROR ServiceRunner     [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [com.atlassian.servicedesk,buildNumber=25] failed
com.querydsl.core.QueryException: Caught PSQLException for delete from "public"."AO_54307E_VIEWPORTFIELD" where "AO_54307E_VIEWPORTFIELD"."FIELD_ID" = ?
...
	at com.atlassian.servicedesk.internal.feature.customer.request.requesttype.field.RequestTypeFieldManagerImpl.deleteFieldByFieldId(RequestTypeFieldManagerImpl.java:312)
	at com.atlassian.servicedesk.bootstrap.upgrade.tasks.AUT_0025_DeleteLeftOverCustomFields.deleteCustomField(AUT_0025_DeleteLeftOverCustomFields.java:45)
	at java.base/java.lang.Iterable.forEach(Iterable.java:75)
	at com.atlassian.servicedesk.bootstrap.upgrade.tasks.AUT_0025_DeleteLeftOverCustomFields.doUpgrade(AUT_0025_DeleteLeftOverCustomFields.java:34)
...
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "AO_54307E_VIEWPORTFIELD" violates foreign key constraint "fk_ao_54307e_viewportfieldvalue_field_id" on table "AO_54307E_VIEWPORTFIELDVALUE"
  Detail: Key (ID)=(67) is still referenced from table "AO_54307E_VIEWPORTFIELDVALUE".
...
2025-06-12 10:27:41,304+0200 Caesium-1-4 WARN ServiceRunner     [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Failed to complete upgrade task [factory=com.atlassian.servicedesk,buildNumber=25]
2025-06-12 10:27:41,306+0200 Caesium-1-4 ERROR ServiceRunner     [c.a.jira.upgrade.UpgradeScheduler] Error occurred during execution of upgrades 
com.atlassian.jira.upgrade.UpgradeException
 * Plugin upgrade(s) failed
	at com.atlassian.jira.upgrade.UpgradeScheduler.runPluginUpgrades(UpgradeScheduler.java:111)
	at com.atlassian.jira.upgrade.UpgradeScheduler.runUpgrades(UpgradeScheduler.java:82)
...

The upgrade task above is from the fix of JSDSERVER-15464 Deleting custom fields used in Request types leaves information in the Jira database that should be removed. It's trying to delete the orphaned field data from the "AO_54307E_VIEWPORTFIELD" table but unable to, as the ID is still referenced by another row in the "AO_54307E_VIEWPORTFIELDVALUE" table, which stores the default value of a hidden request type field.

Workaround

1. A possible workaround is to manually delete the records from the "AO_54307E_VIEWPORTFIELD" table. The data to be deleted is visible in the log, which we could use to verify and remove the data as needed.
2. Another possible workaround is to delete the request type that has the orphaned custom field configured. This configuration is not visible in the UI and hence, it needs to be identified based on the information from the database. For example, the name of the request type can be found in "AO_54307E_VIEWPORTFORM".

After identifying the data to be removed, back up Jira and delete the data first from "AO_54307E_VIEWPORTFIELDVALUE", follow by "AO_54307E_VIEWPORTFIELD". Rerun the upgrade task if you are upgrading with zero downtime. If you upgraded Jira manually, restart Jira and the failed upgrade task will be re-attempted.

In the example log above, the unused reference is 'customfield_10200', and the ID is 67 on the "AO_54307E_VIEWPORTFIELD."

• This query below checks for the data to be removed. Please modify the syntax and value accordingly depending on your database.

  select * from "AO_54307E_VIEWPORTFIELD" where "ID" = 67 or "FIELD_ID" = 'customfield_10200'
  

  • Result

    DESCRIPTION|DISPLAYED|FIELD_ID         |FIELD_ORDER|FIELD_TYPE|FORM_ID|ID|LABEL |REQUIRED|
    -----------+---------+-----------------+-----------+----------+-------+--+------+--------+
               |false    |customfield_10200|          0|radio     |      1|67|Button|false   |
    

• This query verifies if the ID to be deleted from "AO_54307E_VIEWPORTFIELD" is indeed referenced by this table for the orphaned custom field. Please modify the syntax and value accordingly depending on your database.

  select * from "AO_54307E_VIEWPORTFIELDVALUE" where "FIELD_ID" = 67
  

  • Result

    FIELD_ID|FIELD_NAME       |ID|VALUE|VALUE_ORDER|
    --------+-----------------+--+-----+-----------+
          67|customfield_10200| 1|10100|          0|
    

• This query checks for all referenced custom fields that can be found in "AO_54307E_VIEWPORTFIELDVALUE" but do not exist in the customfield table. Please modify the syntax and value accordingly depending on your database.

  with cflist as (
    select substring("FIELD_NAME", 13, 5) as cfid from "AO_54307E_VIEWPORTFIELDVALUE")
  select CAST(cfid AS varchar) from cflist where cfid not in (select CAST(id AS varchar) from customfield)
  

  • Result

    cfid |
    -----+
    10200|
    

Versions Tested

Upgrading from 10.5.1 to 10.6.1