Michael Lai wrote:
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.
Michael,
There really is no way to keep a connection alive indefitely, and I'm
not aware of compelling reasons to do so for many reasons. First, it
consumes resources that aren't needed if the connection is sitting idle,
and second, it increases the risk that network issues, or server
restarts will cause your application to crash.
It takes very little time (on the order of a few milliseconds) to create
a JDBC connection to MySQL, so you should set your connection pool to
only let connections stay idle for a few minutes.
Most of the high-volume applications I've seen that use Java with MySQL
don't let connections stay idle more than 10 minutes or so.
-Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
