• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL connection is dropped before wait_timout is elapsed

 
BhaveshKumar Solanki
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Our web application uses mysql-connector-java-5.0.3-bin mysql driver and we have implemented a pooling mechanism to maintains certain number of connection in pool. Our application is running on a machine X and Mysql database is running on different machine Y. We have set mysql wait_timout variable for 24 hours. Ideally the connection would be dropped automatically after sitting idle for 24 hours. To remove such dropped connections from the pool we just execute a lightweight query to database server to check whether the underlying database connection is live or dead. If the connection is dead it would throw an SQL exception and we remove such connection from the pool.

We are currently facing a problem that the mysql database is abnormally dropping a connection in couple of idle hours in spite of having wait_timout variable set for 24 hours. After the connection is dropped when our pooling mechanism execute a lightweight query to detect dropped connection, it is taking more than 15 minutes to execute and then throws following exception:


** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Connection timed out

STACKTRACE:

java.net.SocketException: Connection timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2248)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2196)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1163)
at itm.common.DBConnectionManager$DBConnectionPool.getConnection(DBConnectionManager.java:428)
at itm.common.DBConnectionManager.getConnection(DBConnectionManager.java:113)
at itm.common.ConnectionUsageEnforcer.init(ConnectionUsageEnforcer.java:63)
at itm.common.ConnectionUsageEnforcer.<init>(ConnectionUsageEnforcer.java:54)
at itm.login.LoginManager.<init>(LoginManager.java:81)
at itm.login.LoginServlet.authenticateUser(LoginServlet.java:212)
at itm.login.LoginServlet.service(LoginServlet.java:138)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at itm.setup.LoginFilter.doFilter(LoginFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **


java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Connection timed out

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Connection timed out

STACKTRACE:

java.net.SocketException: Connection timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2248)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2196)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1163)
at itm.common.DBConnectionManager$DBConnectionPool.getConnection(DBConnectionManager.java:428)
at itm.common.DBConnectionManager.getConnection(DBConnectionManager.java:113)
at itm.common.ConnectionUsageEnforcer.init(ConnectionUsageEnforcer.java:63)
at itm.common.ConnectionUsageEnforcer.<init>(ConnectionUsageEnforcer.java:54)
at itm.login.LoginManager.<init>(LoginManager.java:81)
at itm.login.LoginServlet.authenticateUser(LoginServlet.java:212)
at itm.login.LoginServlet.service(LoginServlet.java:138)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at itm.setup.LoginFilter.doFilter(LoginFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **


at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1713)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2248)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2196)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1163)
at itm.common.DBConnectionManager$DBConnectionPool.getConnection(DBConnectionManager.java:428)
at itm.common.DBConnectionManager.getConnection(DBConnectionManager.java:113)
at itm.common.ConnectionUsageEnforcer.init(ConnectionUsageEnforcer.java:63)
at itm.common.ConnectionUsageEnforcer.<init>(ConnectionUsageEnforcer.java:54)
at itm.login.LoginManager.<init>(LoginManager.java:81)
at itm.login.LoginServlet.authenticateUser(LoginServlet.java:212)
at itm.login.LoginServlet.service(LoginServlet.java:138)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at itm.setup.LoginFilter.doFilter(LoginFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)


After getting this exception, our application is able to create a new connection in fraction of seconds. We are not able to identify the reason why the connection is dropped within couple of idle time and why it is taking more than 15 minutes to execute the lightweight query with the dead connection. We have also tried the setQueryTimeout() with 5 seconds but the results are same.

Any help and suggestions are appreciated.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you saying that the connection is dropping out even though you close the connection immediately after use?
 
BhaveshKumar Solanki
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We do not close the connection after using it. We have implemented a connection pooling mechanism. Once the connectin is used, it is returned to the pool so that it can be reused.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That looks OK. In connection pooling, the close() method is overridden to perform the functions of the original method except that the connection is returned to the pool instead of being closed.

Are you using an open source connection pool or did you write your own.
 
BhaveshKumar Solanki
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We have written our own pooling mechanism.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why would you write your own connection pool? It is normally best to just use software which has been proven in the field, especially if it is free.

Try one of the open source connection pools instead.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic