Bug #11259 Cannot connect using connectionpooling with Tomcat 5.5.9
Submitted: 11 Jun 2005 9:19 Modified: 14 Jun 2005 21:32
Reporter: M Wensink Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:latest snapshot OS:Microsoft Windows (w2k)
Assigned to: CPU Architecture:Any

[11 Jun 2005 9:19] M Wensink
Description:
un 11, 2005 11:13:17 AM org.apache.catalina.core.ApplicationContext log
SEVERE: Can't delete reservations: 
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: No operations allowed after connection closed.'.

** BEGIN NESTED EXCEPTION ** 

java.sql.SQLException
MESSAGE: No operations allowed after connection closed.

STACKTRACE:

java.sql.SQLException: No operations allowed after connection closed.
	at com.mysql.jdbc.Connection.checkClosed(Connection.java:1834)
	at com.mysql.jdbc.Connection.versionMeetsMinimum(Connection.java:5315)
	at com.mysql.jdbc.Field.<init>(Field.java:170)
	at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:636)
	at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:412)
	at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1955)
	at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1380)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
	at com.mysql.jdbc.Connection.pingInternal(Connection.java:4069)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2675)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1477)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:264)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
	at frs.beans.reserv.ReservationRegistryBean.deleteReservations(ReservationRegistryBean.java:373)
	at frs.servlets.ResourceManagerListener$Cleaner.run(ResourceManagerListener.java:202)

** END NESTED EXCEPTION **

How to repeat:
Bug emerges on every start

Suggested fix:
??
[13 Jun 2005 8:01] Vasily Kishkin
Could you please provide more information about this bug ? Could you write here any test case ?
[13 Jun 2005 16:01] M Wensink
I have made a smal website to test the bug I previously reported. You have to unpack te zip, plave the server.xml in the tomcat conf directury and the bugtest directory in the webapps directory of tomcat.
The problem is with the autoReconnect feature. Disabling this in the server.xml does not give the problem anymore
[13 Jun 2005 16:42] M Wensink
I did not manage to send the zip. I have isolated the file in the testprogram below:

import com.mysql.jdbc.jdbc2.optional.*;
import java.sql.*;

public class Test {

   private static final String server   = "localhost";
   private static final String database = "test";
   private static final String username = "root";
   private static final String password = "root";

   private Statement stmt;

   public static void main (String [ ] args)
   {
      try
      {  System.err.println ("Initialize datasource");
         MysqlDataSource ds = new MysqlDataSource();
         ds.setServerName (server);
         ds.setDatabaseName (database);
         ds.setUser (username);
         ds.setPassword (password);
         ds.setAutoReconnect (true);

         System.err.println ("Setup database connection\n");
         Connection conn = ds.getConnection();

         test (conn);
      }
      catch (Exception e)
      {  e.printStackTrace (System.err);
         System.exit (1);
      }
   }

   private static void test (Connection conn) throws Exception
   {
      Statement stmt = conn.createStatement();
      try {
         stmt.executeUpdate ("DROP TABLE IF EXISTS test");
         stmt.executeUpdate ("CREATE TABLE test (field integer)");
         stmt.executeUpdate ("INSERT INTO test (field) VALUES (0)");

         PreparedStatement ps = conn.prepareStatement ("SELECT * FROM test");
         ResultSet rs = ps.executeQuery();
      	while (rs.next())
      	{	Integer i = (Integer) rs.getObject ("field");
            System.out.println ("field: " + i);
	      }
			ps.close();
      }
      finally {
         stmt.executeUpdate ("DROP TABLE IF EXISTS test");
      }
   }
}
[14 Jun 2005 21:32] Mark Matthews
Fixed for 3.1.9. You should be able to test this with a nightly snapshot after 00:00 GMT Jun-15 from http://downloads.mysql.com/snapshots.php#connector-j

Thanks for your bug report.

btw, autoReconnect is going to get deprecated in Connector/J 3.2. You should look at something that is more robust, such as the example listed at:

http://dev.mysql.com/doc/connector/j/en/cj-faq.html#id2829608

You should also configure your connection pool to not allow stale connections, and periodically test them if stale connections are an issue for your application.