CODEWITHSUNDEEP
sqlsql-server-2008grailsdatasource
Ana Franco
Ana Franco

Reputation: 1821

com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error in grails app with sql server 2008

I'm having trouble with a database I connect, I have an app in grails that connects to this DB and each time the database is backed up it stops my app to reconnect again, I get this exception:

com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1805)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:3581)
at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:3482)
at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3062)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6120)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:402)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2542)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
at org.codehaus.groovy.grails.orm.hibernate.metaclass.ListPersistentMethod$1.doInHibernate(ListPersistentMethod.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:407)
at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:344)
at org.codehaus.groovy.grails.orm.hibernate.metaclass.ListPersistentMethod.doInvokeInternal(ListPersistentMethod.java:59)
at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke(AbstractStaticPersistentMethod.java:79)
at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke(AbstractStaticPersistentMethod.java:72)
at org.codehaus.groovy.grails.orm.hibernate.HibernateGormStaticApi.list(HibernateGormStaticApi.groovy:236)
at org.grails.datastore.gorm.GormStaticApi.findAll(GormStaticApi.groovy:429)
at com.formacol.solicitud.cotizacion.Cliente.findAll(Cliente.groovy)
at com.formacol.solicitud.cotizacion.Cliente$findAll.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at com.formacol.solicitud.cotizacion.Cliente$findAll.call(Unknown Source)
at com.formacol.solicitud.cotizacion.CotizacionController.$tt__cotizaciones(CotizacionController.groovy:292)
at com.formacol.solicitud.cotizacion.CotizacionController$_cotizaciones_closure11.doCall(CotizacionController.groovy)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)
at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086)
at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910)
at groovy.lang.Closure.call(Closure.java:411)
at groovy.lang.Closure.call(Closure.java:427)
at org.codehaus.groovy.grails.orm.support.GrailsTransactionTemplate$1.doInTransaction(GrailsTransactionTemplate.groovy:62)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
at org.codehaus.groovy.grails.orm.support.GrailsTransactionTemplate.execute(GrailsTransactionTemplate.groovy:59)
at com.formacol.solicitud.cotizacion.CotizacionController.cotizaciones(CotizacionController.groovy)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.codehaus.groovy.grails.web.servlet.mvc.MixedGrailsControllerHelper.invoke(MixedGrailsControllerHelper.java:154)
at org.codehaus.groovy.grails.web.servlet.mvc.AbstractGrailsControllerHelper.handleAction(AbstractGrailsControllerHelper.java:354)
at org.codehaus.groovy.grails.web.servlet.mvc.AbstractGrailsControllerHelper.executeAction(AbstractGrailsControllerHelper.java:231)
at org.codehaus.groovy.grails.web.servlet.mvc.AbstractGrailsControllerHelper.handleURI(AbstractGrailsControllerHelper.java:197)
at org.codehaus.groovy.grails.web.servlet.mvc.AbstractGrailsControllerHelper.handleURI(AbstractGrailsControllerHelper.java:121)
at org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsController.handleRequest(SimpleGrailsController.java:72)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.codehaus.groovy.grails.web.servlet.GrailsDispatcherServlet.doDispatch(GrailsDispatcherServlet.java:355)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:200)
at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:486)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:411)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:338)
at org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:332)
at org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:297)
at org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:288)
at org.codehaus.groovy.grails.web.mapping.filter.UrlMappingsFilter.doFilterInternal(UrlMappingsFilter.java:217)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.codehaus.groovy.grails.web.sitemesh.GrailsPageFilter.executeFilterChainWithWrappedResponse(GrailsPageFilter.java:233)
at org.codehaus.groovy.grails.web.sitemesh.GrailsPageFilter.obtainContent(GrailsPageFilter.java:208)
at org.codehaus.groovy.grails.web.sitemesh.GrailsPageFilter.doFilter(GrailsPageFilter.java:153)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter.doFilter(RequestHolderAuthenticationFilter.java:49)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:82)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.codehaus.groovy.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:69)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.codehaus.groovy.grails.web.filters.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:67)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

The database configuration I'm using for this database is as follows, and I haven't seen anything wrong in it.

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    username = "XXXXXXXXXX"
    password = "XXXXXXXXXX"
}

dataSource_siesa {
  pooled = true
  jmxExport = true
  driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
  username = "XXXXXXXX"
  password = "XXXXXXXXX"
  readOnly = true
  //logSql = true
}

environments {
    development {
        dataSource {
            dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
        url = "jdbc:sqlserver://localhost:1433;databaseName=myappDllo;"
    }
    dataSource_siesa {
        url = "jdbc:sqlserver://XXX.XX.X.X:1433;databaseName=extappDllo;"
    }
}
test {
    dataSource {
        dbCreate = "update"
        url = "jdbc:sqlserver://localhost:1433;databaseName=myappTest;"
    }
    dataSource_siesa {
        url = "jdbc:sqlserver://XXX.XX.X.X:1433;databaseName=extappTest;"
    }
}
production {
    dataSource {
        dbCreate = "update"
        url = "jdbc:sqlserver://localhost:1433;databaseName=myappPdn;"
        properties {
           // See http://grails.org/doc/latest/guide/conf.html#dataSource for documentation
           jmxEnabled = true
           initialSize = 5
           maxActive = 50
           minIdle = 5
           maxIdle = 25
           maxWait = 10000
           maxAge = 10 * 60000
           timeBetweenEvictionRunsMillis = 5000
           minEvictableIdleTimeMillis = 60000
           validationQuery = "SELECT 1"
           validationQueryTimeout = 3
           validationInterval = 15000
           testOnBorrow = true
           testWhileIdle = true
           testOnReturn = false
           jdbcInterceptors = "ConnectionState"
           defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
        }
    }
    dataSource_siesa {
        url = "jdbc:sqlserver://XXX.XX.X.X:1433;databaseName=extappPdn;"
    }
}

}

What properties should I set so I can prevent this?

Thanks

Upvotes: 6

Views: 32812

Answers (2)

Narizz28
Narizz28

Reputation: 1

I know this is an old thread, but in case anyone stumbles across this from searching, here's more info than what's offered above.

the MS SQL JDBC drive version 9.4 and above has these 2 properties available with [default values] that may help this issue:

connectRetryCount [1]

connectRetryInterval [10]

As you can see, by default it only tries once. When I've run across this error using that driver, my logs show the "Connection reset.." error once, then success by setting the connectRetryCount to 2 or more (5 is what I usually use.)

For the available properties: https://learn.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver15

For the JDBC driver's SQL Version Compatibility Matrix: https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15

Upvotes: 0

Joshua Moore
Joshua Moore

Reputation: 24776

The issue is that your second connection doesn't have all the same properties as your first in regards to retrying and timeouts. Set your second datasource to also have these properties.

production {
    dataSource {
        dbCreate = "update"
        url = "jdbc:sqlserver://localhost:1433;databaseName=myappPdn;"
        properties {
           jmxEnabled = true
           initialSize = 5
           maxActive = 50
           minIdle = 5
           maxIdle = 25
           maxWait = 10000
           maxAge = 10 * 60000
           timeBetweenEvictionRunsMillis = 5000
           minEvictableIdleTimeMillis = 60000
           validationQuery = "SELECT 1"
           validationQueryTimeout = 3
           validationInterval = 15000
           testOnBorrow = true
           testWhileIdle = true
           testOnReturn = false
           jdbcInterceptors = "ConnectionState"
           defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
        }
    }
    dataSource_siesa {
        url = "jdbc:sqlserver://XXX.XX.X.X:1433;databaseName=extappPdn;"
        properties {
           jmxEnabled = true
           initialSize = 5
           maxActive = 50
           minIdle = 5
           maxIdle = 25
           maxWait = 10000
           maxAge = 10 * 60000
           timeBetweenEvictionRunsMillis = 5000
           minEvictableIdleTimeMillis = 60000
           validationQuery = "SELECT 1"
           validationQueryTimeout = 3
           validationInterval = 15000
           testOnBorrow = true
           testWhileIdle = true
           testOnReturn = false
           jdbcInterceptors = "ConnectionState"
           defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
        }
    }
}

Upvotes: 4

Related Questions