getting jdbc transaction failed at first hit

Question

I am getting "JDBC transaction failed" at first hit to DB. I found one problem is that my MySQL is idle for 10 to 12 hours. So maybe after 10 hours when I am going to hit at login time getting exception.

My configuration is

<bean id="salesOptSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
            <prop key="hibernate.format_sql">true</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>                
            <prop key="hibernate.jdbc.batch_size">50</prop>
            <prop key="hibernate.current_session_context_class">thread</prop>
            <prop key="hibernate.connection.isolation">2</prop>             
            <prop key="hibernate.c3p0.min_size">5</prop>
            <prop key="hibernate.c3p0.max_size">20</prop>
            <prop key="hibernate.c3p0.timeout">300</prop>
            <prop key="hibernate.c3p0.max_statements">50</prop>
            <prop key="hibernate.c3p0.idle_test_period">3000</prop>
            
        </props>
    </property>
    <property name="packagesToScan" value="com.perennialsys.salesoptimization.vo" />
</bean>

How can I solve this?

I found from googling that idle_test_period should not exceed timeout. Is this the reason?

See http://stackoverflow.com/questions/8535433/spring-app-losing-connection-to-mysql-after-8-hours-how-to-properly-configure — M. Deinum, Jun 08 '16 at 08:17
didn't get..please see above configuration and help me for updating above proprties — Musaddique S, Jun 08 '16 at 08:23

score 1 · Answer 1 · answered Jun 08 '16 at 12:06

The database server may close a connection on its side after a certain amount of time - causing some error in your application, because it'll attempt to send a query on a connection which is no longer available on the server side.

In order to avoid this you can let the pool periodically check a connection for it's validity. This is what idle_test_period is for.

timeout is the timespan after which the pool will remove a connection from the pool, because the connection wasn't checked out (used) for a while and the pool contains more connections than c3pO.min_size.

Please keep in mind, that hibernate.c3p0.idle_test_period value must never exceed that of hibernate.c3p0.timeout. Otherwise C3P0 will never detect connections that has been closed.

Thanks, Rajesh

getting jdbc transaction failed at first hit

1 Answers1