| Bug #38554 | Stored procedures unreliable/unusable with combination with result set streaming | ||
|---|---|---|---|
| Submitted: | 5 Aug 2008 6:51 | Modified: | 3 May 2010 20:51 |
| Reporter: | P Eger | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.0.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Aug 2008 6:56]
P Eger
Note that the problem only seems to occur with an empty result set, inserting a row lets the unit test pass. mysql> insert into t1(c0) values (1); Query OK, 1 row affected (0.00 sec) >> run test code (SUCCESS) mysql> truncate table t1; Query OK, 1 row affected (0.00 sec) >> run test code (FAILURE w/exception)
[6 Aug 2008 19:45]
P Eger
happens on both linux and windows x64, changing to OS: Any.
[7 Aug 2008 17:05]
P Eger
Occurs on 5.1.26-rc also
[13 Aug 2008 15:04]
Tonci Grgin
Hi Eger and thanks for your report.
What happens if you use CallableStatements? Can you retest with:
CallableStatement callable = this.conn.prepareCall("{call test1(?)}");?
[13 Aug 2008 15:25]
Tonci Grgin
Test case, using SVN repository sources and our test fw
Attachment: TestBug38554.java (text/x-java), 2.24 KiB.
[13 Aug 2008 15:26]
Tonci Grgin
I do not see bug here, please review my test case and output/log: 080813 17:19:20 2 Connect root@localhost on test 2 Query /* @MYSQL_CJ_FULL_PROD_NAME@ ( Revision: @MYSQL_CJ_REVISION@ ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 2 Query SHOW COLLATION 2 Query SET character_set_results = NULL 2 Query SET autocommit=1 2 Query SET sql_mode='STRICT_TRANS_TABLES' 2 Query SELECT VERSION() 2 Query DROP TABLE IF EXISTS test38554 2 Query CREATE TABLE test38554 ( c0 INT NOT NULL PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=latin1 2 Query DROP PROCEDURE IF EXISTS test_function38554 080813 17:19:21 2 Query CREATE PROCEDURE test_function38554 (p0 INT)BEGIN SELECT * FROM test38554 WHERE c0 = p0;END 2 Query USE `test` 2 Query SELECT DATABASE() 2 Query USE `test` 2 Query SHOW CREATE PROCEDURE `test`.`test_function38554` 2 Query CALL test_function38554(2) 2 Quit .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. Connected to 5.0.68-pb10-log java.vm.version : 1.5.0_12-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_12-b04 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Time: 0,469 OK (1 test)
[13 Aug 2008 16:52]
P Eger
Apologies, it appears Connector/J 5.1.6 is *not* affected. Connector/J 5.0.8 is the effected version, with which I am able to reproduce the error reliably. Also, tried using CallableStatement in test code, bug still occurs.
[13 Aug 2008 17:00]
Tonci Grgin
Hi again. I am unable to reproduce the problem with attached test case on latest c/J 5.0 sources too... .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. java.vm.version : 1.5.0_12-b04 java.vm.vendor : Sun Microsystems Inc.Connected to 5.0.68-pb10-log java.runtime.version : 1.5.0_12-b04 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Time: 1,297 OK (1 test) I suggest to either build your own c/J driver from sources or take latest snapshot and retest.
[13 Aug 2008 18:51]
P Eger
mysql-connector-java-5.0-nightly-20080801-bin.jar shows the same problem as 5.0.8, this is the most recent snapshot listed on http://downloads.mysql.com/snapshots.php#connector-j. I would be happy to test HEAD, but I could not find instructions anywhere on how to access your VCS repository?
[14 Aug 2008 7:28]
Todd Farmer
Will attach updated test case. Able to repeat with fetch size = Integer.MIN_VALUE, related to the following code in MysqlIO.readAllResults():
boolean serverHasMoreResults = (this.serverStatus &
SERVER_MORE_RESULTS_EXISTS) != 0;
Comparing the 5.1 and 5.0 sources, it appears that handling of multiple result sets with streaming has been added (it is labeled with TODO in 5.0 sources).
5.0 version:
//
// TODO: We need to support streaming of multiple result sets
//
if (serverHasMoreResults && streamResults) {
clearInputStream();
throw SQLError.createSQLException(Messages.getString("MysqlIO.23"), //$NON-NLS-1$
SQLError.SQL_STATE_DRIVER_NOT_CAPABLE);
}
5.1 version:
if (serverHasMoreResults && streamResults) {
//clearInputStream();
//
//throw SQLError.createSQLException(Messages.getString("MysqlIO.23"), //$NON-NLS-1$
//SQLError.SQL_STATE_DRIVER_NOT_CAPABLE);
if (topLevelResultSet.getUpdateCount() != -1) {
tackOnMoreStreamingResults(topLevelResultSet);
}
reclaimLargeReusablePacket();
return topLevelResultSet;
}
I'm unsure whether this should be ported back to 5.0 or simply documented as a limitation. I'll assess the complexity of porting back the 5.1 code, but we should also solicit Mark's feedback.
[14 Aug 2008 7:29]
Todd Farmer
Revised test case
Attachment: TestBug38554.java (text/x-java), 2.35 KiB.
[14 Aug 2008 16:31]
P Eger
Glad you could reproduce. I'm not sure what it looks like at a driver/protocol level, but it definitely seems wrong that a sproc that only does a simple "select ..." could fail because of this multiple result set limitation. As it stands, RS streaming looks to be unusable with sprocs on 5.0.X. We're able to update to 5.1.X so this is okay for us (though possibly not others).
[3 May 2010 20:51]
Todd Farmer
Won't fix in C/J 5.0, already fixed in C/J 5.1.
Description: For certain types of stored procedures, cannot enable result set streaming via setFetchSize(Integer.MIN_VALUE), doing so results in the following exception reliably when the sproc attempts to execute. You can see from test code that multiple result sets are definitely not used. Tried with Connector/J 5.1.6 & 5.0.8, mysql 5.0.66a enterprise. java.sql.SQLException: Can not use streaming results with multiple result statements at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1470) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1787) at com.mysql.jdbc.Connection.execSQL(Connection.java:3283) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467) at com.mysql.jdbc.CallableStatement.executeQuery(CallableStatement.java:794) at TestSprocMRS.test(TestSprocMRS.java:27) How to repeat: Create table/procedure as follows: ---------------------------------------------- mysql> create table t1(c0 int); Query OK, 0 rows affected (0.02 sec) mysql> create procedure test1(p0 int) select * from t1 where c0=p0; Query OK, 0 rows affected (0.00 sec) Then execute following code: ---------------------------------------------- import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import org.junit.Test; public class TestSprocMRS { @Test public void testSproc() throws Exception { com.mysql.jdbc.jdbc2.optional.MysqlDataSource nds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource(); nds.setUser("user"); nds.setPassword("password"); nds.setServerName("localhost"); nds.setPortNumber(3306); nds.setDatabaseName("testdb"); Connection conn = nds.getConnection(); try { PreparedStatement ps = conn.prepareCall("{call test1(?)}",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(Integer.MIN_VALUE); ps.setInt(1, 1); ResultSet rs = ps.executeQuery(); //<-- EXCEPTION THROWN HERE?? while(rs.next()) ; rs.close(); ps.close(); } finally { conn.close(); } } } Suggested fix: 1) Disable result set streaming, or 2) Don't use stored procedures. Either of course are not desirable (or possible) in our case.