Connection problems to PostgreSQL result in stuck threads in Jira
In specific network setups, problems with the connection to the PostgreSQL database can cause Jira threads to get stuck. It usually happens when the response from the database is not passed to Jira, which keeps waiting on the action to complete. A fix for this is to manually add the properties that terminate every connection after a specified time.
We’ve encountered this problem only with the PostgreSQL database and specific network setup, where you have proxies between Jira and the database. The reason for it is that data packets sent from Jira to the database are lost, and the response is never returned by the proxy, which results in stuck threads.
Usually, with problems with the database, the connection would be terminated before that happens, but in these conditions it seems like the database is still performing the action Jira is waiting for. See related issue (fixed in 8.16)
To solve this problem:
Upgrade the JDBC driver for PostgreSQL to 42.2.19 or later. This driver better handles the properties you’ll add in the next step.
- To do that, download the driver from https://jdbc.postgresql.org/
- Stop Jira
- Copy the new driver to
- Remove the old one
- Start Jira
dbconfig.xmlfile, and add the following properties between the
<jdbc-resource>tags, see example below:(This file (located at the root of your JIRA home directory) defines all details for JIRA's database connection. This file is typically created by running the JIRA setup wizard on new installations of JIRA, or by configuring a database connection using the JIRA configuration tool.)
<jira-database-config> ... <jdbc-datasource> ... <connection-properties>tcpKeepAlive=true;socketTimeout=240</connection-properties> </jdbc-datasource> </jira-database-config>
tcpKeepAlive: checks whether the connection is still running.
socketTimeout: terminates the connection after the specified time (in seconds). We’ve chosen a conservative 4 minutes, but if you tend to run SQL queries that take a long time, you can increase this value.
PostgreSQL jdbc driver version
The socketTimeout connection property was not enforced properly due to a bug in the driver. Version 42.2.15 (2020-08-14) includes a bug fix:
Additionally, a bug exists in JDBC versions prior to 42.2.19 where SocketTimeoutException was ignored and thus timeout did not work as expected. Version 42.2.19 (2021-02-18) includes a bug fix:
Please ensure that version 42.2.19 or later is used to ensure that the socketTimeout connection property works as expected.