Postgres connection getting closed

[mannems (Sandeep Mannem)]

The database connection is closing after 25-30 minutes of the SonarQube server being started.

SonarQube Developer version: 10.2
Postgres version: 14.1
We are running Sonarqube developer edition in linux server inside podman container and we are using postgres database. Please see below error logs
Tried using the hikari properties provided in troubleshooting page in sonarqube, no luck.

2023.11.14 19:00:06 WARN  ce[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@7901c983 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
2023.11.14 19:00:06 ERROR ce[][o.s.c.t.CeWorkerImpl] Failed to pop the queue of analysis reports
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 10023ms.
### The error may exist in org.sonar.db.property.InternalPropertiesMapper
### The error may involve org.sonar.db.property.InternalPropertiesMapper.selectAsText
### The error occurred while executing a query
### Cause: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 10023ms.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:156)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:142)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at jdk.proxy2/jdk.proxy2.$Proxy53.selectAsText(Unknown Source)
	at org.sonar.db.property.InternalPropertiesDao.selectByKey(InternalPropertiesDao.java:179)
	at org.sonar.ce.queue.CeQueueImpl.getWorkersPauseStatus(CeQueueImpl.java:350)
	at org.sonar.ce.queue.InternalCeQueueImpl.peek(InternalCeQueueImpl.java:78)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.tryAndFindTaskToExecute(CeWorkerImpl.java:170)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.findAndProcessTask(CeWorkerImpl.java:153)
	at org.sonar.ce.taskprocessor.CeWorkerImpl$TrackRunningState.get(CeWorkerImpl.java:135)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:87)
	at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:53)
	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:75)
	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 10023ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
	at org.sonar.db.profiling.NullConnectionInterceptor.getConnection(NullConnectionInterceptor.java:31)
	at org.sonar.db.profiling.ProfiledDataSource.getConnection(ProfiledDataSource.java:112)
	at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:145)
	at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:67)
	at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:345)
	at org.apache.ibatis.executor.ReuseExecutor.prepareStatement(ReuseExecutor.java:91)
	at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:61)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
	... 24 common frames omitted
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
	at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:993)
	at org.postgresql.jdbc.PgConnection.setNetworkTimeout(PgConnection.java:1694)
	at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:561)
	at com.zaxxer.hikari.pool.PoolBase.isConnectionDead(PoolBase.java:168)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:170)
	... 38 common frames omitted

[Colin]

Hey there. Are you using Kerberos? You might be affected by this.

After upgrading from SonarQube 8.9.9 to 9.9.2 we randomly see kerberos authention issue

Hi Jonas, In fact, after answering, I poked around a little more. We don’t actually expect there to be a code fix for this. The Jira ticket I pointed you to isn’t updated because we’re waiting for final confirmation, but it looks like settings will probably handle this. Try setting fixed connection pool and indefinite lifetime for the connections when using Kerberos and SQL Server: sonar.jdbc.minIdle=25 sonar.jdbc.maxActive=25 sonar.jdbc.maxLifetime=0 sonar.jdbc.maxWait=30000 And since this i…

[mannems (Sandeep Mannem)]

@Colin We are not using kerberos and tried the settings anyway but did not work

[Colin]

I may have been a bit daft suggesting that anyways – I just realized your post mentions Postgres, not Microsoft SQL Server.

In this similar thread, it was related to memory exhaustin on the Postgres database side.

Database connection dies on report upload

some more log lines 2021.06.17 06:11:54 ERROR ce[][o.s.c.t.CeWorkerImpl] Failed to pop the queue of analysis reports org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: org.postgresql.util.PSQLException: Connection to pd-sonarqube:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. ### The error may exist in org.sonar.db.property.InternalPropertiesMapper ### The error may involve org.sonar.db.pr…

Are you sure your Postgres DB is sized appropriately? It would be useful to have a DBA (if you have one around) monitor memory / CPU utilization.

[mannems (Sandeep Mannem)]

This issue is resolved, we found that podman configuration is causing the issue. Installed sonarqube as zip file directly in server instead of docker/podman resolved the issue.