| Bug #103878 | Connector/J 8 : query with 'show xxx' will get exception when use cursor | ||
|---|---|---|---|
| Submitted: | 1 Jun 2021 13:01 | Modified: | 13 Oct 2021 0:05 |
| Reporter: | hong wang (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 8.0.21, 8.0.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Jun 2021 7:41]
MySQL Verification Team
Hello hong wang, Thank you for the report and test case. regards, Umesh
[2 Jun 2021 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 2021 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."
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.