Re: Datasource JNDI and DataSourceRealm 8 hour timeout - Mark Matthews - com.mysql.lists.java

2 messages in com.mysql.lists.javaRe: Datasource JNDI and DataSourceRea...

From	Sent On	Attachments
Martijn	12 Jul 2005 07:07
Mark Matthews	12 Jul 2005 07:38

Subject:	Re: Datasource JNDI and DataSourceRealm 8 hour timeout
From:	Mark Matthews (ma...@mysql.com)
Date:	07/12/2005 07:38:19 AM
List:	com.mysql.lists.java

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Martijn wrote:

Hello list,

This is something which keep on hunting me for some time now and I'm getting somewhat frustrated here. Yes, this is related to the 8hour timeout, and yes this has haunted this list before in some other form. I'm using the standard Connector/J definition from http://dev.mysql.com/doc/connector/j/en/cj-tomcat-config.html.

So with this definition the regular I/O Communication Exceptions after 8 hours of being idle don't occur anymore. I can access my web application for days on end. Great!

But the Exception resurfaces when I try to login again through the DataSourceRealm definition (which uses the jdbc/MySQLDB). I get the Communication Exception once more and it seems that this single login kills my pool completely since all connections from that point on are 'stale'. I have to reload the context to revive the system.

I have fiddled with the settings (timeout related). I also have set the following parameters, but to no avail:

<parameter> <name>removeAbandoned</name> <value>true</value> </parameter>  <parameter> <name>removeAbandonedTimeout</name> <value>60</value> </parameter> <parameter> <name>logAbandoned</name> <value>true</value> </parameter>

Again, all works out, but the DataSourceRealm blows it al.

FYI. I'm using Tomcat 5.0.28 and Connector/J 3.1.10 at the moment. Having the same problems with the 3.0.x series.

Has anyone experienced this in the past, or can anyone provide me with some help here? C3P0?

regards, Martijn Smit

Martijn,

The trick is to configure your connection pool to not hold connections idle that long (which is really pointless, given that it takes a couple of milliseconds to setup a new MySQL connection), or configure MySQL's wait_timeout to be longer than 8 hours (like 2^32 seconds?).

However, if the JDBC realm that tomcat ships can't handle a SQLException due to a stale connection every once and awhile, it's broken, because 1) The JDBC spec doesn't specify that connections last forever, or survive network issues and 2) No vendor can provide "automagical" reconnection that works for all cases, so most don't, as there is already a mechanism for handling connection failures, namely SQLExceptions.

I've seen that at least when using the non-DBCP backed realm, Tomcat's code tries the queries 3 or so times before giving up...Maybe when backed by a pool it doesn't?

C3p0 is better about giving configuration options that actually _help_ (i.e. checking idle connections, having a max idle timeout, etc), so you may want to give it a try.

-Mark

iD8DBQFC09XxtvXNTca6JD8RAqYCAJ9Cz7xKaqEHZTsaE7ys0YpLPzbFVQCePrUP W/uPowUF1eadYaxdTu/lWbw= =F4Zh -----END PGP SIGNATURE-----