This problem is soooooo common that the solutions described above should work in most of the cases, however, it wasn't my case:
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.
If you have the same problem, but in a web application, this please read on; some of the things described here are still useful or at least you should check them out.
Yes, I am new in hibernate and I miss out this:
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.
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;
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:
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)
Catch it!!! I had this:
<bean id="hibernateTemplate"
class="org.springframework.orm.hibernate3.HibernateTemplate"
scope="prototype">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>
An this is wrong. The HibernateTemplate object must be declared singleton (maybe “session” in a web application to avoid multithreading issues). This saves lots of session objects and keep them alive because they are always been called across the application.
However, I am starting to think that HibernateTemplate doesn't close properly it's Session objects. If this were the case, it will explain why when I release my HibernateTemplate objects the corresponding session objects weren't released and closed.
I've discovered these things that I think they could help in solving the problem:
You can se the parameter “releaseMode” to the value “AFTER_STATEMENT” and let C3P0 get this open sessions to mysql back. With this, the open sessions that hibernate gets, are released in the quickest way. This is discouraged in the documentation, but bear in mind that if you have put a pooling mechanism, this could act as a safety net catching this connections and reusing them.
<!-- Possible values for this are: ON_CLOSE : legacy behavior. The session gets the connection when the first time it requires it and release it when it's destroyed AFTER_TRANSACTION : session is released after Transaction issues a commit or rollback AFTER_STATEMENT : connection is released after execution of every statement, BUT it will keep the connection open if the object --> <prop key="hibernate.connection.release_mode"></prop>
For me, this have been the silver bullet that can fix a broken application whihout the need to go back to development to fix the issue.
The configuration parameter unreturnedConnectionTimeout does the following:
However, it is discouraged because it hides problems in the application. But if it can save your day, take it into account.
You have also this parameter: debugUnreturnedConnectionStackTraces that can help to debug those connection that the application stores and doesn't return back until it's too late.
First, define C3P0 datasource:
<!--
A better datasource: it gives a new connection each time
is requested.
-->
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close"
scope="singleton">
<!--
To test a connection:
mysql -h HOSTNAME -u USERNAME -p
and aftwerwards introduce the password when requested
-->
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://${mysql-server}:${mysql-port}/${mysql-database}"/>
<property name="user" value="${mysql-user}"/>
<property name="password" value="${mysql-password}"/>
<!--
Recommended values for maxPoolSize and minPoolSize:
1. get value of max available connections of your
database issuing command 'show variables like "max_connections";'
2. this will be the upper limit for maxPoolSize: you shouldn't
reach never this limit
3. check out with show processlist the active connections
to the database: this will help to determine the available
connections for your application:
(show processlist) - (show variables like max_connections)
4. it is advisable to identify how many DAO objects will be
issuing sentences to the database at the same time. Ok,
make a rough stimation. Say that this value will be
10. A good and steady value for maxPoolSize can
be 40 (four times).
-->
<property name="maxPoolSize" value="10"/>
<property name="minPoolSize" value="5"/>
<!--
checkoutTimeout mits how long a client will wait for a
Connection, if all Connections are checked out and one
cannot be supplied immediately. In milliseconds.
http://www.mchange.com/projects/c3p0/#checkoutTimeout
-->
<property name="checkoutTimeout" value="10000"/>
<property name="acquireIncrement" value="1"/>
<property name="idleConnectionTestPeriod" value="100"/>
<!-- property name="preferredTestQuery" value="select 1"/ -->
<!--
testConnectionOnCheckout is not recommended because it
checks the connection before give it to hibernate. In a
paranoid environment could fix some problems
-->
<!-- property name="testConnectionOnCheckout" value="true"/-->
<!--
debugUnreturnedConnectionStackTraces prints a stack trace when
a connection that is given to hibernate reachs its timeout value.
This could help to debug cases when a connection is taken and
not released in a certain amount of time
http://www.mchange.com/projects/c3p0/#debugUnreturnedConnectionStackTraces
-->
<!-- property name="debugUnreturnedConnectionStackTraces" value="true"/-->
<!--
unreturnedConnectionTimeout is the silver bullet for a problem in
a greedy application (in terms of sessions). It will count how long
hibernate has taken a connection, and if reachs a timeout, c3p0 will
replace this connection with a fresh session, avoiding the problem
of the database may close opened for much time
http://www.mchange.com/projects/c3p0/#unreturnedConnectionTimeout
-->
<!--property name="unreturnedConnectionTimeout" value="100"/-->
</bean>
Second, the hibernateTemplate and jdbctemplate objects of spring will be available accross the application. Beware!!! If you are developing a web application, these should be session instead of singleton!!!
<bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate" scope="singleton"> <constructor-arg ref="dataSource"/> </bean> <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate" scope="singleton"> <property name="sessionFactory" ref="sessionFactory"/> </bean>
Finally, the sessionFactory:
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" scope="singleton"> <property name="dataSource" ref="dataSource"/> <property name="annotatedClasses"> <list> <value>com.mapfre.dga.mercurio.entity.Detail</value> <value>com.mapfre.dga.mercurio.entity.Indicator</value> <value>com.mapfre.dga.mercurio.entity.Period</value> <value>com.mapfre.dga.mercurio.entity.Struct</value> <value>com.mapfre.dga.mercurio.entity.Valoration</value> <value>com.mapfre.dga.mercurio.entity.Version</value> </list> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop> <!-- for debug, put these values to "true" --> <prop key="hibernate.show_sql">${show-sql-in-hibernate}</prop> <prop key="hibernate.format_sql">${format-sql-in-hibernate}</prop> <!-- Possible values for this are: on_close : legacy behavior. The session gets the connection when the first time it requires it and release it when it's destroyed after_transaction : session is released after Transaction issues a commit or rollback after_statement : connection is released after execution of every statement, BUT it will keep the connection open if the object --> <!-- prop key="hibernate.connection.release_mode">after_statement</prop --> </props> </property> </bean>
I've put plenty of comments of useful configuration parameters and lots of information, just in case.
http://www.mchange.com/projects/c3p0/
~~DISQUS~~