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) {
