Make Jira DBCP connection refresh checks more frequent

Problem Definition

Jira uses DBCP library to manage the connection pool to DB. As part of it, it has validation-query which runs periodically to check if connections are healthy, so it can be used by the Java code without getting the connection error.

In the case of a large DB pool (100+ connections) or aggressive network environment when connections to DB can be dropped frequently, the current setting of 300000 ms (300 sec/5 min) is too high. The thread running validation runs very infrequently and doesn't have a chance to check all connections in the pool. Details from BasicDataSource Configuration Parameters

• timeBetweenEvictionRunsMillis (-1) - The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.
• numTestsPerEvictionRun (3) - The number of objects to examine during each run of the idle object evictor thread (if any).

This leads to situations that some connections are stale and expire, so later when Java code uses them, it will receive an Error: "The last packet successfully received from the server was 34,123,456 milliseconds ago ... Caused by: java.net.SocketException: Connection reset"
Full error:

2020-10-23 06:16:36,579 JiraTaskExectionThread-1 WARN admin 376x1580x2 hpvzys 10.196.197.9 /rest/api/2/reindex [c.a.jira.index.AccumulatingResultBuilder] com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following <Trim>
(The last packet successfully received from the server was 34,191,605 milliseconds ago.  The last packet sent successfully to the server was 34,191,607 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.)
java.lang.RuntimeException:  ....
...
Caused by: java.net.SocketException: Connection reset
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:115)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
	at sun.security.ssl.OutputRecord.writeBuffer(OutputRecord.java:431)
	at sun.security.ssl.OutputRecord.write(OutputRecord.java:417)
	at sun.security.ssl.SSLSocketImpl.writeRecordInternal(SSLSocketImpl.java:894)
	at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:123)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3721)
	... 40 more

See also related KBs

• Operations in Jira server fail with connection pool errors in logs
• Operations in Jira server fail with communications link failure in logs with MySQL

Suggested Solution

Update the Jira docs and decrease the default value of time-between-eviction-runs-millis to make it more frequent.

Workaround

1. Change values manually in the dbconfig.xml file:

   •  <time-between-eviction-runs-millis>30000</time-between-eviction-runs-millis>

2. Save the file and restart Jira.