I've detected that this page receives many visits. Unfortunately, I am still struggling with this problem and nothing that I have tried before have worked, so read the rest of the page at your own risk.

This problem is driving me mad since more than three months. I've tried many things to find out a solution, but at the present moment (30/09/2013) nothing is working.

However, I am creating this page to register all the solutions and as an advice for other people.

• Have tried the well known solutions: solution1, solution2
• Have searched in google for solutions to this problem and nothing works for you

This problem is soooooo common that the solutions described above should work in most of the cases, however, it wasn't my case:

• My application worked perfectly
• Mysql worked perfectly
• There wasn't a network error
• So… what is the problem???

Before going on, I think it's important to describe what's my architecture. If it doesn't match yours, probably the solutions described here won't work in your case.

• Mysql as persistence engine (I love this: persistence engine. In my times this was simple a RDBMS, but now… wow! it's a persistence engine!)
• C3P0 as connection pooling mechanism
• Hibernate as data access layer
• Spring to tie all together
• A batch application that runs for hours and then stops. It only have one thread: starts, do thounsands of queries and then stops.

PENDIENTE: INDICAR AQUÍ LOS DETALLES DE LA CONFIGURACION HIBERNATE Y C3P0

Yes, I am new in hibernate and I miss out this:

http://stackoverflow.com/questions/14554353/database-connection-to-mysql-times-out-even-after-setting-c3p0-testconnectiononc

Apparently, the retention of Hibernate Sessions can more time than the value of wait_timeout can produce this problem.

This could be my case, because it happens that I found one Session object stored as a private property inside of a singleton: this property could be held for many time after the first query is made. And the first query was made in the creation of the database.

1. Configure your wait_timeout value to a low value, like five minutes or less:

wait_timeout = 60 
# just in case 
interactive_timeout = 60

- **Make a test.** I've built the following test, which is pretty straightforward: 

    @Test
    public void test()
    {
        Period p = entityProvider.randomExistingPeriod();
 
        // issue a query
        StringBuilder hql = new StringBuilder();
 
        hql.append( "from Table where period = :period" );
 
        Query q = session.createQuery( hql.toString() );
        q.setEntity( "period", p );
 
        @SuppressWarnings("unchecked")
        List<Period> result = q.list();
 
        log.info( "Sleeping...." );
        try
        {
            Thread.sleep( 10 * 60 * 1000 ); // ten minutes
        }catch( InterruptedException ex )
        {
            log.info( "Awakening..." );
        }
 
        // issue a second query
        // issue a query
        StringBuilder hql2 = new StringBuilder();
 
        hql2.append( "from Table where period = :period" );
 
        Query q2 = session.createQuery( hql.toString() );
        q2.setEntity( "period", p );
 
        @SuppressWarnings("unchecked")
        List<Period> result2 = q2.list();
 
        assertNotNull( result );
        assertNotNull( result2 );
        assertTrue( result.size() > 0 );
        assertTrue( result2.size() > 0 );
 
    } // test

In other words;

1. issue a valid query to the database
2. wait an amount of time greater than the wait_timeout you have configured
3. issue a valid query to the database. This second query will show up the problem

2013-09-03 15:52:53,634 DEBUG c3p0.impl.NewPooledConnection            - com.mchange.v2.c3p0.impl.NewPooledConnection@c22a3b invalidated by Exception.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 600.214 milliseconds ago.  The last packet sent successfully to the server was 15 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3090)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
[....]

The solution is as easy as not keeping the hibernate Session object too much in memory. Saying this is easy, but achiving this….

For myself, it worked the following countermeasures:

• Bear always in mind that the Session object can't be kept in memory too much time, and close as quick as you can
• Avoid saving Session beans as members of classes; use it only in local variables will reduce the exposure to this problem
• Lower the wait_timeout value for your application in development. I've discovered that lower values of wait_timeout in the config file are rejected, so it is best to have a small bean that issues this command upon execution for development: this will ensure you that only your application is enforced to work with this values instead of changing the values for all the database

To change the wait_timeout value for a particular session:

SET SESSION wait_timeout = 300;

In my case I have a batch application that longs for 24 hours. So putting a big value in wait_timeout will solve the problem. Here is how to do this programmatically:

mysql> SET global interactive_timeout = 172800;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SET global wait_timeout = 172800;
Query OK, 0 ROWS affected (0.01 sec)

You have to change interactive_timeout also because, depeding on the type of session is starting, the value wait_timeout can be set to the value of ineractive_timeout.

I've developed a bean who issues mysql commands to the database. With this, I've configured it to set low wait_timeout values for the testing sessions. With this, all the tests made will use low values, hence allowing all the possible errors arise before they reach to production.

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
 
/**
 * Allows to set commands to the sql database, like set commands.
 *
 * @author RLUNARO
 *
 */
public class MysqlConfigurator
{
    private SimpleJdbcTemplate jdbc;
    private List<String> initialListOfCommands;
 
    public SimpleJdbcTemplate getJdbc()
    {
        return jdbc;
    }
 
    public void setJdbc(SimpleJdbcTemplate jdbc)
    {
        this.jdbc = jdbc;
    }
    public List<String> getInitialListOfCommands()
    {
        return initialListOfCommands;
    }
 
    public void setInitialListOfCommands(List<String> initialListOfCommands)
    {
        this.initialListOfCommands = initialListOfCommands;
    }
 
    /**
     * This method must be called upon set of the initial properties.
     */
    public void init()
    {
        // traverse list of commands and run them
        for( String command : initialListOfCommands )
        {
            issueStatement( command );
        } // command : initialListOfCommands
    }
 
    public void issueStatement( String command )
    {
        jdbc.update( command );
    }
 
    public String queryVariable( String variable )
    {
        String sql = "show variables like ? ";
        List<Object> args = new ArrayList<Object>();
        args.add( variable );
        List<Map<String,Object>> result = jdbc.queryForList( sql, args.toArray() );
        return (String) result.get(0).get( "Value" );
    }
 
}

And here is the spring configuration I am using (see below). I am using a singleton to load the bean at the beginning of the program and make sure that the values are set.

  <bean id="mysqlConfigurator"
        class="com.mapfre.dga.mercurio.mock.MysqlConfigurator"
        init-method="init"
        scope="singleton">
      <property name="jdbc" ref="simpleJdbcTemplate"/>
      <property name="getInitialListOfCommands">
        <list>
          <value>set session wait_timeout = 30</value>
        </list>
      </property>
   </bean>

After this, sometimes I have the same problem, others I get this error.

com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@1665147 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)

I've discovered these things that I think they could help in solving the problem:

• configuring when hibernate releases its connections (and don't forget this and this)

Ater trying the parameter debugUnreturnedConnectionStackTraces in the application, I've found that I've starved the available connections.

I think that the problem maybe that I've configured HibernateTemplate as prototype, that is, it is created a new HibernateTemplate for every data access object in the application, hence using more connections than the strictly neccesary.

I've configured release after_statement to see if this fixes the problem.

PENDIENTE VER SI ESTA EJECUCION RESUELVE EL PROBLEMA

All these tests have been made on corporate Windows machines. I am starting to think that some kind of security policy is imposed in my user that limits the activity of my windows machine. To be studied…

~~DISQUS~~