ResultSet is from UPDATE. No Data.

[David Linsin]

I created a simple test case to reproduce the problem (also check Bug
#15500):

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\',\'0205 342903\',\'0000000000000000000
0000000000000\',\'\',\'0\');INSERT INTO Attribute
VALUES(\'d0450f050a0dfd8e00e6da7b00dfa3c5\',\'d045 0f050a0dfd8e00e6da7bda3bb07e\'
,\'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\',\'d045 0f050a0dfd8e00e6da7bda3bb07e\'
,\'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","ro ot", "");
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;
--- In reply to ---
Hey there,

I have a probleme executing a query using a PreparedStatement, it
results in a SQLException with the message "ResultSet is from UPDATE.
No Data.". Below I added the profiler log which indicates that the
statement-id changed between [PREPARE] and [QUERY]. Shouldn't it be
[PREPARE] and [EXECUTE] anyway?

INFO: Profiler Event: [PREPARE] at
org.jboss.resource.adapter.jdbc.BaseWrapperManaged Connection.prepareStatement(BaseWrapperManagedConn ection.java:360)
duration: 62 ms, connection-id: 0, statement-id: 43, resultset-id: -1,
message: SELECT name,value FROM Attribute WHERE foreignuuid=?
2006-09-18 10:47:30,176 INFO [STDOUT] (Timer-4) 18.09.2006 10:47:30 N/A
N/A
INFO: Profiler Event: [QUERY] at
org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(WrappedPreparedStatement.java: 296)
duration: 32 ms, connection-id: 0, statement-id: 44, resultset-id: 0,
message: SELECT name,value FROM Attribute WHERE
foreignuuid='c01b997f0a0dfd8e018a4aeffbfeb45f'

I'm using JDK 1.5.0_06, mysql-connector-java-3.1.13-bin.jar with JBoss
4.0.2 and mysql 4.1.10 on W2K. I'm using JNDI to get a DataSource and
retrieve a Connection object.

Can anybody think of reason why the statement-id changes? Is this
change the reason for the SQLException mentioned above?

Thanks

David