Frequent Connection reset in Spring Jdbc Template

Question

I am using JDBC Template to connect to oracle database. Frequently I am getting exception which will disappear after few seconds. When I retry I will be able to execute sql query.

2016-09-17 12:34:30.404 [ajp-nio-8009-exec-4] ERROR UserController.getcontactInfo - Error while getting contact info
org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL []; Io exception: Connection reset; nested exception is java.sql.SQLRecoverableException: Io exception: Connection reset
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:251) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at c.s.c.dao.impl.DaoImpl.getContactInfo(DaoImpl.java:109) ~[classes/:na]
    at c.s.c.service.impl.UserServiceImpl.getContactInfo(UserServiceImpl.java:159) ~[classes/:na]
    at c.s.c.controller.user.UserController.getcontactInfo(UserController.java:95) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_102]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_102]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_102]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_102]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) [spring-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) [spring-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622) [servlet-api.jar:na]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) [spring-webmvc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) [servlet-api.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292) [catalina.jar:8.0.36]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.36]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-websocket.jar:8.0.36]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) [catalina.jar:8.0.36]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.36]
    at c.s.c.login.AuthenticationFilter.doFilter(AuthenticationFilter.java:84) [classes/:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) [catalina.jar:8.0.36]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.36]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) [catalina.jar:8.0.36]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) [catalina.jar:8.0.36]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) [catalina.jar:8.0.36]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) [catalina.jar:8.0.36]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [catalina.jar:8.0.36]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) [catalina.jar:8.0.36]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528) [catalina.jar:8.0.36]
    at org.apache.coyote.ajp.AbstractAjpProcessor.process(AbstractAjpProcessor.java:873) [tomcat-coyote.jar:8.0.36]
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670) [tomcat-coyote.jar:8.0.36]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520) [tomcat-coyote.jar:8.0.36]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476) [tomcat-coyote.jar:8.0.36]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_102]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_102]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.0.36]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_102]
Caused by: java.sql.SQLRecoverableException: Io exception: Connection reset
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:458) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.6.0-Production+"]
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) ~[commons-dbcp-1.4.jar:1.4]
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) ~[commons-dbcp-1.4.jar:1.4]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    ... 46 common frames omitted

My connection pool settings

datasource.maxActive=10
datasource.maxIdle=10
datasource.initialSize=5
datasource.maxWait=5000

I suspected it be oracle driver issue listed in

Oracle JDBC intermittent Connection Issue

https://community.oracle.com/message/3795101

http://www.usn-it.de/index.php/2009/02/20/oracle-11g-jdbc-driver-hangs-blocked-by-devrandom-entropy-pool-empty/

I set following property in jvm.

-Djava.security.egd=file:///dev/urandom

But still it didnt help.

Anyone is facing/faced similar problem?

Make sure you configure a validation query. The database server will close idle connections after a certain amount of time (configurable on server), so any connection sitting unused in the dbcp pool will go stale. Adding a validation query to the pool configuration will ensure that the connection is checked and reestablished before given to your code. — Andreas, Sep 18 '16 at 06:45
Could you describe in a bit more detail what do you mean by "frequently"? — Gergely Bacso, Sep 18 '16 at 07:46
It happens very randomnly. I would say 10 times in a day and my application is not so heavy. 10-20 requests per hour.Thats it — Javadroider, Sep 18 '16 at 15:15

score 1 · Accepted Answer · answered Sep 18 '16 at 06:46

1

Found the bug reference so I'll post my comment as an answer.

See Java bug 6202721

Seems even if you explicitly request /dev/urandom Java silent replaces it with /dev/random, which can't keep up with entropy generation on Linux.

Try specifying /dev/./urandom instead. The substitution happens based on the explicit string value and if you want to really force urandom you have to provide an alternate form that doesn't match the string but still resolves to the same path.

answered Sep 18 '16 at 06:46

Jim Garrison

85,615
20
155
190

But looks like this it fixed in Java 6. I am using Java 8. Correct me if I am wrong. – Javadroider Sep 18 '16 at 07:08
1

The bug was closed as "not an issue" and nothing was done. – Jim Garrison Sep 18 '16 at 16:19

Frequent Connection reset in Spring Jdbc Template

1 Answers1