connection pools time-outs in GlassFish

I've been using JDBC connection pools and JMS connection factories for a recent project and upon deployment on GlassFish I did a sanity test to confirm that everything is OK. It passed the test.

So off I go, leaving it running on an Amazon EC2 instance. When I check on it about 3 days later I notice that it doesn't work anymore ... "server error", the UI informs me (which is a bad error message for the end users, whom might not know what a server (those working in restaurants) has to do with it). Anyway, by looking at the logs I see a few of these:

Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

and

Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

Now the way I implemented this was in the "init" method of the servlet I would create my instances of javax.jms.Connection and java.sql.Connection as member fields of the servlet, hoping to reuse them for the lifetime of the servlet.

The errors in the logs seem to indicate that those connections get yanked out while my servlet is running, albeit idle. Not cool.

Looking at the admin console I see an "Idle Timeout:" configuration setting which defaults to 300 sec for both my JDBC connection pool and JMS connection factory. I changed that to 300000000. I have the feeling that this is going to fix the issue, but I need to run it for a while to confirm.

I'm not sure if this is the right thing of approaching this, so if you know any better please add a comment.

UPDATE:
It seems that changing the configuration settings for GlassFish is not enough to fix the problem.

Likely the wait_timeout configuration setting of MySQL has something to do with this too. By default mysqld will close idle connections after 8 hours, so we'd need to increase that to up to the max value of 31536000. One way to do that is through a configuration file (e.g. /etc/my.cnf, where we'd add a line with 'wait_timeout= 31536000' under the [mysqld] section.

Yet a better approach would be to catch the exceptions mentioned above in the servlet code and recreate the connection to the DB.

ANOTHER UPDATE:
It seems that catching the exception and re-opening the connection in the servlet code doesn't work very well. I get the following exception when trying to re-establish the connection:

Exception:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 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)
...

Other people have experienced this too. One workaround indicated there, like I mentioned above, is to tinker with wait_timeout configuration of MySQL.

Another avenue to explore would be the "Connection Validation" capabilities of the JDBC connection pool. That's easily available through GlassFish admin console. According to the docs "Optionally, the application server can validate connections before they are passed to applications. This validation allows the application server to automatically reestablish database connections if the database becomes unavailable due to network failure or database server crash. Validation of connections incurs additional overhead and slightly reduces performance."

It'd be interested to see if the "table" validation method fixes this issue.