0

The following xml config file stops working after 8 hours of inactivity with this Error:

ERROR: Already closed.

25-may-2017 8:45:23 org.apache.catalina.core.StandardWrapperValve invoke

Servlet.service() for servlet [dispatcher] in the context with path [/system] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: ] with root cause:
java.net.SocketException: **Software caused connection abort: recv failed**

Below is the Configuration file:

<context:component-scan base-package="es.company.system.persistence" />

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="es.mypackage.model"></property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
</bean>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">

    <property name="connection.driver_class" value="com.mysql.jdbc.Driver" />
    <property name="connection.url" value="jdbc:mysql://localhost:3306/system" />
    <property name="connection.username" value="???" />
    <property name="connection.password" value="???" />

    <property name="hibernate.c3p0.min_size" value="5" />
    <property name="hibernate.c3p0.max_size" value="20" />
    <property name="hibernate.c3p0.timeout" value="1800"/>
    <property name="hibernate.c3p0.max_statements" value="50" />

    <property name="hibernate.c3p0.testConnectionOnCheckout" value="true" />

    <property name="hibernate.c3p0.privilegeSpawnedThreads" value="true" />
    <property name="hibernate.c3p0.contextClassLoaderSource" value="library" />

</bean>

<bean id="transactionManager"
    class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

<bean id="persistenceExceptionTranslationPostProcessor"
    class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

<tx:annotation-driven transaction-manager="transactionManager" />

I'm currently using Spring MVC 4.3.8 and Hibernate 4.1.9. with C3P0 conections pool for MySQL. I thought with C3P0 this would be solved, but it's not. Anyone knows what's wrong?

Thanks!!!

Afridi
  • 6,753
  • 2
  • 18
  • 27
Newbie83
  • 25
  • 8

2 Answers2

2

Software caused connection abort: recv failed -- This usually means that there was a network error, such as a TCP timeout.

also set timeout param with default = 0 for hibernate.c3p0.timeout

hibernate.c3p0.timeout – When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.

set autoReconnect , hibernate.c3p0.autoReconnect = true or as url connection param : jdbc:mysql://localhost:3306/test?autoReconnect=true

Add < property name="hibernate.c3p0.preferredTestQuery">SELECT 1</property> and check it log that c3p0 execute it(if not add real select count(1) from real table with limit 1). Also check your network timeout

xyz
  • 5,228
  • 2
  • 26
  • 35
  • Added new option config – xyz Jul 04 '17 at 08:34
  • Did you see slect 1 from in log file?check network timeout. – xyz Jul 05 '17 at 06:39
  • As your networ admin adout timeout. – xyz Jul 05 '17 at 06:40
  • What is you application server?tomcat? – xyz Jul 05 '17 at 06:42
  • Yes, the server is tomcat. – Newbie83 Jul 05 '17 at 15:51
  • I think settings that you have are that you can have. As i said next step is network.actually it firts step but you did all that you can from your side.if you work in local network try to discusss it eith admin team.https://stackoverflow.com/questions/135919/java-net-socketexception-software-caused-connection-abort-recv-failed – xyz Jul 05 '17 at 16:31
  • But I don't understand... I have other web applications working with the same data base and they don't stop working after some hours... – Newbie83 Jul 06 '17 at 12:42
  • on the save server ? check connection setting and timeout for working applications – xyz Jul 06 '17 at 12:45
  • Yes, on the same server. I've never changed any options. Everything is working with default values. – Newbie83 Jul 06 '17 at 14:19
1

I have experienced this issues some time google cloud sql where the SQL server breaks all the connection after idle time out of 10 hrs. But in the application we still have the sessionFactory instance and can create sessions but when you actually fire a db query it fails with broken connection exception.

None of the below attempts worked. 1. auto reconnect set in connection url 2. auto reconnect, timeout, validation properties in xml file. 3. Wrote a polling service to make a jdbc call to keep the connection active for every 8 hrs (<10 hrs idle time out period) 4. Closed and opened new sessionFactory for every db operation.

Finally i have created a polling service which will shutdown the sessiongFactory and create a new one. NOTE: Calling close method on sessionFactory object is not sufficient. You need to explicit set it to null; This may be a workaround but it saved me from day restart of the application. I was not using spring just rest service with hibernate.

Venkat
  • 147
  • 1
  • 1
  • 10