Bug #103878 Connector/J 8 : query with 'show xxx' will get exception when use cursor
Submitted: 1 Jun 13:01 Modified: 13 Oct 0:05
Reporter: hong wang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.21, 8.0.25 OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 13:01] hong wang
Description:
Query with 'show xxx'  will get exception when use cursor

How to repeat:
###DDL

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE TABLE `test_001` (
  `fid` bigint(20) DEFAULT NULL,
  `ftext` longtext COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

###JAVA CODE

public static Connection getConnWithUseCursor() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Set<String> prop = new HashSet<>();
        prop.add("useUnicode=true");
        prop.add("characterEncoding=UTF-8");
        prop.add("serverTimezone=Asia/Shanghai");
        prop.add("useCursorFetch=true");
        prop.add("defaultFetchSize=100");
        prop.add("useServerPrepStmts=true");
        prop.add("allowMultiQueries=true");
        prop.add("useUsageAdvisor=false");
        return DriverManager.getConnection("jdbc:mysql://192.168.56.102:3306/test?" + String.join("&", prop), "root", "123456");
}

private void close(AutoCloseable closeable) {
    try{
        if (closeable != null) {
            closeable.close();
        }
    } catch (Exception ignored) {
    }
}

public static void main(String[] args) {
    String sql = "show create table test_001";
        try (Connection conn = ConnUtil.getConnWithUseCursor()) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
        }
}

### RESULT

it will cause exception like : java.sql.SQLException: The statement (1) has no open cursor.

Suggested fix:
sql like : 'explain' ,'show xxx ' will exception or can not work.

if sql start with 'explain','show xxx' should be used ClientPreparedStatement.
[2 Jun 7:41] MySQL Verification Team
Hello hong wang,

Thank you for the report and test case.

regards,
Umesh
[2 Jun 18:10] OCA Admin
Contribution submitted via Github - Fix for Bug#103878 
(*) Contribution by hong wang (Github jincarry567, mysql-connector-j/pull/64#issuecomment-853059470): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_659918485.txt (text/plain), 3.03 KiB.

[13 Oct 0:05] Daniel So
Posted by developer:
 
Added the following entry to the C/J 8.0.27 changelog: 

"When using cursor-based fetching (useCursorFetch=true), SHOW and EXPLAIN statements failed with a SQLException."