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

Connecting database using Websphere Datasource


protected void doGet(HttpServletRequest request,
          HttpServletResponse response) throws ServletException, IOException 
{
          response.setContentType("text/html");
          PrintWriter out = response.getWriter();
          out.println("<HTML><HEAD><TITLE>");
          out.println("WAS + MySQL Example");
          out.println("</TITLE></HEAD><BODY>");
          out.println("<H1>WAS + MySQL Example</H1>");
          try {
               Context ctx = new InitialContext();
               DataSource ds = (DataSource) ctx
                         .lookup("java:comp/env/jdbc/mysqldsref");
               Connection connection = ds.getConnection();
               Statement stmt = connection.createStatement();
               ResultSet resultSet = stmt.executeQuery("select * from testtbl");
               while (resultSet.next()) {
                    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) {
               e.printStackTrace();
          } catch (SQLException e) {
               e.printStackTrace();
          }
          out.println("</BODY></HTML>");
          out.close();
}

More info
http://www.devx.com/Java/Article/31571/1954