Bug #25545 ssl causes "PROCEDURE can't return a result set in the given context"
Submitted: 11 Jan 2007 14:33 Modified: 13 Apr 2007 8:35
Reporter: Lenz Grimmer Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.0.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[11 Jan 2007 14:33] Lenz Grimmer
Description:
Copied from http://lists.mysql.com/java/9013 where it was initially reported on December 25th:

I've seen lots of complaints about this error but no solutions.
Here's what I would call at very least an interesting result.
I hope the developers read this.  (How can I cause them to do so?)

I'm using mysql-connector-java-5.0.4, server 5.0.18-log

First my source, straight out of the mysql-connector-java doc:
====
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.CallableStatement;
  import java.sql.Types;
  import java.sql.ResultSet;

  class test1 {
   public static void main(String[] args) throws Exception{
	   try {
	   Class.forName("com.mysql.jdbc.Driver");
	   Connection conn = DriverManager.getConnection(args[0]);
	   CallableStatement cStmt = 
		   conn.prepareCall("{call demoSp(?, ?)}");
	   cStmt.setString(1, "abcdefg");
	   cStmt.registerOutParameter(2, Types.INTEGER);
	   cStmt.setInt("inOutParam", 1);
	   boolean hadResults = cStmt.execute();
	   while (hadResults) {
		  ResultSet rs = cStmt.getResultSet();
		  System.out.println("next result set");
		  while(rs.next())
			  System.out.println(rs.getObject(1).toString());
		  hadResults = cStmt.getMoreResults();
	   }
	   int outputValue = cStmt.getInt(2);
	   System.out.println("output "+outputValue);
	   } catch (SQLException ex) {
		  ex.printStackTrace();
	   }
   }	 
  }
====

Notice that I use arg[0] for the url.
(sanitizing the command line - the original is much longer)
  # java test1 "jdbc:mysql://myhost/mydb?user=root"
  next result set
  121
  155
  225
  231
  233
  next result set
  abcdefg
  next result set
  zyxwabcdefg
  output 2
(I added a select to the example procedure from the doc to see that
it would work with an rs that contained more than one result.)

Now I try to use ssl:
  # java test1 "jdbc:mysql://myhost/mydb?user=root&useSSL=true&requireSSL=true"
java.sql.SQLException: PROCEDURE mydb.demoSp can't return a result set in the given
context
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:752)
	at test1.main(test1.java:18)

I get the same result without the requireSSL.
I can run the mysql command with ssl and execute the same queries:
  # mysql --host=myhost --user=root --ssl-ca=...
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 2229 to server version: 5.0.18-log
  mysql> \s
  --------------
  ...
  SSL:			Cipher in use is DHE-RSA-AES256-SHA
  ...
now just copying the commands I see in the log from the successful
run above:
  mysql> set @aaa=binary'1';
  Query OK, 0 rows affected (0.03 sec)

  mysql> call demoSP("asdfg",@aaa);
  ...
  5 rows in set (0.04 sec)

  +------------+
  | inputParam |
  +------------+
  | asdfg      |
  +------------+
  1 row in set (0.05 sec)

  +----------------------------+
  | CONCAT('zyxw', inputParam) |
  +----------------------------+
  | zyxwasdfg                  |
  +----------------------------+
  1 row in set (0.06 sec)

  Query OK, 0 rows affected (0.08 sec)

This shows that it's at least possible to issue the commands and
get back the right results, even using ssl.  Just off hand I have
trouble imagining what ssl could have to do with this problem.

How to repeat:
See description above.

Suggested fix:
(Pasted from http://lists.mysql.com/internals/34207)

This is a solution to the problem I recently described on
java@lists.mysql.com resulting in the error message

 PROCEDURE mydb.demoSp can't return a result set in the given context

All I've changed is src/com/mysql/jdbc/MysqlIO.java and only in 
three places that mention CLIENT_RESERVED.
I'm not sure the changes are right for 4.1.0, since I'm not sure
what's supposed to happen in that case.  But I think they're right
for anything from 4.1.1 onward.  How is this related to SSL and how
does it account for the error message above, you may wonder?
The flag allowing multiple results turns out to be in the upper
16 bits of the flag word, and before 4.1.1 that flag data was only
16 bits long.  Somehow the connector code was updated at 4.1.0 but
the ssl code (in lines > 3800) didn't get updated for >=4.1.1,
so that code was only sending 16 bits worth of flags.
CLIENT_MULTI_QUERIES and CLIENT_MULTI_RESULTS seem to be the only
flags so far in the upper 16 bits.

This patch allows me to to get my multiple results back while using
ssl.  I think I'm only actually using the change on line 3822.
I assume someone who understands this code better than I do will
take a closer look at the other two changes.

1191c1191,1192 
<                 if ((this.clientParam & CLIENT_RESERVED) != 0) {
---
>                 if ((this.clientParam & (CLIENT_PROTOCOL_41 | CLIENT_RESERVED))
> 		     != 0) {
3822c3823,3824
<         if ((this.clientParam & CLIENT_RESERVED) != 0) {
---
>         if ((this.clientParam & CLIENT_PROTOCOL_41 // CLIENT_RESERVED
> 	     ) != 0) {
3841c3843,3844
<             if ((this.clientParam & CLIENT_RESERVED) != 0) {
---
>             if ((this.clientParam & CLIENT_PROTOCOL_41 // CLIENT_RESERVED
> 		 ) != 0) {
[1 Mar 2007 21:19] 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/20949
[13 Apr 2007 8:35] MC Brown
A note has been added to the 5.0.6 changelog.