Hibernate and mysql timeout issue

Question

I have been having trouble with Hibernate and Mysql timeout error.I am also using properties of c3p0(connection provider). After my Hibernate/MySQL have been running after 8 hours(which is default timeout value in Mysql), I have the exception. But it doesn't help.

property for auto reconnect also not working.

Here is my Hibernate Configuration:

<property name="connection_provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    **<property name="connection.autoReconnect"> true</property>
    <property name="connection.autoReconnectForPools">true</property>**
    <property name="connection.is-connection-validation-required">true</property>
    <property name="c3p0.validate">true</property>
    <property name="current_session_context_class">thread</property>
    <property name="cache.use_query_cache">false</property>
    <property name="cache.use_second_level_cache">false</property>
    <property name="c3p0.idle_test_period">20</property>
    <property name="c3p0.timeout">40</property>
    <property name="c3p0.max_size">100</property>
    <property name="c3p0.min_size">1</property>
    <property name="c3p0.acquireRetryAttempts">10</property>
    <property name="c3p0.maxPoolSize">100</property>
    <property name="c3p0.maxIdleTime">300</property>
    <property name="c3p0.maxStatements">50</property>
    <property name="c3p0.minPoolSize">10</property>
    <property name="c3p0.preferredTestQuery">select 1;</property>
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.url">jdbc:mysql://localhost:3306/doqeap</property>
    <property name="connection.user">root</property>
    <property name="connection.password">*******</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.jdbc.batch_size">0</property>
    <mapping></mapping>

please help me to sort out this problem. Thanks

Answer 1

If the Connection timeout is the issue, then Connection testing should eliminate, wither via tests on checkout (reliable but imposes a client visible performance cost) or tests on checking + idle tests.

Looking at you config params, it looks like you mean to set tests on checkouts and idle tests. I'd expect that c3p0 would eliminate timed out Exceptions before your app saw them. If that hasn't happened, it'd be interesting to see two things: 1) c3p0's config, which gets logged at INFO when the pool is initialized -- is c3p0, through the hibernate layer, seeing the configuration you intend? 2) the Exception that your app receives when it encounters the stale Connections.

Good luck!