5

We are working on a java struts2 framework webapplication with Hibernate 3 ORM. We use mysql as our database.

We found a some DB related exceptions in logs. Connection to the database is timed out before the configured time. Here are the commons exceptions we found.

104343235 [pool-6-thread-19] ERROR org.hibernate.util.JDBCExceptionReporter  - The last packet successfully received from the server was 100,838,460 milliseconds ago.  The last packet sent successfully to the server was 100,838,461 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

org.hibernate.exception.JDBCConnectionException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at com.myproject.model.dao.entities.EntitiesDAO.getByIds(EntitiesDAO.java:148)

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 100,838,460 milliseconds ago.  The last packet sent successfully to the server was 100,838,461 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at sun.reflect.GeneratedConstructorAccessor345.newInstance(Unknown Source)104343242 [pool-6-thread-16] ERROR org.hibernate.util.JDBCExceptionReporter  - The last packet successfully received from the server was 100,838,544 milliseconds ago.  The last packet sent successfully to the server was 100,838,544 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
104343242 [pool-6-thread-16] ERROR org.hibernate.util.JDBCExceptionReporter  - The last packet successfully received from the server was 100,838,544 milliseconds ago.  The last packet sent successfully to the server was 100,838,544 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3829)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2449)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 21 more

Everyday we are restarting our app server as a temparory fix to the issue.

Any help will be highly appreciated.

Improve this question
2
  • See Here for reference Commented Oct 29, 2012 at 3:59
  • I am facing the same issue. How you solved this? Commented Apr 4, 2014 at 5:34

2 Answers 2

Reset to default
5

This happens because MySQL server terminates connections with no activity after a timeout. You need to add a few lines to the dbcp configuration Try adding the following parameters : 

validationQuery="SELECT 1"
testOnBorrow="true

How it works: The connection-pool tries running validationQuery before returning the connection. If the validationQuesry fails, dbcp discard the connection, creates a new one and return it.

Here's an example:

<Resource   name="jdbc/cooldatabase"
            description="Strandls.com license database"
            auth="Container"
            type="javax.sql.DataSource"
            factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/cooldatabase?autoReconnect=true"
            username="cooluser"
            password="coolpassword"
            initialSize="0"
            maxActive="20"
            maxIdle="10"
            minIdle="0"
            maxWait="-1"
            validationQuery="SELECT 1"
            testOnBorrow="true"
            poolPreparedStatements="true"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"/>

You can find complete details here : http://amitcodes.wordpress.com/2008/07/26/16/

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

How to do implement the same for c3p0 connection pooling?
I have never worked with c3p0 so far, but as per the discussion, there is a property called testConnectionOnCheckout: <property name="testConnectionOnCheckout">true</property> Here is the complete discussion: forum.hibernate.org/viewtopic.php?f=1&t=1015229
Source : community.jboss.org/wiki/… testConnectionOnCheckout Must be set in c3p0.properties, default value is false. Don't use it, this feature is very expensive. If set to true, an operation will be performed at every connection checkout to verify that the connection is valid. A better choice is to verify connections periodically using c3p0.idleConnectionTestPeriod.
we are using - <property name="hibernate.c3p0.idle_test_period">300</property> is this the one which you mentioned or its another one ?
My bad, c3p0.idle_test_period is the hibernate configuration key which over rides c3p0.idleConnectionTestPeriod.
|
1

Use connection pool with Hibernate. For example c3p0 https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.