MySQL Bugs: #12596: wrong behavior if connection timeout reached

Bug #12596	wrong behavior if connection timeout reached
Submitted:	16 Aug 2005 10:08	Modified:	16 Aug 2005 12:52
Reporter:	Riccardo Cohen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	4.1.12	OS:	Windows (windows+linux)
Assigned to:		CPU Architecture:	Any

Description:
We used the latest mysql4 and jdbc driver 3.1.10 on windows and linux.

After the connection timeout (wait_timeout), the function Connection.isClosed() answers the wrong value (ie: true). After that, a select fails, and then the response is correct(ie:false).

The temporary patch consist in making a "select 1" before checking for the connection, but I'd prefer the driver to answer directly the right answer
You can reproduce the bug in attached source

How to repeat:
//|
//| Copyright (C) Articque Informatique
//| All rights reserved. Duplication and distribution prohibited.
//| Les Roches, 37230 Fondettes France
//| Telephone +33 02.47.49.90.49
//| Fax +33 02.47.49.91.49
//| E-Mail: info@articque.com
//|

/*
Mysql version : 4.1.12
Driver version: mysql-connector-java-3.1.10_bin.jar
IBM jdk1.4.2

RESULT:

Connection NOT ok             // first loop
reconnected ok
Select Query ok !
Connection ok                 // second loop : the connection looks ok in java but it is actually closed by mysql
Exception in select query
Connection NOT ok             // third loop : now the driver knows the connection is closed => reconnecting
reconnected ok
Select Query ok !
Connection ok                 // fourth loop : the connection looks ok in java but it is actually closed by mysql
Exception in select query
*/

package mysql_connect;

import java.lang.*;
import java.sql.*;
import java.util.*;

public class mysql
{

  //MMMMMMMMMMMMM
  public static void main(String[] args)
  {
    Connection db_conn=null;
    //set driver name
    try
    {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
    }
    catch (Exception E)
    {
      System.out.println("Exception : driver error");
    }
    while(true)
    {
      //test is connection is ok
      boolean fail=true;  
      try
      {
        fail=(db_conn==null || db_conn.isClosed());
      }
      catch (Exception Ex)
      {
        System.out.println("Exception in connection");
      }
      if(!fail) //connection is ok
      {      
        System.out.println("Connection ok");
        //Do a SelectQuery to really test the connection
        Statement db_stmt=null;
        try
        {
          db_stmt=db_conn.createStatement();
          db_stmt.setQueryTimeout(10);
          if(db_stmt.executeQuery("select 1")!=null)
            System.out.println("Select Query ok !");
          else
            System.out.println("Select Query Fail !");
        }
        catch (Exception e)
        {
          System.out.println("Exception in select query");
        }
      }
      else //connection is deconnected, so reconnect it
      {
        System.out.println("Connection NOT ok");
        // Connect to database
        try
        {
          db_conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1/sncf","lib_sncf","****");
        }
        catch (Exception e)
        {
          System.out.println("Unable to connect to : lib_sncf");
        } 
        //test if reconnected
        try
        {
          fail=(db_conn==null || db_conn.isClosed());
        }
        catch (Exception Ex)
        {
          System.out.println("Impossible to test connection");
        }
        if(!fail)//connection is ok
        {
          System.out.println("reconnected ok");
          //Do a SelectQuery to really test the connection     
          Statement db_stmt=null;
          try
          {
            db_stmt=db_conn.createStatement();
            db_stmt.setQueryTimeout(10);
            if(db_stmt.executeQuery("select 1")!=null)
              System.out.println("Select Query ok !");
            else
              System.out.println("Select Query Fail !");
              
          }
          catch (Exception e)
          {
            System.out.println("Exception in select query");
          }
        }
        else
          System.out.println("impossible to reconnect");
      }
      try{Thread.sleep(1000*35*1);}catch(Exception E){}// 35 secondes 
      //mysql wait_timeout= 20 sec, so connection is lost and need a reconnection
    }
  }

}

The JDBC specification explicitly forbids isClosed() returning whether the connection is "valid" or not. The _only_ thing isClosed() does is tell the caller whether close() has been called on the connection.

JDBC-4.0 looks to have an isValid() method. isClosed() can _not_ be implemented the way in which you've implemented it, because that "SELECT " may _never_ return based on how the network has failed (if it has failed). This violates the contract that JDBC clients expect, because isClosed() is documented in general to not be a blocking method.

In general, it doesn't make a lot of sense to let a JDBC connection sit idle long enough for wait_timeout to expire (it means you are wasting both server and client-side resources). Therefore you should configure things in your application to not do so.

Thanks for the quick answer.
Actually the connection is in my connection pool, and should stay connected (if not what's the use of a connection pool :) )
I thought that IsClosed() would tell me if it is still connected (for timeout pb, network pb or else) Could you tell me if there is a function for that ?
Thanks

There is currently no method available to check whether a connection is valid or not. (The previous comment said it is being planned for JDBC-4.0).

At least with MySQL, the reason for a connection pool is to _limit_ the ultimate number of connections that are created. Since it only takes a few milliseconds to open a connection to MySQL, there is not a compelling reason in most cases to hold connections idle for more than a few minutes, and certainly not as long as wait_timeout which by default is 8 hours.

Thanks a lot