| Bug #15500 | ResultSet is from UPDATE. No Data | ||
|---|---|---|---|
| Submitted: | 5 Dec 2005 20:32 | Modified: | 1 Jul 2009 21:12 |
| Reporter: | Jayant Deshpande | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | OS: | Linux (Linux) | |
| Assigned to: | CPU Architecture: | Any | |
[5 Dec 2005 20:32]
Jayant Deshpande
[5 Dec 2005 21:09]
Vasily Kishkin
Could you please provide some test case and definition of procedure ?
[5 Dec 2005 21:16]
Jayant Deshpande
CallableStatement queryCS = null;
queryCS = conn.prepareCall("{call " + procName}");
queryRS = queryCS.executeQuery();
while (queryRS.next()) { /// This statment throws an exception
xxxx
xxx
}
[5 Dec 2005 22:09]
Jayant Deshpande
Some more code, used for creating datasource and connection object :
private static DataSource lookupDataSource(String datasourceName) throws NamingException {
Hashtable environment = null;
InitialContext initialContext = null;
DataSource dataSource = null;
environment = new Hashtable();
environment.put(InitialContext.INITIAL_CONTEXT_FACTORY, System.getProperty("datasource_initial_context_factory"));
environment.put(InitialContext.PROVIDER_URL, System.getProperty("datasource_provider_urls"));
initialContext = new InitialContext(environment);
try {
dataSource = (DataSource) initialContext.lookup("java:jdbc/"+datasourceName);
} finally {
initialContext.close();
}
return dataSource;
}
private Connection getConnection() {
Connection connection = null;
DataSource ds = null;
try {
ds = getReportDatasource();
connection = ds.getConnection();
} catch (SQLException sqle) {
LoggerUtil.log(oasLogger, LoggerConstants.ERROR, "DataGenerator.class", "getCustomQueryData()", sqle.getMessage());
}
return connection;
}
[6 Dec 2005 9:38]
Aleksey Kishkin
testcase
Attachment: Bug15500.java (text/x-java), 1.35 KiB.
[6 Dec 2005 10:09]
Aleksey Kishkin
Jayant, If procedure doesn't return any recordset, (for example, in case of manual error handling), java program returns very this exception (I attached testcase for experiments.) Could you check if your procedure returns some recordset?
[6 Dec 2005 15:59]
Jayant Deshpande
Aleksey Kishkin, What could be the solution for such issues? do we have to handle them in procedure ? If so please provide some information.
[14 Dec 2005 22:10]
Vasily Kishkin
I need definition of your procedure for reproducing the bug. Could you please write here ?
[15 Jan 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[20 Jan 2006 11:09]
Srivats Chandrasekaran
I am using mySQL connector/J and i am getting this error, my store proc doesnt return any resultset and the funny the same store procedure runs for certain records and throws this exception for others and the functionality isnt all that complicated either.
DROP PROCEDURE IF EXISTS nb_nol_update //
CREATE PROCEDURE nb_nol_update(
IN nb_factor_date_gb CHAR,
IN nb_pool_number_gb CHAR,
IN nb_current_nol_gb CHAR,
IN nb_fileName_gb CHAR
)
BEGIN
DECLARE nb_curr_upb_tmp DOUBLE;
DECLARE nb_result INT DEFAULT 1;
DECLARE MY_ERROR CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR MY_ERROR SET nb_result = 0;
IF nb_fileName_gb = 'FhlmcMonthlyBreakout' THEN
select nb_current_unpaid_balance into nb_curr_upb_tmp from nb_pool_info where nb_pool_number = nb_pool_number_gb and nb_factor_date = nb_factor_date_gb;
update nb_pool_info
set
nb_nol = nb_current_nol_gb,
nb_loan_size = ( nb_curr_upb_tmp / nb_current_nol_gb )
where nb_pool_number = nb_pool_number_gb and
nb_factor_date = nb_factor_date_gb;
ELSE
select nb_current_unpaid_balance into nb_curr_upb_tmp from nb_pool_info where nb_pool_number = nb_pool_number_gb and nb_factor_date = nb_factor_date_gb;
select nb_curr_upb_tmp;
select nb_current_nol_gb;
update nb_pool_info
set
nb_nol = nb_current_nol_gb,
nb_loan_size = ( nb_curr_upb_tmp / nb_current_nol_gb )
where nb_pool_number = nb_pool_number_gb and
nb_factor_date = nb_factor_date_gb;
update nb_pool_master
set
nb_original_nol = nb_current_nol_gb,
nb_original_loan_size = ( nb_curr_upb_tmp / nb_current_nol_gb )
where nb_pool_number = nb_pool_number_gb;
END IF;
END;
and this is the JAVA method
private void _updateLoanCount(String loanCount_,String factorDate_, String poolNumber_, String fileName_,Connection dbConnection_){
StringBuffer queryBuf = new StringBuffer();
try{
PreparedStatement updateStmt = null;
queryBuf.append("call nb_nol_update('");
queryBuf.append(factorDate_);
queryBuf.append("','");
queryBuf.append(poolNumber_);
queryBuf.append("','");
queryBuf.append(loanCount_);
queryBuf.append("','");
queryBuf.append(fileName_);
queryBuf.append("'");
System.out.println("THE QUERY "+queryBuf.toString());
updateStmt = dbConnection_.prepareStatement(queryBuf.toString());
updateStmt.executeQuery();
}catch(Exception exc_){
exc_.printStackTrace();
}
}
[20 Jan 2006 11:20]
Jayant Deshpande
Hi Please check all the parameters you are passing for the data and the data type. It may look funny but this is the one reason when you get such error. Jayant
[20 Jan 2006 11:47]
Srivats Chandrasekaran
This is the stacktrace that i am getting java.sql.SQLException: ResultSet is from UPDATE. No Data. at com.mysql.jdbc.ResultSet.next(ResultSet.java:6091) at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1250) at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3640) at com.newbreak.factors.NBRecordHandler.updateDB(NBRecordHandler.java:272) at com.newbreak.factors.NBFhlmcBreakoutHandler._extractSequenceRecords(NBFhlmcBreakoutHandler.java:175) at com.newbreak.factors.NBFhlmcBreakoutHandler.updateRecord(NBFhlmcBreakoutHandler.java:108) at com.newbreak.factors.NBRecordProccessor.processRecord(NBRecordProccessor.java:102) at com.newbreak.factors.NBFactorUpdateTestApp.main(NBFactorUpdateTestApp.java:10) java.lang.NullPointerException at com.newbreak.factors.NBRecordHandler.updateDB(NBRecordHandler.java:330) at com.newbreak.factors.NBFhlmcBreakoutHandler._extractSequenceRecords(NBFhlmcBreakoutHandler.java:175) at com.newbreak.factors.NBFhlmcBreakoutHandler.updateRecord(NBFhlmcBreakoutHandler.java:108) at com.newbreak.factors.NBRecordProccessor.processRecord(NBRecordProccessor.java:102) at com.newbreak.factors.NBFactorUpdateTestApp.main(NBFactorUpdateTestApp.java:10)
[20 Jan 2006 16:45]
Mark Matthews
The JDBC specification says that you can't call CallableStatement.executeQuery() on stored procedures that might not return result sets, or _don't_ return result sets. You need to use execute() in these cases, or executeUpdate() for those that _definitely_ don't return a result set. What happens if you use either of those methods?
[30 Jan 2006 6:38]
Srivats Chandrasekaran
I am getting the same exception when i try to use executeUpdate() and execute() methods, but what i dont understand is , its happening in a completely random fashion. I execute this query some 10 to 15 times during the process albiet for different sets of data and its throwing exception in some casses and not for others.
[21 Feb 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[28 Sep 2006 11:16]
David Linsin
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\',\'00000000000000000000000000000000\',\'\',\'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;
[8 Dec 2006 10:20]
Dan Costelloe
If David Linsin's submission above counts as sufficient feedback for this bug, then can we get the status updated? cheers.
[11 Dec 2006 8:56]
David Linsin
I agree that at least the status of this bug is changed, since i submitted feedback!
[11 Dec 2006 9:25]
Tonci Grgin
Hi David. What we are really asking is what happens if you replace
ResultSet rs = stmt.executeQuery(this.bugQuery);
// THIS IS WHERE THINGS GO WRONG
with
ResultSet rs = stmt.execute(this.bugQuery);
?
[11 Dec 2006 17:42]
David Linsin
If I'm running my test case without retrieving an ResultSet using: stmt.executeQuery(this.bugQuery); I cannot reproduce the bug, same with calling: stmt.execute(this.bugQuery); The bug only appears when the ResultSet instance is being accessed by calling ResultSet.next().
[12 Dec 2006 12:47]
Tonci Grgin
So David, when called like our connector team leader, Mr. Mark Matthews asked, there is no error. I'm setting this report to "Not a bug".
[12 Dec 2006 13:05]
David Linsin
The java.sql.Statement interface doesn't provide a method called "execute" which returns a java.sql.ResultSet instance. So calling one of the "execut" methods on a Statement instance is not a valid "workaround", since people might need a ResultSet when executing a select query. There is definitely a bug when accessing the ResultSet instance, it's not about calling "execute" or "executeQuery". Setting this bug entry to "Not a Bug" based on simply changing the calling method, with no proper analysis, is not really helpful.
[12 Dec 2006 14:34]
Mark Matthews
Tonci closed this bug a little prematurely, but I can see how he got confused. The original bug was with stored procedures, and the request to use .execute() and not executeUpdate() is valid in that case. Your bug is different, but has a similar result (in the exception). It really should be a different bug, so I've moved it to http://bugs.mysql.com/bug.php?id=25009 (anyone affected by the multiple-statement, throws this kind of exception scenario should subscribe to updates on the bug).
[1 Jul 2009 21:12]
Mark Matthews
Going back to the original bug, if one uses CallableStatement.exceuteQuery() and no result set is returned by the procedure, you get this exception. This is expected behavior. If you need to invoke stored procedures that may or may not return a result set, then use CallableStatement.execute(), check the return value, then based on that call getResultSet().
