Regarding the one that's working, it's probably working because it isn't
letting the connection sit idle for such a long time.
There are a few things you might want to consider:
1. Just increase the connection timeout for your mysql service. The
default is 8 hours. I have one third party app that keeps a connection
for the life of an app (it doesn't use pooling at all) and have pushed
up the timeout to 25hrs on that so connections wouldn't die during the
normal Monday-Friday work week.
2. Add the autoReconnect=true to the end of your jdbc url (note: this is
_not_ recommended by MySQL) and in the catch clause, re-establish the
connection and try again.
3. Put in the validationQuery in your Resource def. I know you said you
didn't want the extra overhead of a validaton query on every access, but
it only takes an extremely small slice of time. Plus it only occurs
when you get a connection. If you keep a connection for the life of
your request, it only happens once per request. Please do close the
connection and return it at the end of the request though.
Personally I'd just take the extremely tiny performance penalty of a
validaton query as the simplest solution.
--David
Thomas Haines wrote:
Hello
I am experiencing a reproducible problem with connection pooling in
Tomcat, although I suspect it is not a Tomcat issue per se.
I am running:
mysql server 5.0.58
tomcat 6.0.16
mysql connector 5.1.6
java version 1.5.0_13
all on CentOS 5
The following error occurs when there has been no activity between the
DB and Tomcat for some period of time:
ERROR (21-06-08 07:59) [servlets.ViewEmail]
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last
packet successfully received from the server was 46859 seconds ago.The
last packet sent successfully to the server was 46859 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.
at sun.reflect.GeneratedConstructorAccessor17.newInstance(Unknown
Source)
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:3246)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at
com.ghf.util.MailoutUtils.getRecipientbyChecksum(MailoutUtils.java:123)
INFO (21-06-08 08:01) [servlets.ViewEmail] : View email request
successfully processed
INFO (21-06-08 08:02) [servlets.ViewEmail] : View email request
successfully processed
At this juncture, I am unsure how to remedy this. My only two
thoughts are to either:
a) downgrade to MySQL Connector/J 5.0.8 and see if this fixes it;
b) add a while (!verified && attempts<2) type loop in getConnection()
method to query the DB using a minimal query and then catch the first
dead connection.
I'm not overly keen on introducing the additional overhead of querying
the DB just to check if it is valid every time a request is made for a
connection. Does anyone have any thoughts on how I might debug/solve
this issue? I've searched widely on Google to no avail.
Interestingly, there is another instance of tomcat communicating with
MySQL on the machine, exactly the same configuration, that doesn't
suffer these woes. Any help would be appreciated!
Tom
Other relevant snippets:
META-INF/context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/dyn">
<Resource name="jdbc/ghip" auth="Container"
type="javax.sql.DataSource"
maxActive="50"
maxIdle="5"
maxWait="10000"
username="******"
password="******"
driverClassName="com.mysql.jdbc.Driver"
removeAbandoned="true"
removeAbandonedTimeout="180"
url="jdbc:mysql://localhost:3306/ghip?autoReconnect=true"
/>
</Context>
DBPool.java exert:
/**
* Method getConnection.
* @return Connection New connection from the pool
*/
public static Connection getConnection() {
Connection conn = null;
try {
Context ctx = (Context) new
InitialContext().lookup("java:comp/env");
conn = ((DataSource) ctx.lookup("jdbc/ghip")).getConnection();
}
catch (Exception e) {
e.printStackTrace(System.err);
}
return conn;
}
---------------------------------------------------------------------
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]
