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


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"/>

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)



Connecting database using Websphere Datasource

protected void doGet(HttpServletRequest request,
          HttpServletResponse response) throws ServletException, IOException 
          PrintWriter out = response.getWriter();
          out.println("WAS + MySQL Example");
          out.println("<H1>WAS + MySQL Example</H1>");
          try {
               Context ctx = new InitialContext();
               DataSource ds = (DataSource) ctx
               Connection connection = ds.getConnection();
               Statement stmt = connection.createStatement();
               ResultSet resultSet = stmt.executeQuery("select * from testtbl");
               while ( {
                    String ssn = resultSet.getString("SSN");
                    String lastName = resultSet.getString("LASTNAME");
                    String firstName = resultSet.getString("FIRSTNAME");
                    out.print(ssn + ": ");
                    out.print(firstName + " ");
                    out.println(lastName + "<BR>");
          } catch (NamingException e) {
          } catch (SQLException e) {

More info