Bug #99262 Using cursor fetch does not work in some cases
Submitted: 15 Apr 2020 7:10 Modified: 15 Apr 2020 10:51
Reporter: Ulf Weiland Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[15 Apr 2020 7:10] Ulf Weiland
Description:
Running the SQL "show engines" results in
"java.sql.SQLException: The statement (1) has no open cursor".

I'm running with: mysql-connector-java-8.0.19 towards a server MySQL 8.0.19.

I have attached a Java program that exemplifies this. The program runs three three queries with and without "useCursorFetch". Running the "show engines" with useCursorFetch = true
fails. See the main for details.

The command "show engines" command is one example of a command that fails. There are other show commands that also fails. E.g. show create procedure.  

How to repeat:
package db;

import java.sql.*;
import java.util.Properties;

public class CursorFetchNotWorking {

private static void runQuery(String query, boolean cursorFetch) {
try {
String myDriver = "com.mysql.jdbc.Driver";
String myUrl = "jdbc:mysql://localhost:3307/";
Properties connectionProps = new Properties();
connectionProps.put("user", "dbvis");
connectionProps.put("password", "dbvis");
connectionProps.put("defaultFetchSize", "5000");
if (cursorFetch) {
connectionProps.put("useCursorFetch", "true");
}
Class.forName(myDriver);
Connection conn = DriverManager.getConnection(myUrl, connectionProps);
Statement st = conn.createStatement();
System.out.println("Connected:" + conn.getMetaData().getDriverVersion());
ResultSet rs = st.executeQuery(query);

while (rs.next()) {
String col1 = rs.getString(1);
System.out.println(col1);
}
st.close();
}
catch (Exception e) {
System.out.println("Got exception: " + e.getMessage());
e.printStackTrace();
}
}

public static void main(String[] args) {
System.out.println("=== Working");
runQuery("Select 1" , true);

System.out.println("=== NOT Working");
runQuery("show engines" , true);

System.out.println("=== Working");
runQuery("show engines" , false);
}
}
[15 Apr 2020 10:51] MySQL Verification Team
Hello Ulf Weiland,

Thank you for the report and test case.

regards,
Umesh