Bug #102678 Connector/J 8 query with explain can not return ResultRow
Submitted: 22 Feb 2021 1:42 Modified: 21 Nov 2022 22:39
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.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[22 Feb 2021 1:42] hong wang
Description:
Connector/J 8 query with explain can not return ResultRow.But Connector/J 5 ok.

How to repeat:
### mysql-connector-java version 8.0.21

### DDL:

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

CREATE TABLE IF NOT EXISTS test.`t_a` (
  `fid` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`fid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test.`t_a` (name) VALUES
('01'),
('02'),
('03'),
('04'),
('05'),
('06'),
('07');

### Java

**you should repeat mysql host/user/pass.** 

import java.sql.*;
import java.util.HashSet;
import java.util.Set;

public class Repeat {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        String sql = "explain select * from t_a";

        try (Connection con = ConnUtil.getConn(); PreparedStatement stmt = con.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            int count = 0;
            while (rs.next()) {
                count++;
            }
            System.out.println("Execute sql:[" + sql + "] return " + count + " results.");
        }

        // use cursor can not work
        try (Connection con = ConnUtil.getConnWithUseCursor(); PreparedStatement stmt = con.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            int count = 0;
            while (rs.next()) {
                count++;
            }
            System.out.println("Execute sql:[" + sql + "] return " + count + " results.");
        }

    }

    public static Connection getConn() 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=GMT%2B8");
//        prop.add("useCursorFetch=true"); // not use cursor work success
//        prop.add("defaultFetchSize=1000");
        prop.add("useServerPrepStmts=true");
        prop.add("allowMultiQueries=true");
        prop.add("useUsageAdvisor=false");
        prop.add("zeroDateTimeBehavior=convertToNull");
        return DriverManager.getConnection("jdbc:mysql://192.168.56.102:3306/test?" + String.join("&", prop), "root", "pass");
    }

    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=GMT%2B8");
        prop.add("useCursorFetch=true");
        prop.add("defaultFetchSize=1000");
        prop.add("useServerPrepStmts=true");
        prop.add("allowMultiQueries=true");
        prop.add("useUsageAdvisor=false");
        prop.add("zeroDateTimeBehavior=convertToNull");
        return DriverManager.getConnection("jdbc:mysql://192.168.56.102:3306/test?" + String.join("&", prop), "root", "pass");
    }
}

### result

Execute sql:[explain select * from t_sec_user] return 1 results.
Execute sql:[explain select * from t_sec_user] return 0 results.

when use useServerPrepStmts=true and useCursorFetch=true,can not return explain qeury result.

Suggested fix:
Reference Connector/J 5 source code BinaryResultsetReader.java line:74 should add '&& stmt.resultField != null' condition.ServerStatement on prepare response does not return field info when execute sql like: explain select xx from xx.
[22 Feb 2021 7:33] MySQL Verification Team
Hello say mr,

Thank you for the report and test case.

regards,
Umesh
[21 Nov 2022 22:39] Filipe Silva
Related to Bug#103878.

Fixed in Connector/J 8.0.26.
[21 Dec 2022 18:16] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.32 changelog: 

"Prepared statements that contained EXPLAIN queries and used a cursor could not return any results."