Re: "max timeout" problem - connection is not available the first time

[Mark Matthews]

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

Arjan Gerritsen wrote:
> Dear list,
> 
> This is the situation. I'm developping a webapplication using Mysql as 
> database and "MySQL Connector/J".
> 
> It seems to work fine. But it goes wrong when the application isn't used for 
> the time of the "max timeout" of Mysql (default: 8 hours). I tried 
> everything, I used the autoReconnect and autoReconnectForPools. But it 
> doesn't seem to work. I used both new versions of the J connector (the 
> production-ready and develop version). But still the same effect, after 8 
> hours of inactivity the connection is lost.
> 
> I'm out of ideas, so i hope someone can give me a hint or give me a solution 
> to this problem. Below a StackTrace of the Exception.
> 
> ===========
> StackTrace:
> 2004-12-20 16:01:42,781 [EnergyAlert] ERROR [http8080-Processor2] 
> (JDBCExceptionReporter.java:46) - Communication link failure: 
> java.net.SocketException
> 2004-12-20 16:01:42,796 [EnergyAlert] ERROR [http8080-Processor2] 
> (JDBCException.java:37) - Cannot open connection
> java.sql.SQLException: Communication link failure: java.net.SocketException
[really-long-stacktrace-snipped]

Arjan,

Have you read the Connector/J Manual? It tells you what you should do in
this case in the troubleshooting section. If you're using autoReconnect
and transactions, you're always going to get at least _one_ exception
when you first try to use a stale connection...there's really no way
around that.

The _real_ solution is to either increase 'wait_timeout' on the MySQL
server, shorten the connection pool connection 'lifetime' to something
less than 'wait_timeout', tell your connection pool to test idle
connections for staleness or do all three (which is what I recommend).

Since it looks like you're using DBCP, the configuration variables
you'll want to fiddle with are:

validationQuery (set to 'SELECT 1')
testWhileIdle (set to 'true')
timeBetweenEvictionRunsMillis (some positive integer)
minEvictableIdleTimeMillis (set to something smaller than 'wait_timeout').

and if you don't mind a hit for every getConnection():

testOnBorrow (set to 'true')

Unfortunately, in my opinion, DBCP turns out not to have all the knobs
you need to tune to have a robust connection pool (such as
max-idle-lifetime for a connection), and isn't as robust as other
solutions I've used...If you can, you might take a look at C3P0
(http://c3p0.sf.net), which integrates well with Hibernate...If you use
C3P0, you can adjust the following variables:

preferredTestQuery (set to 'SELECT 1')

idleConnectionTestPeriod (set to something much less than wait_timeout,
prevents connections from going stale)

maxIdleTime (set to something slightly less than wait_timeout,
preventing 'stale' connections from being handed out)

(if you can take the performance 'hit'):

testConnectionOnCheckout (set to 'true')


There is no _safe_ way for _any_ JDBC driver to automagically recover
connections that have had their network connectivity affected, whether
that's through the server doing it, or someone unplugging your switch,
restarting the server, etc. That's why _you_ need to code defensively
(i.e. actually _handle_ SQLExceptions, rather than just let them
propagate all the way up the stack), and use the features of your
connection pool to make sure that the connection is as 'live' as it can
be when it's handed out.

Regards,

	-Mark

- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: [phone]
www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBxwGhtvXNTca6JD8RAtLZAJ49pme4Bm3pEuvM7nHeaQXeeVaaGQCfXPmN
mHHuJYxI1qiVu1GSrhRG0tI=
=bw60
-----END PGP SIGNATURE-----