MySQL CommunicationsException: Communications link failure


Sometimes I seem to lose connection to my mysql-database and then a connection is thrown and the program stops executing as it is now. I would like to try to reconnect to the database lets say 5 times more with a sleep() of 10 seconds in between to see if I can get the connection back. Which I guess will almost always be the case.

Is there some functionality for this in spring, like a reconnect() function somewhere or what would be the best way to accomplish this?

Not sure why the connection drops in the first case but may be some mysql-problem.

Any help would be appreciate

Solution

Are you actually loosing the connection? I guess your problem is that you are using database connection pools and are keeping open a number of connections. After about 8 hours MySQL will silently kill the connection-handler on it’s side. As soon as you try to use the connection that was connected to a killed handler, you will get the exceptions you are having.

the keep-alive option never really worked for us, so we switched to a connection pool, that is able to test a connection before returning it to the application. This validation-query solved allmost all of our problems.

Here comes our spring-config for the datasource:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${cweb.database.driver}"/>
        <property name="url" value="${cweb.database.url}"/>
        <property name="username" value="${cweb.database.user}"/>
        <property name="password" value="${cweb.database.password}"/>
        <property name="initialSize" value="5"/>
        <property name="maxIdle" value="5"/>
        <property name="maxActive" value="20"/>
        <property name="validationQuery" value="SELECT 1"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="10000"/>
        <property name="minEvictableIdleTimeMillis" value="60000"/>
</bean>

The trick with this solution is that the pool will issue the “SELECT 1” statement every 10 seconds on an idling connection. If a connection died, the pool will re-connect within 10 seconds. On the other hand whenever a connection is returned, at first the “SELECT 1” query is executed, and only if this was successfull, the connection will be returned. If it was not successfull, a new connection will be created and returned (of course this connection too will be tested first)

src: http://www.experts-exchange.com/Programming/Languages/Java/J2EE/Frameworks/Spring/Q_26468458.html

Advertisements

DBCP Datasource in Java Console


import java.sql.Connection;

import org.apache.commons.dbcp.BasicDataSource;

public class BasicDataSourceExample {

  public static void main(String args[]) throws Exception {

    BasicDataSource bds = new BasicDataSource();
    bds.setDriverClassName("com.mysql.jdbc.Driver");
    bds.setUrl("jdbc:mysql://localhost/commons");
    bds.setUsername("root");
    bds.setPassword("");

//    bds.setInitialSize(5);

    Connection connection = bds.getConnection();

    System.err.println(connection);
    connection.close();
  }
}