It (stacktrace below) could be cause by the idle time of connection pool is longer than the connection idle time of mysql server.
So
1) set maxIdle time to 8 hours in context.xml
2) set autoReconnect to true in url. Something like, jdbc:mysql://(host):(port)/(db_name)?autoReconnect=true
I am not sure it will work. I follow up with the test result.
Ref: http://forums.mysql.com/read.php?39,42763,42763
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 45 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3134)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1818)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2466)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at uk.ac.ebi.biomodels.RDatabase.executeQuery(RDatabase.java:148)
... 32 more
Caused by: 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:3119)
... 39 more
Tuesday, July 8, 2008
Subscribe to:
Post Comments (Atom)
6 comments:
Tested this morning, the problem was NOT resolved. I am thinking if it is because I am using static instance of my database class.
Was busy on other new features raised during last meeting. So just moved back to this issue again.
One wrong conception needs to clarified here is that "maxIdle" means maximum idle connections allows, not maximum idle time.
Anyway, today, I checked code again to make sure all connection, statements and resultset were closed.
And then, I need to figure out how to set maximum idle time.
I need to completely undeploy the applcation to make context.xml updated.
All tweaks tried before changed nothing.
So with the hope, I tried this one http://developer.spikesource.com/blogs/vsrini/2007/09/tomcat_jdbc_connection_pool_co.html .
Basically, this configuration do validation before post query. I know, it is not graceful at all.
According to the solution in last comment, the resource definition became as following. So added testOnBorrow="true" and validationQuery="SELECT '1'" . It seems working. At least from catalina.out, the error gone.
But validate connection before every query reasonably harms performance. Best way is to set maximum idle time of db pool same as MySQL's.
<Resource name="jdbc/dbtest"
url="jdbc:mysql://localhost:4030/dbtest?autoReconnect=true"
username="xxxxxx"
password="xxxxxx"
auth="Container"
type="javax.sql.DataSource"
logAbandoned="true"
removeAbandoned="true"
removeAbandonedTimeout="60"
autoReconnect="true"
maxActive="30"
maxIdle="6"
maxWait="10000"
driverClassName="com.mysql.jdbc.Driver"
testOnBorrow="true"
validationQuery="SELECT '1'"
/>
I tried to remove validationQuery="SELECT '1'" . Similar error thrown.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was65057 seconds ago.The last packet sent successfully to the server was 65057 seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
Post a Comment