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.
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.