Summary

In Jira Data Center 8.20.10, users may run into a problem where Issues will fail to load when attempting to view it, leading to the followng errors being displayed:

"This issue can't be displayed right now"
"This issue can no longer be opened"

Environment

Jira Data Center 8.20.10

Diagnosis

Within the atlassian-jira.log a variation of the following errors may be present: 

2022-12-17 01:03:37,576+0100 http-nio-8092-exec-2228 ERROR kkrd446 63x33567492x3 1cqdlge 1.1.1.1,2.2.2.2,3.3.3.3 /secure/AjaxIssueAction!default.jspa [c.o.s.c.j.f.editable.database.DatabasePickerImpl] Error rendering field customfield_25602 retrieving value: Company CLEAN_ADDRESS Change
java.sql.SQLTransientConnectionException: SN_Model_New - Connection is not available, request timed out after 30000ms.
... 
2022-12-17 01:04:07,583+0100 http-nio-8092-exec-2228 ERROR kkrd446 63x33567492x3 1cqdlge 1.1.1.1,2.2.2.2,3.3.3.3 /secure/AjaxIssueAction!default.jspa [c.o.s.c.j.f.editable.database.DatabasePickerImpl] Error rendering field customfield_25604 retrieving value: ZY Legend C Change Approval
java.sql.SQLTransientConnectionException: SN_Model_New - Connection is not available, request timed out after 30000ms.

Cause

According to the errors returned from the logs, it points to an issue with specific custom fields of a particular Issue.
We can run the following query below against Jira's database to see more details on the particular custom fields by passing the ID:

SELECT * FROM customfield
WHERE id = 25602
OR id = 25604

SELECT * FROM customfieldvalue
WHERE customfield = 25602
OR customfield = 25604

This issue can potentially be caused by a database connection pool limit being hit, per references to java.sql.SQLTransientConnectionException.
To confirm that database connections are causing this, we can use the Database Monitoring page while replicating the issue. 

If we see that the number of connections max out or comes close to the maximum then the issue is likely related to the size of the database connection pool.

See Monitoring database connection usage for more information.

Solution

If the issue is related to a database connection pool limit, we can perform the following steps to increase the timeout value, per our tuning database connections KBA:

Editing the dbconfig.xml file to start Jira 's database connections

You can manually set values within the dbconfig.xml file at the root of your Jira home directory:

1. Edit and increase the following attributes to a higher value:
   
   1. <pool-max-size>60</pool-max-size>
2. Save your edited dbconfig.xml file.
3. Restart your Jira installation.

Use Jira Configuration Tool to start Jira 's database connections

Alternatively, you can use Jira Configuration Tool to perform this:

1. Start the Jira configuration tool:
   
   • Windows: Open a command prompt and run config.bat in the bin sub-directory of the Jira installation directory.

   • Linux/Unix: Open a console and execute config.sh in the bin sub-directory of the Jira installation directory.

     This command might fail with the error as described in Unable to Start Jira applications Config Tool due to No X11 DISPLAY variable was set error. 

     If this happens, please refer to the article referenced above for the workaround.
   
   You may need to set the JAVA_HOME environment variable to run the Jira  configuration tool. See Installing Java for details.
   
2. Once the Jira configuration tool is running, select the Advanced tab.
   
   
   
   
3. To specify a value for one of these options, ensure that its leftmost checkbox has been selected first.
4. Increase the value for the Maximum Size attribute.
5. Save your changes. They will be stored as elements in your dbconfig.xml file.

Refer to the Connection pool settings for more information about the options on this tab.