List:General Discussion« Previous MessageNext Message »
From:Michael Lai Date:February 9 2006 1:36pm
Subject:Re: Connection problem after long idle periods
View as plain text  
David Logan wrote:

> Michael Lai wrote:
>
>> I am currently using MySQL 5.0.15 with mysql-connector-java-3.1.11.  
>> I can access the database from my JSPs with no problem except for one 
>> small issue.  After a long delay (usually overnight), when someone 
>> first tries to access the database, I would get the following error:
>>
>> Communications link failure due to underlying exception: ** BEGIN 
>> NESTED EXCEPTION ** java.net.SocketException MESSAGE: Broken pipe 
>> STACKTRACE: java.net.SocketException: Broken pipe at 
>> java.net.SocketOutputStream.socketWrite0(Native Method) at 
>> java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) 
>> at java.net.SocketOutputStream.write(SocketOutputStream.java:136) at 
>> java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) 
>> at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) 
>> at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2690) at 
>> com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619) at 
>> com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552) at 
>> com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at 
>> com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at 
>> com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at 
>> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933) 
>> at 
>> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027) 
>> at 
>>
> org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
> 
>> at 
>> org.apache.jsp.processLogin_jsp._jspService(org.apache.jsp.processLogin_jsp:81) 
>> at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) 
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at 
>> org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322) 
>> at 
>> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291) 
>> at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) 
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at 
>>
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
> 
>> at 
>>
> org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
> 
>> at 
>>
> org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) 
>> at 
>>
> org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) 
>> at 
>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) 
>> at 
>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) 
>> at 
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
> 
>> at 
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) 
>> at 
>> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) 
>> at 
>>
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
> 
>> at 
>>
> org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) 
>> at 
>>
> org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
> 
>> at 
>>
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) 
>> at java.lang.Thread.run(Thread.java:595) ** END NESTED EXCEPTION ** 
>> Last packet sent to the server was 0 ms ago.
>>
>> But afterwards, on the second and subsequent attempts, the database 
>> runs fine.  It is only the first try after a long idle period that I 
>> get this error message.  I am not sure how to fix this.  I am 
>> guessing that the database connection is down after a long idle 
>> period and have to recreate a connection first after the first try.
>>
> Hi Michael,
>
> Try looking at
> http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html more
> specifically the connect-timeout variable. You should use SHOW VARIABLES
> LIKE "%connect%"; and check the value.
>
> It maybe this and you can stretch it out further if you need to.
>
> Regards
>

I tried lengthening the connection timeout but it doesn't seem to be 
working.  Here is my ROOT.xml configuration:

<Context path="" docBase="/home/tomcat/applications/" debug="0" 
reloadable="true" >
    <Resource name="jdbc/****" type="javax.sql.DataSource"
              auth="Container" driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost/<hostname>?autoReconnect=true"
              username="****" password="****"
              maxActive="8" maxIdle="-1" maxWait="10000" />
</Context>

Is there any way to keep a connection alive indefinitely?  I thought the 
autoReconnect would do the trick.
Thread
Connection problem after long idle periodsMichael Lai1 Feb
  • Re: Connection problem after long idle periodsDavid Logan1 Feb
    • Re: Connection problem after long idle periodsMichael Lai9 Feb
      • Re: Connection problem after long idle periodsMark Matthews10 Feb