DB connections reset

Hi Ann, as I indicated in another thread, I managed to setup SQ 9.8 with MS SQL server 2K19. It works fine for a while, all of a sudden I get the following on the comnmandline in spite of the fact that DB is up and running (I can ping it). I shutdown SQ then restart, it works again foir 15-30 minutes and get the same thing, here is the errors

2023.01.04 11:27:59 WARN  ce[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:77 ClientConnectionId: 1f253eed-d650-4930-89c3-eaeaec13f257 (The connection is closed.). Possibly consider using a shorter maxLifetime value.
2023.01.04 11:28:08 WARN  ce[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:80 ClientConnectionId: efbb7995-b2b2-4690-bec9-5154db780948 (The connection is closed.). Possibly consider using a shorter maxLifetime value.
2023.01.04 11:28:08 WARN  ce[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:78 ClientConnectionId: e3dc8b3c-f036-4f9a-858d-c7c4dec3647f (The connection is closed.). Possibly consider using a shorter maxLifetime value.
2023.01.04 11:28:08 WARN  ce[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:79 ClientConnectionId: 84dbdcc5-1c7b-47ae-a900-9c457280d0a4 (The connection is closed.). Possibly consider using a shorter maxLifetime value.
2023.01.04 11:29:57 WARN  web[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:61 ClientConnectionId: e919aed4-7c97-43f4-879e-64869c0e2b76 (The connection is closed.). Possibly consider using a shorter maxLifetime value.
2023.01.04 11:30:38 WARN  web[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:66 ClientConnectionId: c92d7c27-a576-4dc4-b68c-b1edd03d77e1 (The connection is closed.). Possibly consider using a shorter maxLifetime value.
2023.01.04 11:31:08 WARN  web[][c.z.h.pool.PoolBase] HikariPool-1 - Failed to validate connection ConnectionID:65 ClientConnectionId: 2bcf86af-27b9-4ed9-9f96-9f3f437a8b4a (The connection is closed.). Possibly consider using a shorter maxLifetime value.

the followed by the following errors when I try to analyse any project

2023.01.04 11:33:24 WARN  web[AYV74w8geL7xOITTAAA6][c.z.h.p.ProxyConnection] HikariPool-1 - Connection ConnectionID:6 ClientConnectionId: 7362ac5e-cdad-4c96-b448-18c8b775afc4 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3806)
	at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2109)
	at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6847)
	at com.microsoft.sqlserver.jdbc.TDSReader.nextPacket(IOBuffer.java:6745)
	at com.microsoft.sqlserver.jdbc.TDSReader.ensurePayload(IOBuffer.java:6718)
	at com.microsoft.sqlserver.jdbc.TDSReader.skip(IOBuffer.java:7402)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.skipValue(dtv.java:3364)
	at com.microsoft.sqlserver.jdbc.DTV.skipValue(dtv.java:162)
	at com.microsoft.sqlserver.jdbc.Column.skipValue(Column.java:152)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.skipColumns(SQLServerResultSet.java:232)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.discardCurrentRow(SQLServerResultSet.java:1731)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1784)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1048)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:976)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:333)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:308)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:201)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:60)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
	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.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:145)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at com.sun.proxy.$Proxy77.selectByUuids(Unknown Source)
	at org.sonar.db.rule.RuleDao.lambda$selectByUuids$1(RuleDao.java:71)
	at org.sonar.db.DatabaseUtils.executeLargeInputs(DatabaseUtils.java:139)
	at org.sonar.db.DatabaseUtils.executeLargeInputs(DatabaseUtils.java:124)
	at org.sonar.db.DatabaseUtils.executeLargeInputs(DatabaseUtils.java:113)
	at org.sonar.db.rule.RuleDao.selectByUuids(RuleDao.java:71)
	at org.sonar.server.rule.ws.SearchAction.doSearch(SearchAction.java:271)
	at org.sonar.server.rule.ws.SearchAction.handle(SearchAction.java:187)
	at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:111)
	at org.sonar.server.platform.web.WebServiceFilter.doFilter(WebServiceFilter.java:84)
	at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:153)
	at org.sonar.server.platform.web.SonarLintConnectionFilter.doFilter(SonarLintConnectionFilter.java:66)
	at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:153)
	at org.sonar.server.platform.web.MasterServletFilter.doFilter(MasterServletFilter.java:116)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.UserSessionFilter.doFilter(UserSessionFilter.java:81)
	at org.sonar.server.platform.web.UserSessionFilter.doFilter(UserSessionFilter.java:68)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.CspFilter.doFilter(CspFilter.java:63)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.CacheControlFilter.doFilter(CacheControlFilter.java:76)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.SecurityServletFilter.doHttpFilter(SecurityServletFilter.java:60)
	at org.sonar.server.platform.web.SecurityServletFilter.doFilter(SecurityServletFilter.java:47)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.RedirectFilter.doFilter(RedirectFilter.java:57)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.RequestIdFilter.doFilter(RequestIdFilter.java:66)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.sonar.server.platform.web.RootFilter.doFilter(RootFilter.java:65)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
	at jdk.internal.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.catalina.security.SecurityUtil.lambda$execute$0(SecurityUtil.java:280)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAsPrivileged(Subject.java:550)
	at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:311)
	at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:253)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:187)
	at org.apache.catalina.core.ApplicationFilterChain.lambda$doFilter$0(ApplicationFilterChain.java:145)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:143)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at ch.qos.logback.access.tomcat.LogbackValve.invoke(LogbackValve.java:256)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.sonar.server.app.SecureErrorReportValve.invoke(SecureErrorReportValve.java:38)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:891)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1784)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.net.SocketException: Connection reset
	at java.base/java.net.SocketInputStream.read(SocketInputStream.java:186)
	at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
	at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:1207)
	at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:1194)
	at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2100)
	... 181 common frames omitted

Hi,

I’ve moved your post to a new thread because once you changed the context (H2 → SQL Server) it became a different topic.

Just FYI, we look at lots of threads each day. We can’t be counted on to notice cross-topic correspondences.

Regarding your current problem, does this seem relevant?

SONAR-17762 - Cannot configure keepaliveTime for HikariCP

And if so, can you double-confirm that you’re on 9.8? Because that was supposed to have been fixed in 9.8.

 
Ann

Ann, Yes I confirm it is a frsh new 9.8 setup. The item you indicated is not rtelevant as there is no firwall between the linux hosting sonarqube and the winserver hosting the DB, eveny local firewalls on both machines are disabled

1 Like

Hi,

Thanks for the confirmation. I’ve flagged this for more expert attention.

 
Ann

Hello @eliassal ,

Are you running on default configuration for a database connection? If not, do you mind sharing it?

Usually, this happens when the connection timeout on a database side (or firewall) is shorter than the one configured for the database connection pool, thus logs suggest decreasing maxLifetime value - by default, it is set to 30 min. Could you lower it to 20 min and see if that helped?

Hi Jacek, aas I indicated earlier there is no firewall between the linux hosting sonarqube and the winserver hosting the DB, eveny local firewalls on both machines are disabled.
Enclosed the somar.properties file
sonar.zip (7.6 KB)

I tried to find where I can update maxLifetime but there is no such key in the sonar.properties file enclosed here

Also, yesterday I increased the RAM for the VM by 1 Gig since that time, I did not have this issue, I am waiting to see as well

Okay. Thanks for the update.

In any case, you could try to set sonar.jdbc.maxLifetime property in this file to a lower value (20min) if the problem persists.

Also, I was referring to any configuration on a database that would kill idle connections in a pool after a certain amount of time.

Let me know if the problem remains.