Nonexistent options for custom field of type Select List (cascading) showing in Jira Statistic and other Gadgets

Summary

For a cascading custom field the Jira Statistic Gadget displays results, that are not part of the configured options. This can also happen with 3rd party gadgets i.e. when using the Rich Filters for Jira Dashboard plugin from Qotilabs

Diagnosis

1. Find the customfield ID for the affected field (see How to find id for custom field(s)? )

2. Run the following query (substitute <customfield_id> with the ID from step 1

   1 2 3 4 5 6 select p.pname, p.pkey, j.issuenum from jiraissue j join project p on p.id = j.project where j.id in (select c.issue from customfieldvalue c where c.customfield = '<customfield_id>' and cast (c.stringvalue as integer) not in (select o.id from customfieldoption o where o.customfield = '<customfield_id>' ));

3. Check on the issues, and verify it contains valid and correct data.

Cause

The database contains invalid options for the cascading select list. The configuration and possible selections for this field have changed either by directly changing the custom field, or due to importing data. This left incorrect rows in the database, that are picked up by the gadgets.

Solution

To fix this situation you will need to remove the data directly from the database

1. Shut down Jira (ensure you have a backup just in case)

2. Run the following db query to remove all rows containing faulty data:

   1 2 3 4 delete from customfieldvalue c where c.customfield = '<customfield_id' and cast (c.stringvalue as integer) not in (select o.id from customfieldoption o where o.customfield = '<customfield_id' );

   substitute <customfield_id> with the ID of the custom field as obtained during the diagnosis step.

3. Restart Jira

4. Run a reindex

5. Check on the gadget.