How to avoid MySQL connection timeout errors with EclipseLink?

Question

MySQL closes a connection after a certain time if nothing happens (8 hours by default). The time can be influenced by the wait_timeout variable in the configuration.

I have an Eclipse RCP application where I use EclipseLink as persistence framework and I get an error when the client exceeds the timeout:

    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: 
       No operations allowed after connection closed.
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   ...
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
   at com.mysql.jdbc.Util.getInstance(Util.java:386)
       ...
   com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException
   ...
       org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor...

I tried to set autoReconnect/autoReconnectForPools=true but this does not help.

Thanks

EDIT

In my persistence.xml I have the following properties set:

    <property 
      name="eclipselink.jdbc.read-connections.max"
      value="10" />
    <property 
      name="eclipselink.jdbc.cache-statements" 
      value="true" />
    <property 
      name="eclipselink.jdbc.read-connections.shared"
      value="true" />

The rest of the configuration is done in the code:

    Map<Object, Object> map = ...
    map.put(PersistenceUnitProperties.JDBC_URL,...);
    map.put(PersistenceUnitProperties.JDBC_USER,...);
    map.put(PersistenceUnitProperties.JDBC_PASSWORD, ...);
    map.put(PersistenceUnitProperties.JDBC_DRIVER,  ...);
    map.put(PersistenceUnitProperties.CLASSLOADER, this.getClass()
            .getClassLoader());
    map.put(PersistenceUnitProperties.TARGET_DATABASE, "MySQL");
    entityManagerFactory = new PersistenceProvider()
            .createEntityManagerFactory("...", map);

Answer 1

EclipseLink should auto reconnect dead connections. EclipseLink will trap the error, test the connection and if dead reconnect and possibly retry the query (if outside a transaction).

But this depends on what connection pooling you are using, what is your persistence.xml.

Answer 2

The easiest way to do this would be to spawn a thread that sends some sort of keepalive or simple query every hour or so. Here, we would leave a flag so the thread can be shut down on program exit, db change, etc. If it needs to respond faster to that type of shutdown, you can change the counter in the for loop and the sleep time.

boolean parentKilledMe = false;
while (!parentKilledMe){
    //put query here
    for (int x = 0; x < 360 && !parentKilledMe;x++){
        try{
            Thread.sleep(6000);
        } catch (InterruptedException e) {
            //your error handling here
        }
    }
}