• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

MySQL connection is dropped before wait_timout is elapsed

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic