Connection problems to PostgreSQL result in stuck threads in Jira

Still need help?

The Atlassian Community is here for you.

Ask the community

Setting socketTimeout is no longer advised as it may cause problems with longer database queries that Jira may perform, for example during upgrade tasks. Please see:   JRASERVER-74314 - Getting issue details... STATUS

This property will no longer be added to database configuration since Jira 9.4 and will be ignored for existing configurations.   

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

Issue

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.

Context

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)

Solution

To solve this problem:

  1. 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.

    1. To do that, download the driver from https://jdbc.postgresql.org/
    2. Stop Jira
    3. Copy the new driver to <jira-install>/lib
    4. Remove the old one
    5. Start Jira
  2. Edit the dbconfig.xml file, and add the following properties between the <jdbc-datasource> or  <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.)

  3. <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.

  4. Restart Jira.

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:

(warning) Please ensure that version 42.2.19 or later is used to ensure that the socketTimeout connection property works as expected. 

Last modified on Jun 5, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.