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.Connec tion;
import java.sql.Driver Manager;
import java.sql.Result Set;
import java.sql.Statem ent;

/**
* 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_CHE CKS = 1;DELETE FROM Category
WHERE
id=\'0205342903 \';SET FOREIGN_KEY_CHE CKS = 0;INSERT INTO Category
VALUES(\'d0450f 050a0dfd8e00e6d a7bda3bb07e\',\ '0205342903\',\ '00000000000000 00000
0000000000000\' ,\'\',\'0\');IN SERT INTO Attribute
VALUES(\'d0450f 050a0dfd8e00e6d a7b00dfa3c5\',\ 'd0450f050a0dfd 8e00e6da7bda3bb 07e\'
,\'eType\',\'ma chine\',null);S ET FOREIGN_KEY_CHE CKS = 1;";
String updateQuery = "SET FOREIGN_KEY_CHE CKS = 1;DELETE FROM Attribute
WHERE
foreignuuid=\'d 0450f050a0dfd8e 00e6da7bda3bb07 e\' AND name=\'eType\'; SET
FOREIGN_KEY_CHE CKS = 0;INSERT INTO Attribute
VALUES(\'d0563b a70a0dfd8e01df4 3e22395b352\',\ 'd0450f050a0dfd 8e00e6da7bda3bb 07e\'
,\'eType\',\'ma chine\',null);S ET FOREIGN_KEY_CHE CKS = 1;";
String bugQuery = "SELECT name,value FROM Attribute WHERE
foreignuuid=\'d 0450f050a0dfd8e 00e6da7bda3bb07 e\'";

/**
* 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.g etConnection("j dbc:mysql://localhost:3306/jboss2?useUnico de=true&
characterEncodi ng=UTF8&allowMu ltiQueries=true ","root", "");
this.con.setAut oCommit(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.create Statement();
stmt.executeUpd ate(this.import Query);
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.create Statement();
stmt.executeUpd ate(this.update Query);
ResultSet rs = stmt.executeQue ry(this.bugQuer y);
// 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.prin tln("Count: " + i);
}
}
} catch (Exception e) {
System.out.prin tln("Count: " + i);
e.printStackTra ce();
}
}
}

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 PreparedStateme nt, 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.resou rce.adapter.jdb c.BaseWrapperMa nagedConnection .prepareStateme nt(BaseWrapperM anagedConnectio n.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.resou rce.adapter.jdb c.WrappedPrepar edStatement.exe cuteQuery(Wrapp edPreparedState ment.java:296)
duration: 32 ms, connection-id: 0, statement-id: 44, resultset-id: 0,
message: SELECT name,value FROM Attribute WHERE
foreignuuid='c0 1b997f0a0dfd8e0 18a4aeffbfeb45f '

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