Description:
Connector/J 8 stmt setQueryTimeout can not work when use custor.
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
INSERT INTO test.test_001 (fid,ftext) VALUES
(1,'323232'),
(2,'fdsafdsa');
###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");
}
public void stmtExecuteQuery() {
String sql = "select * from temp_001 t where fid in (select sleep(5))";
Connection conn = null;
long start = System.currentTimeMillis();
try{
conn = getConnWithUseCursor();
Statement stmt = conn.createStatement();
stmt.setQueryTimeout(3);
start = System.currentTimeMillis();
stmt.executeQuery(sql);
} catch (SQLException | ClassNotFoundException ex) {
ex.printStackTrace();
}finally {
close(conn);
System.out.println("stmt execute query cost:" + (System.currentTimeMillis() - start));
}
}
public void preStmtExecuteQuery() {
String sql = "select * from temp_001 t where fid in (select sleep(5))";
Connection conn = null;
long start = System.currentTimeMillis();
try{
conn = getConnWithUseCursor();
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setQueryTimeout(3);
start = System.currentTimeMillis();
stmt.executeQuery();
} catch (SQLException | ClassNotFoundException ex) {
ex.printStackTrace();
}finally {
close(conn);
System.out.println("cost:" + (System.currentTimeMillis() - start));
}
}
private void close(AutoCloseable closeable) {
try{
if (closeable != null) {
closeable.close();
}
} catch (Exception ignored) {
}
}
public static void main(String[] args) {
stmtExecuteQuery();
preStmtExecuteQuery();
}
### RESULT
stmtExecuteQuery() can not get query timeout,but preStmtExecuteQuery() ok.
Suggested fix:
in MySQL Connector/j 8.0.21.
When use cursor,
com.mysql.cj.jdbc.StatementImpl#createResultSetUsingServerFetch create new PreparedStatement to replaced Statement to executeQuery,but not set queryTimeout.
Description: Connector/J 8 stmt setQueryTimeout can not work when use custor. 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 INSERT INTO test.test_001 (fid,ftext) VALUES (1,'323232'), (2,'fdsafdsa'); ###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"); } public void stmtExecuteQuery() { String sql = "select * from temp_001 t where fid in (select sleep(5))"; Connection conn = null; long start = System.currentTimeMillis(); try{ conn = getConnWithUseCursor(); Statement stmt = conn.createStatement(); stmt.setQueryTimeout(3); start = System.currentTimeMillis(); stmt.executeQuery(sql); } catch (SQLException | ClassNotFoundException ex) { ex.printStackTrace(); }finally { close(conn); System.out.println("stmt execute query cost:" + (System.currentTimeMillis() - start)); } } public void preStmtExecuteQuery() { String sql = "select * from temp_001 t where fid in (select sleep(5))"; Connection conn = null; long start = System.currentTimeMillis(); try{ conn = getConnWithUseCursor(); PreparedStatement stmt = conn.prepareStatement(sql); stmt.setQueryTimeout(3); start = System.currentTimeMillis(); stmt.executeQuery(); } catch (SQLException | ClassNotFoundException ex) { ex.printStackTrace(); }finally { close(conn); System.out.println("cost:" + (System.currentTimeMillis() - start)); } } private void close(AutoCloseable closeable) { try{ if (closeable != null) { closeable.close(); } } catch (Exception ignored) { } } public static void main(String[] args) { stmtExecuteQuery(); preStmtExecuteQuery(); } ### RESULT stmtExecuteQuery() can not get query timeout,but preStmtExecuteQuery() ok. Suggested fix: in MySQL Connector/j 8.0.21. When use cursor, com.mysql.cj.jdbc.StatementImpl#createResultSetUsingServerFetch create new PreparedStatement to replaced Statement to executeQuery,but not set queryTimeout.