Bug #25009 ResultSet is from UPDATE. No Data
Submitted: 12 Dec 2006 14:33 Modified: 22 Feb 2007 14:10
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version: OS:
Assigned to: Mark Matthews CPU Architecture:Any

[12 Dec 2006 14:33] Mark Matthews
Description:
I built a simple test case to reproduce this bug. I can reproduce it using mysql
4.1.10 and 5.0.24a on win2k with connector/j 3.1.13 and 5.0.3.

Just run the java program a couple of times and it'll eventually throw an
SQLException with the message: ResultSet is from UPDATE. No Data. Which should
not happen after executing a SELECT statement.

Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Simple test program to reproduce bug
 *
 * @author David Linsin
 * @version 0.1
 * @since 0.1
 */
public class BugReproducer{
  Connection con;
  String importQuery = "SET FOREIGN_KEY_CHECKS = 1;DELETE FROM Category WHERE
id=\'0205342903\';SET FOREIGN_KEY_CHECKS = 0;INSERT INTO Category
VALUES(\'d0450f050a0dfd8e00e6da7bda3bb07e\',\'0205342903\',\'0000000000000000000
0000000000000\',\'\',\'0\');INSERT INTO Attribute
VALUES(\'d0450f050a0dfd8e00e6da7b00dfa3c5\',\'d0450f050a0dfd8e00e6da7bda3bb07e\'
,\'eType\',\'machine\',null);SET FOREIGN_KEY_CHECKS = 1;";
  String updateQuery = "SET FOREIGN_KEY_CHECKS = 1;DELETE FROM Attribute WHERE
foreignuuid=\'d0450f050a0dfd8e00e6da7bda3bb07e\' AND name=\'eType\';SET
FOREIGN_KEY_CHECKS = 0;INSERT INTO Attribute
VALUES(\'d0563ba70a0dfd8e01df43e22395b352\',\'d0450f050a0dfd8e00e6da7bda3bb07e\'
,\'eType\',\'machine\',null);SET FOREIGN_KEY_CHECKS = 1;";
  String bugQuery = "SELECT name,value FROM Attribute WHERE
foreignuuid=\'d0450f050a0dfd8e00e6da7bda3bb07e\'";
  
  /**
   * Sets up db connection and sets autocommit to false 
   * 
   * @throws Exception in case anything goes wrong during setup
   */
  public BugReproducer() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    // CONFIGURE YOUR DB PARAMETERS HERE
    this.con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/jboss2?useUnicode=true&
characterEncoding=UTF8&allowMultiQueries=true","root", "");
    this.con.setAutoCommit(false);
  }
  
  /**
   * Executes an update statment, closes statement
   * and commits transaction.
   * Closes connection. 
   * 
   * @throws Exception in case anything goes wrong
   */
  public void testBug() throws Exception {
    Statement stmt = this.con.createStatement();
    stmt.executeUpdate(this.importQuery);
    stmt.close();
    this.con.commit();
    this.con.close();
  }
  
  /**
   * Executes an update statement and a query, 
   * reads first row of ResultSet which randomly leads to an SQLException.
   * Closes statement and commits transaction.
   * Closes connection.
   * 
   * @throws Exception in case anything goes wrong 
   */
  public void testBug1() throws Exception {
    Statement stmt = this.con.createStatement();
    stmt.executeUpdate(this.updateQuery);
    ResultSet rs = stmt.executeQuery(this.bugQuery);
    // THIS IS WHERE THINGS GO WRONG
    rs.next();
    stmt.close();
    this.con.commit();
    this.con.close();
  }
  
  /**
   * Creates two instances of BugReproducer class sequentially and calls
testBug()
   * resp. testBug2(). This happens in loop which should lead to an SQLException
with 
   * error message "ResultSet is from UPDATE. No Data."
   * 
   * @param args unused
   */
  public static void main(String[] args) {
      int i = 0;
      try {
        for (i=0; i <= 5000; i++) {
          BugReproducer one = new BugReproducer();
          one.testBug();
          
          BugReproducer two = new BugReproducer();
          two.testBug1();
          
          if (i%250 == 0) {
            System.out.println("Count: " + i);
          }
        }
      } catch (Exception e) {
        System.out.println("Count: " + i);
        e.printStackTrace();
      }
  }
}

Schema:

CREATE TABLE Category (
    uuid char(32) NOT NULL,
    id character varying(254),
    parentuuid char(32),
    name character varying(254),
    sort integer,
    PRIMARY KEY (uuid),
    INDEX parentuuid (parentuuid),
    INDEX id (id),
    CONSTRAINT `parent_fk_1` FOREIGN KEY (`parentuuid`) REFERENCES `Category`
(`uuid`) ON DELETE CASCADE
) CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE Attribute (
    uuid char(32) NOT NULL,
    foreignuuid char(32),
    name character varying(254),
    value character varying(254),
    fid integer,
    PRIMARY KEY (uuid),
    INDEX foreignuuid (foreignuuid),
    INDEX name (name(16)),
    INDEX value (value(8)),
    CONSTRAINT `attribute_fk_1` FOREIGN KEY (`foreignuuid`) REFERENCES
`Category` (`uuid`) ON DELETE CASCADE
) CHARACTER SET utf8 ENGINE=InnoDB;

How to repeat:
I built a simple test case to reproduce this bug. I can reproduce it using mysql
4.1.10 and 5.0.24a on win2k with connector/j 3.1.13 and 5.0.3.

Just run the java program a couple of times and it'll eventually throw an
SQLException with the message: ResultSet is from UPDATE. No Data. Which should
not happen after executing a SELECT statement.

Java Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Simple test program to reproduce bug
 *
 * @author David Linsin
 * @version 0.1
 * @since 0.1
 */
public class BugReproducer{
  Connection con;
  String importQuery = "SET FOREIGN_KEY_CHECKS = 1;DELETE FROM Category WHERE
id=\'0205342903\';SET FOREIGN_KEY_CHECKS = 0;INSERT INTO Category
VALUES(\'d0450f050a0dfd8e00e6da7bda3bb07e\',\'0205342903\',\'0000000000000000000
0000000000000\',\'\',\'0\');INSERT INTO Attribute
VALUES(\'d0450f050a0dfd8e00e6da7b00dfa3c5\',\'d0450f050a0dfd8e00e6da7bda3bb07e\'
,\'eType\',\'machine\',null);SET FOREIGN_KEY_CHECKS = 1;";
  String updateQuery = "SET FOREIGN_KEY_CHECKS = 1;DELETE FROM Attribute WHERE
foreignuuid=\'d0450f050a0dfd8e00e6da7bda3bb07e\' AND name=\'eType\';SET
FOREIGN_KEY_CHECKS = 0;INSERT INTO Attribute
VALUES(\'d0563ba70a0dfd8e01df43e22395b352\',\'d0450f050a0dfd8e00e6da7bda3bb07e\'
,\'eType\',\'machine\',null);SET FOREIGN_KEY_CHECKS = 1;";
  String bugQuery = "SELECT name,value FROM Attribute WHERE
foreignuuid=\'d0450f050a0dfd8e00e6da7bda3bb07e\'";
  
  /**
   * Sets up db connection and sets autocommit to false 
   * 
   * @throws Exception in case anything goes wrong during setup
   */
  public BugReproducer() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    // CONFIGURE YOUR DB PARAMETERS HERE
    this.con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/jboss2?useUnicode=true&
characterEncoding=UTF8&allowMultiQueries=true","root", "");
    this.con.setAutoCommit(false);
  }
  
  /**
   * Executes an update statment, closes statement
   * and commits transaction.
   * Closes connection. 
   * 
   * @throws Exception in case anything goes wrong
   */
  public void testBug() throws Exception {
    Statement stmt = this.con.createStatement();
    stmt.executeUpdate(this.importQuery);
    stmt.close();
    this.con.commit();
    this.con.close();
  }
  
  /**
   * Executes an update statement and a query, 
   * reads first row of ResultSet which randomly leads to an SQLException.
   * Closes statement and commits transaction.
   * Closes connection.
   * 
   * @throws Exception in case anything goes wrong 
   */
  public void testBug1() throws Exception {
    Statement stmt = this.con.createStatement();
    stmt.executeUpdate(this.updateQuery);
    ResultSet rs = stmt.executeQuery(this.bugQuery);
    // THIS IS WHERE THINGS GO WRONG
    rs.next();
    stmt.close();
    this.con.commit();
    this.con.close();
  }
  
  /**
   * Creates two instances of BugReproducer class sequentially and calls
testBug()
   * resp. testBug2(). This happens in loop which should lead to an SQLException
with 
   * error message "ResultSet is from UPDATE. No Data."
   * 
   * @param args unused
   */
  public static void main(String[] args) {
      int i = 0;
      try {
        for (i=0; i <= 5000; i++) {
          BugReproducer one = new BugReproducer();
          one.testBug();
          
          BugReproducer two = new BugReproducer();
          two.testBug1();
          
          if (i%250 == 0) {
            System.out.println("Count: " + i);
          }
        }
      } catch (Exception e) {
        System.out.println("Count: " + i);
        e.printStackTrace();
      }
  }
}

Schema:

CREATE TABLE Category (
    uuid char(32) NOT NULL,
    id character varying(254),
    parentuuid char(32),
    name character varying(254),
    sort integer,
    PRIMARY KEY (uuid),
    INDEX parentuuid (parentuuid),
    INDEX id (id),
    CONSTRAINT `parent_fk_1` FOREIGN KEY (`parentuuid`) REFERENCES `Category`
(`uuid`) ON DELETE CASCADE
) CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE Attribute (
    uuid char(32) NOT NULL,
    foreignuuid char(32),
    name character varying(254),
    value character varying(254),
    fid integer,
    PRIMARY KEY (uuid),
    INDEX foreignuuid (foreignuuid),
    INDEX name (name(16)),
    INDEX value (value(8)),
    CONSTRAINT `attribute_fk_1` FOREIGN KEY (`foreignuuid`) REFERENCES
`Category` (`uuid`) ON DELETE CASCADE
) CHARACTER SET utf8 ENGINE=InnoDB;
[10 Jan 2007 18:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17880
[22 Feb 2007 14:10] MC Brown
A note has been added to the 5.0.5 changelog.