DB connections are timed out in SQ 9.8

SonarQube
1

Must-share information (formatted with Markdown):

  • which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)
    sonarqube9.8
  • how is SonarQube deployed: zip, Docker, Helm
    zip
  • what are you trying to achieve
    Our database setup is based on a Patroni cluster. At the moment SQ is directly connected to master db node. But we want to put a load balancer in between SQ server and patroni. We tried this change. Sonar re-started. After a few minutes we were seeing connection timeouts for db connection pool. This makes many pipelines to fail or loading global configuration fails too.
  • what have you tried so far to achieve this
    We have gone through this thread
    DB connections get stuck timing out - #4 by Q_Jones
    This tells to set some timeout config in sonar.properties. Could you please elaborate about this config. If our loadbalancer timeout is set to 120 secs at the moment, which of the following properties needs calibration.
    sonar.jdbc.idleTimeout , default 600000
    sonar.jdbc.keepaliveTime , default 300000
    sonar.jdbc.maxLifetime , default 1800000
    sonar.jdbc.validationTimeout , default 5000
    Do not share screenshots of logs – share the text itself (bonus points for being well-formatted)!
    Error that we got in web.log
2023.03.20 07:52:44 ERROR web[AYb9pdV3N7uPTcHQAAik][o.s.s.w.WebServiceEngine] Fail to process request http://mycompanyserver/api/settings/values.protobuf?
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 10014ms.
### The error may exist in org.sonar.db.component.ComponentMapper
### The error may involve org.sonar.db.component.ComponentMapper.selectByKeyAndBranchOrPr
### The error occurred while executing a query
### Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 10014ms.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)

======== Also the below warning

2023.03.20 22:33:15 WARN  ce[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@7d5a5364 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2

Hi,

Basically, what’s happening is that your load balancer is closing connections while SonarQube still thinks they’re usable.

For the properties, the best source is probably the horse’s mouth

:warning: Make sure you upgrade to SonarQube v9.9 LTS soon, not only to benefit from our Best LTS Ever™, but because soon we will systematically ask users to upgrade when they ask questions about earlier versions of SonarQube, which are now considered unsupported. :smiley:

 
HTH,
Ann

3

Hi Ann,
Thanks for sharing this link. Awesome. Yes the upgrade is in plan once we have done with LB issues. :smiley:

Cheers,
Jiju

1 Like
7

@gopaji , just to share, this topic was initiated by another thread which generated a ticket for documentation.

It’s not released yet (when I wrote this reply)… but we plan to add the Hikari-specific property description on the Setup and upgrade > Troubleshooting page in the SonarQube 9.9 & 10.0 documentation.

1 Like