Custom Field usage not available due to CustomFieldUsageRecalculationJob timing out
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
When analyzing Custom Field usage as described on Analyzing the usage of custom fields, the "Issues: last refresh" on the top right shows "never" or has not been updated in over a day.
The data displayed on Last value update and Issues columns are made available by the following scheduled job:
com.atlassian.jira.issue.fields.usage (also known as CustomFieldUsageRecalculationJob)
If this job is not running properly the data will not get updated.
Environment
8.16+ (when the screen was made available)
Diagnosis
The logs may display the following error message when the job times out:
1
2
3
4
5
6
2022-03-09 08:00:00,070+0000 Caesium-1-1 INFO ServiceRunner [c.a.j.i.fields.usage.CustomFieldUsageDataService] Start collecting db data about `Issues with values` for 1400 custom fields, batches: 2, max batch size 1000
2022-03-09 08:00:00,070+0000 Caesium-1-1 INFO ServiceRunner [c.a.j.i.fields.usage.CustomFieldUsageDataService] Collecting db data about `Issues with values` for 1000 of 1067 custom fields, for batch 1 of 2
2022-03-09 08:00:00,070+0000 Caesium-1-1 DEBUG ServiceRunner [c.a.j.i.fields.usage.CustomFieldUsageDAO] Collecting numbers of issues with value for 1000 custom fields
.... 4 minutes later ...
2022-03-09 08:04:00,119+0000 Caesium-1-1 ERROR ServiceRunner [c.a.jira.database.DatabaseAccessorImpl] Unable to rollback SQL connection.
org.postgresql.util.PSQLException: This connection has been closed.panelFor reference, the above was collected on PostgreSQL with <connection-properties>tcpKeepAlive=true;socketTimeout=240</connection-properties> configured on dbconfig.xml.
This instance had 1400 custom fields.
Cause
The DB connection has a timeout that is lesser than the time it takes for the job to run its query.
The query is a variation of the following:
1
2
3
select count(distinct CUSTOM_FIELD_VALUE.issue), CUSTOM_FIELD_VALUE.customfield
from public.customfieldvalue CUSTOM_FIELD_VALUE
where CUSTOM_FIELD_VALUE.customfield in (SELECT id FROM public.customfield) group by CUSTOM_FIELD_VALUE.customfield;Since the job is limited to 1000 custom field at a time by default, the "SELECT id FROM public.customfield" is replaced by an explicit list of 1000 custom field IDs.
As an admin you can check the status of the job by going to <JIRA_BASE_URL>/secure/admin/SchedulerAdmin.jspa and looking for CustomFieldUsageRecalculationJob. Click "Show more" on the far-right. Here's a screenshot of how a timeout error would look like on that page:
Solution
Option #1:
Remove or increase the socketTimeout to a setting that will cover the time it takes for the query to run.
Keep in mind this setting affects all interactions with the database, not only for the batch job meaning all queries will have a unlimited/greater timeout value.
Option #2:
Since there are times you'd prefer to keep the socketTimeout, an alternative is to modify the job so it fetches a smaller set of custom fields at a time.
The default setting will fetch all issue data related to a maximum of 1000 custom fields. By changing the com.atlassian.jira.issue.fields.usage.query.max.ids parameter to 100, the job will run more times with smaller sets of custom fields and each run is more likely to fit into the timeout setting.
This setting can be changed by adding the following line to yourjira-config.properties to each of your nodes.
com.atlassian.jira.issue.fields.usage.query.max.ids=100
More details about jira-config.properties file can be found here: Edit the jira-config.properties file in Jira server
Restart the nodes one at a time after modifying the file for the change to take effect.
The jira-config.properties file is located on your local home directory. If jira-config.properties is not there you can create a brand new and add the line.
Note that the CustomFieldUsageRecalculationJob runs at 3AM server time by default so check it again the next to ensure it finished and that the Custom Field usage information is available.
Was this helpful?