Connection to the Bank drops after a certain time

Question

I have a server where I have several web applications connected to the database(Mysql). The problem is that if any system gets idle it loses the connection to the bank and I have to refresh the application so that everything goes back to normal.

I was working around this problem with a Thread that was ping the database every 1 hour. But now that I’m working with WEBServices this method is no longer working.

I read in some places that the Mysql drops all connections if idle for 8 hours. And I also found several possible solutions, like setting up the C3P0, etc. But even doing these settings the error still persists. In the Tomcat have these mistakes:

Caused by: org.hibernate.TransactionException: commit failed
Caused by: org.hibernate.TransactionException: unable to commit against JDBC connection
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Caused by: java.net.SocketException: Software caused connection abort: socket write error

I’m using Hibernate, and my settings are that way:

<!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/bd</property>
        <property name="connection.username">root</property>
        <property name="connection.password"></property>
        <property name="connection.autoReconnect">true</property>
        <property name="connection.autoReconnectForPools">true</property>

        <!-- C3P0 -->
        <property name="c3p0.acquire_increment">5</property>
        <property name="c3p0.timeout">0</property>
        <property name="c3p0.min_size">3</property>
        <property name="c3p0.max_size">100</property>
        <property name="c3p0.max_statements">0</property>
        <property name="c3p0.idle_test_period">3000</property>

I even put the timeout as 0 because in some places they say that this way the connection never expires, but continues to expire.

Is my configuration right? Is it necessary to do some other configuration? Is there another way to solve this problem?

---

Updated

I realized that even with these errors I can still include, change and edit the data but every time I try to perform one of these operations I have as return error 500 and the screen is frozen, that is to say the fields are not cleared.

---

Updated 23-12-2015

Example of a method where I make the connection:

public void salvar(Agenda agenda) {
        Session sessao = HibernateUtil.getSessionFactory().openSession();
        Transaction transacao = null;
        try {
            transacao = sessao.beginTransaction();
            sessao.save(agenda);
            transacao.commit();
        } catch (RuntimeException ex) {
            ex.printStackTrace();
            throw ex;

        } finally {
            sessao.close();
        }
    }

All methods of DAO has the finally with the sessao.close().

I was thinking, I have this property on hibernate.cfg:

<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>

Could this interfere with the properties of C3P0 that I put?

Answer 1

There are several things to try to test, I will put some.

On the connection line you can try to put the autoReconnect argument

<property name="connection.url">jdbc:mysql://localhost:3306/bd?autoReconnect=true</property>

Also add a preview for C3P0

<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>  

And finally, the property to test the connection at a checkout.

Create a file c3p0.properties, or if you are already using the file c3p0-config.xml add the property as true http://www.mchange.com/projects/c3p0/index.html#testConnectionOnCheckout

A second alternative

Is to find the file my.ini or my.cnf in the directory of mysql respective to your operating system, in that link there are examples of paths where the directory is.

If you do not initially find it, also check that the file is not found occult. In either of them it works.

When finding, add the following lines with the values, which are recognized in seconds

wait_timeout = 99999999999999999999999 
interactive_timeout = 99999999999999999999999

By default these even implicit values are 8 horas, or 28800 segundos.

Answer 2

Take a look at the settings of this other post, I believe that with this your problem will be solved.

Watch out for the properties idleConnectionTestPeriod and maxIdleTimeExcessConnections

Best Configuration of c3p0 [closed]