Re: Connections timing out even with validation query??

Tue, 15 Jul 2008 06:02:31 -0700 

David...see below...

On Jul 15, 2008, at 8:28 AM, David Smith wrote:
Looks like for some reason your mysql db is closing the connection in the middle of your query reads. This isn't normal behavior in a tomcat/mysql setup. Setting connect_timeout only impacts the initial handshake and validationQuery just tests the connection before you borrow it. I wouldn't expect either to make a difference in this problem.
If you use the command line client, can you hold a connection open long enough to query the database? 
Yes:
mysql> select * from user;
+---------+----------------------+---------------------+----------+
| userNum | lastName             | firstName           | acctType |
+---------+----------------------+---------------------+----------+
|       1 | User                 | Anonymous           |        0 |
|       2 | User                 | Test                |        0 |
....etc....
Also have you made any non-standard changes in the config of your mysql server?
The only change I've made at all is to change the timeout (several days ago) to 12 hrs. BUT, thls is a mysql provided by the ISP as a package under the CentOS/ Virtuozzo combination. The entries in both /etc/my.cnf and /root/.my.cnf are completely vanilla: 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
connect_timeout=43200

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


and


[client]
 password=xxxxxxxxxxxxxxx
The system works fine except when I let it go over 12 hrs (night before last), and then I got the same exception. 



--David

Ken Bowen wrote:

Hi All,
I get the feeling this was lost over the weekend when I sent it last Saturday. 
Does anyone have any thoughts on this?
Using: Tomcat 5.5.26 ; Java 1.5 ; MySQL 5.0.51 running on CentOs 5.0 hosted in a Virtuozzo container.
The problem: With MySQL connect_timeout = 5, I'm getting timeout exceptions (below) even though I have a 
validation query set; Here's my app's META-INF/context-xml:

<Context path="" debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/sb_data" auth="Container" type="javax.sql.DataSource" 
           maxActive="100" maxIdle="30" maxWait="10000"
           username="xxxxxx" password="xxxxxx"
           driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/sb_data"
           validationQuery="select 1"/>
<Resource name="jdbc/sb_users" auth="Container" type="javax.sql.DataSource" 
           maxActive="100" maxIdle="30" maxWait="10000"
           username="xxxxxx" password="xxxxxx"
           driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/sb_users"
           validationQuery="select 1"/>
</Context>
My understanding is that if a closed connection is detected, it should automatically be replaced by a new connection??
The exception trace is below. Any guidance would be greatly appreciated. 
Thanks in advance,
Ken Bowen

-------------------------
javax.servlet.ServletException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error: 


** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:
java.io.EOFException
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2949)
      at com.mysql.jdbc.Statement.executeQuery(Statement.java:959)
at com.mysql.jdbc.DatabaseMetaData.getUserName(DatabaseMetaData.java: 5986) at org .apache .tomcat .dbcp.dbcp.DelegatingConnection.toString(DelegatingConnection.java: 104) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource $PoolGuardConnectionWrapper.toString(PoolingDataSourc 
e.java:344)
      at java.lang.String.valueOf(String.java:2615)
      at java.lang.StringBuilder.append(StringBuilder.java:116)
at com .strongbrain.database.DAOBaseUsers.getConnection(DAOBaseUsers.java: 38) at com .strongbrain.database.UserManager.getUserFromLogin(UserManager.java: 276) at com .strongbrain.auth.LoggedInUser.loggedInFromCookie(LoggedInUser.java: 59) at com.strongbrain.actions.BaseAction.execute(BaseAction.java: 105) at org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419) at org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224) at org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) 
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
      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 org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 738) 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.ApplicationDispatcher.invoke(ApplicationDispatcher.java:679) at org .apache .catalina .core .ApplicationDispatcher.processRequest(ApplicationDispatcher.java:461) at org .apache .catalina .core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:399) at org .apache .catalina .core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301) at org .tuckey .web.filters.urlrewrite.RewrittenUrl.doRewrite(RewrittenUrl.java:176) at org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 728) at org .apache .catalina .core .ApplicationFilterChain .internalDoFilter(ApplicationFilterChain.java:215) at org .apache .catalina .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 188) at com .strongbrain .DisableUrlSessionFilter.doFilter(DisableUrlSessionFilter.java:42) 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.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: 174) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:200) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:773) at org .apache .jk.common.ChannelSocket.processConnection(ChannelSocket.java:703) at org.apache.jk.common.ChannelSocket $SocketConnection.runIt(ChannelSocket.java:895) at org.apache.tomcat.util.threads.ThreadPool $ControlRunnable.run(ThreadPool.java:689) 
      at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.

STACKTRACE:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2949)
      at com.mysql.jdbc.Statement.executeQuery(Statement.java:959)
at com.mysql.jdbc.DatabaseMetaData.getUserName(DatabaseMetaData.java: 5986) at org .apache .tomcat .dbcp.dbcp.DelegatingConnection.toString(DelegatingConnection.java: 104) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource $PoolGuardConnectionWrapper.toString(PoolingDataSource.java:344) 
      at java.lang.String.valueOf(String.java:2615)
      at java.lang.StringBuilder.append(StringBuilder.java:116)
at com .strongbrain.database.DAOBaseUsers.getConnection(DAOBaseUsers.java: 38) at com .strongbrain.database.UserManager.getUserFromLogin(UserManager.java: 276) at com .strongbrain.auth.LoggedInUser.loggedInFromCookie(LoggedInUser.java: 59) at com.strongbrain.actions.BaseAction.execute(BaseAction.java: 105) at org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419) at org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224) at org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) 
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
      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 org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 738) 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.ApplicationDispatcher.invoke(ApplicationDispatcher.java:679) at org .apache .catalina .core .ApplicationDispatcher.processRequest(ApplicationDispatcher.java:461) at org .apache .catalina .core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:399) at org .apache .catalina .core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301) at org .tuckey .web.filters.urlrewrite.RewrittenUrl.doRewrite(RewrittenUrl.java:176) at org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 728) at org .apache .catalina .core .ApplicationFilterChain .internalDoFilter(ApplicationFilterChain.java:215) at org .apache .catalina .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 188) at com .strongbrain .DisableUrlSessionFilter.doFilter(DisableUrlSessionFilter.java:42) 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.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: 174) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:200) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:773) at org .apache .jk.common.ChannelSocket.processConnection(ChannelSocket.java:703) at org.apache.jk.common.ChannelSocket $SocketConnection.runIt(ChannelSocket.java:895) at org.apache.tomcat.util.threads.ThreadPool $ControlRunnable.run(ThreadPool.java:689) 
      at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2592)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2949)
      at com.mysql.jdbc.Statement.executeQuery(Statement.java:959)
at com.mysql.jdbc.DatabaseMetaData.getUserName(DatabaseMetaData.java: 5986) at org .apache .tomcat .dbcp.dbcp.DelegatingConnection.toString(DelegatingConnection.java: 104) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource $PoolGuardConnectionWrapper.toString(PoolingDataSource.java:344) 
      at java.lang.String.valueOf(String.java:2615)
      at java.lang.StringBuilder.append(StringBuilder.java:116)
at com .strongbrain.database.DAOBaseUsers.getConnection(DAOBaseUsers.java: 38) at com .strongbrain.database.UserManager.getUserFromLogin(UserManager.java: 276) at com .strongbrain.auth.LoggedInUser.loggedInFromCookie(LoggedInUser.java: 59) at com.strongbrain.actions.BaseAction.execute(BaseAction.java: 105) at org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419) at org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224) at org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) 
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
      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 org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 738) 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.ApplicationDispatcher.invoke(ApplicationDispatcher.java:679) at org .apache .catalina .core .ApplicationDispatcher.processRequest(ApplicationDispatcher.java:461) at org .apache .catalina .core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:399) at org .apache .catalina .core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301) at org .tuckey .web.filters.urlrewrite.RewrittenUrl.doRewrite(RewrittenUrl.java:176) at org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 728) at org .apache .catalina .core .ApplicationFilterChain .internalDoFilter(ApplicationFilterChain.java:215) at org .apache .catalina .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 188) at com .strongbrain .DisableUrlSessionFilter.doFilter(DisableUrlSessionFilter.java: 42) 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.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: 174) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:200) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:773) at org .apache .jk.common.ChannelSocket.processConnection(ChannelSocket.java:703) at org.apache.jk.common.ChannelSocket $SocketConnection.runIt(ChannelSocket.java:895) at org.apache.tomcat.util.threads.ThreadPool $ControlRunnable.run(ThreadPool.java:689) 
      at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **
org .apache .struts .action.RequestProcessor.processException(RequestProcessor.java:523) org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 421) org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224) org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) 
      javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 738) org .tuckey .web.filters.urlrewrite.RewrittenUrl.doRewrite(RewrittenUrl.java:176) org .tuckey .web .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java: 728) com .strongbrain .DisableUrlSessionFilter.doFilter(DisableUrlSessionFilter.java:42) 







--
David Smith
Programmer/Analyst
College of Agriculture and Life Sciences
Cornell University
B32 Morrison Hall
Ithaca, NY 14853
Phone: (607) 255-4521


---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to