MySQL Bugs: #6561: Connector-J generates null pointer exception

Bug #6561	Connector-J generates null pointer exception
Submitted:	10 Nov 2004 22:06	Modified:	22 Nov 2004 21:11
Reporter:	M Wensink	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	latest snapshot	OS:	Windows (win2k)
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any

Description:
I'm using the Java connector in a webapplication using jakarta Struts and DBCP.
With the latest snapshot I get a null pointer exception after deletion of a row containing a foreign key. The next example is an excerpt of my tables.
After the execution of the delete statement field1 of the last row of table test2 is set to null. In my application an exception is thrown, when after querying table test2, the value of field2 is used. This value also appears to be null??? 

Unfortunately the test does not generate the exception. I have spent some hours to isolate the problem from my application, but I didn't manage.
Still I think it is connector problem, because when I use the 'old' 3.0 connect the exception is not generated.

   private static void test (Connection conn) throws Exception
   {
      Statement stmt = conn.createStatement();
      try {
         stmt.executeUpdate ("DROP TABLE IF EXISTS test2");
         stmt.executeUpdate ("DROP TABLE IF EXISTS test1");
         stmt.executeUpdate ("CREATE TABLE test1 (field1 INTEGER, PRIMARY KEY (field1))");
         stmt.executeUpdate ("CREATE TABLE test2 (field1 INTEGER, field2 INTEGER, " +
                             "KEY field1 (field1), KEY field2 (field2), " +
                             "CONSTRAINT fk1 FOREIGN KEY (field1) REFERENCES test1 (field1) ON DELETE SET NULL, " +
                             "CONSTRAINT fk2 FOREIGN KEY (field2) REFERENCES test1 (field1) ON DELETE CASCADE)");
         stmt.executeUpdate ("INSERT INTO test1 (field1) VALUES (1)");
         stmt.executeUpdate ("INSERT INTO test1 (field1) VALUES (2)");
         stmt.executeUpdate ("INSERT INTO test1 (field1) VALUES (3)");
         stmt.executeUpdate ("INSERT INTO test2 (field1, field2) VALUES (1,1)");
         stmt.executeUpdate ("INSERT INTO test2 (field1, field2) VALUES (2,2)");
         stmt.executeUpdate ("INSERT INTO test2 (field1, field2) VALUES (1,3)");

         stmt.executeUpdate ("DELETE FROM test1 WHERE field1 = 1");

         PreparedStatement ps = conn.prepareStatement ("SELECT * FROM test2");
         System.out.println ("PS: " + ps.toString());
         ResultSet rs = ps.executeQuery();
         while (rs.next())
         {  Integer i1 = (Integer) rs.getObject ("field1");
            System.out.println ("I1: " + i1);
            Integer i2 = (Integer) rs.getObject ("field2");
            System.out.println ("I2: " + i2);
         }
         ps.close();
      }
      finally {
         stmt.executeUpdate ("DROP TABLE IF EXISTS test2");
         stmt.executeUpdate ("DROP TABLE IF EXISTS test1");
      }
   }

How to repeat:
See above

Suggested fix:
???

I have done some further investigation. If I enable dontUnpackBinaryResults, no exception is thrown, but I get wrong information.
I the test example this means that I get the rows (2,2) and (2,3). So I get the last row with field1 value of 2 (the other row) in stead of null.

I also tried an old snapshot, dated 2004/09/13 18:45:18 $, $Revision: 1.27.4.44, which does not throw an exeception neither gives me wrong information.

I hope this will be helpful.

Marten

I managed to isolate the bug (se next program). The values of two consecutive fields are interchanged if the value of a preceeding DATE-field is 0. It looks like a very nasty bug!

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.setZeroDateTimeBehavior ("convertToNull");

         System.err.println ("Setup database connection");
         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 (field1 DATE, field2 integer, field3 integer)");
         stmt.executeUpdate ("INSERT INTO test (field1,field2,field3) VALUES (0,NULL,0)");
         stmt.executeUpdate ("INSERT INTO test (field1,field2,field3) VALUES ('2004-11-20',NULL,0)");

         PreparedStatement ps = conn.prepareStatement ("SELECT * FROM test");
         ResultSet rs = ps.executeQuery();
      	while (rs.next())
      	{	java.sql.Date d = (java.sql.Date) rs.getObject ("field1");
            System.out.println ("field1: " + d);
      		Integer i = (Integer) rs.getObject ("field2");
            System.out.println ("field2: " + i);
            i = (Integer) rs.getObject ("field3");
            System.out.println ("field3: " + i + "\n");
	      }
			ps.close();
      }
      finally {
         stmt.executeUpdate ("DROP TABLE IF EXISTS test");
      }
   }
}

I'm not able to repeat this with the November 22 snapshot. The code that unpacks binary result sets from prepared statements has been reworked, so this bug might be fixed.

Please re-test with the November 22 snapshot.

You are right. I worked on this problem on last saturday, using the snapshot of that moment. This morning I have tested the new snapshot and the bag seems to be fixed. Hurray!