Description:
When I use JDBC with cursor-based streaming to retrieve a set number of rows each time (useCursorFetch=true, FetchSize>0), and also set the JDBC parameter allowMultiQueries=true, if the SQL statement I execute contains a semicolon (;), then in JDBC 8.0.25, cursor-based reading will fail, and all the data will be directly cached on the client, which can easily cause a client OOM (Out of Memory) error. However, this issue does not exist in JDBC version 5.1.40. Please confirm if this is a bug.
How to repeat:
You are experiencing a problem where using JDBC 8.0.25 with cursor-based streaming (useCursorFetch=true, FetchSize>0) and multi-query (allowMultiQueries=true) settings, combined with semicolon-separated SQL statements, results in a failure of cursor-based reading. This causes all data to be cached client-side, leading to potential Out Of Memory (OOM) errors—a problem that does not occur in JDBC 5.1.40.
Here’s a Java code example to reproduce this issue:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCCursorFetchExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/your_database?useCursorFetch=true&allowMultiQueries=true";
String username = "your_username";
String password = "your_password";
int fetchSize = 10;
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
Statement statement = connection.createStatement()) {
statement.setFetchSize(fetchSize);
// A semicolon-separated SQL statement
String sql = "SELECT * FROM large_table1; SELECT * FROM large_table2;";
boolean isResultSet = statement.execute(sql);
while (isResultSet) {
try (ResultSet resultSet = statement.getResultSet()) {
while (resultSet.next()) {
// Process the rows here
System.out.println(resultSet.getString(1));
}
}
// Move to the next result
isResultSet = statement.getMoreResults();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
复制
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCCursorFetchExample {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/your_database?useCursorFetch=true&allowMultiQueries=true";
String username = "your_username";
String password = "your_password";
int fetchSize = 10;
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
Statement statement = connection.createStatement()) {
statement.setFetchSize(fetchSize);
// A semicolon-separated SQL statement
String sql = "SELECT * FROM large_table1; SELECT * FROM large_table2;";
boolean isResultSet = statement.execute(sql);
while (isResultSet) {
try (ResultSet resultSet = statement.getResultSet()) {
while (resultSet.next()) {
// Process the rows here
System.out.println(resultSet.getString(1));
}
}
// Move to the next result
isResultSet = statement.getMoreResults();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
java
Explanation:
Configuration:
JDBC URL includes useCursorFetch=true to enable cursor-based streaming.
allowMultiQueries=true allows multiple SQL queries in a single statement.
Settings:
statement.setFetchSize(fetchSize) aims to set the fetch size for cursor-based streaming.
SQL Statement:
The sql string contains a semicolon-separated multi-query.
Execution:
statement.execute(sql) executes the multi-query.
statement.getMoreResults() is used to handle multiple result sets.
However, in JDBC 8.0.25 with the described configuration, the cursor-based streaming might not work as expected, causing all data to be loaded into the client, thereby potentially leading to an OOM error. This behavior does not occur in JDBC 5.1.40, indicating it might be considered a bug.
Ensure the your_database, your_username, your_password, and table names (large_table1, large_table2) are replaced with actual values relevant to your setup.
Description: When I use JDBC with cursor-based streaming to retrieve a set number of rows each time (useCursorFetch=true, FetchSize>0), and also set the JDBC parameter allowMultiQueries=true, if the SQL statement I execute contains a semicolon (;), then in JDBC 8.0.25, cursor-based reading will fail, and all the data will be directly cached on the client, which can easily cause a client OOM (Out of Memory) error. However, this issue does not exist in JDBC version 5.1.40. Please confirm if this is a bug. How to repeat: You are experiencing a problem where using JDBC 8.0.25 with cursor-based streaming (useCursorFetch=true, FetchSize>0) and multi-query (allowMultiQueries=true) settings, combined with semicolon-separated SQL statements, results in a failure of cursor-based reading. This causes all data to be cached client-side, leading to potential Out Of Memory (OOM) errors—a problem that does not occur in JDBC 5.1.40. Here’s a Java code example to reproduce this issue: import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCCursorFetchExample { public static void main(String[] args) { String jdbcUrl = "jdbc:mysql://localhost:3306/your_database?useCursorFetch=true&allowMultiQueries=true"; String username = "your_username"; String password = "your_password"; int fetchSize = 10; try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password); Statement statement = connection.createStatement()) { statement.setFetchSize(fetchSize); // A semicolon-separated SQL statement String sql = "SELECT * FROM large_table1; SELECT * FROM large_table2;"; boolean isResultSet = statement.execute(sql); while (isResultSet) { try (ResultSet resultSet = statement.getResultSet()) { while (resultSet.next()) { // Process the rows here System.out.println(resultSet.getString(1)); } } // Move to the next result isResultSet = statement.getMoreResults(); } } catch (SQLException e) { e.printStackTrace(); } } } 复制 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCCursorFetchExample { public static void main(String[] args) { String jdbcUrl = "jdbc:mysql://localhost:3306/your_database?useCursorFetch=true&allowMultiQueries=true"; String username = "your_username"; String password = "your_password"; int fetchSize = 10; try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password); Statement statement = connection.createStatement()) { statement.setFetchSize(fetchSize); // A semicolon-separated SQL statement String sql = "SELECT * FROM large_table1; SELECT * FROM large_table2;"; boolean isResultSet = statement.execute(sql); while (isResultSet) { try (ResultSet resultSet = statement.getResultSet()) { while (resultSet.next()) { // Process the rows here System.out.println(resultSet.getString(1)); } } // Move to the next result isResultSet = statement.getMoreResults(); } } catch (SQLException e) { e.printStackTrace(); } } } java Explanation: Configuration: JDBC URL includes useCursorFetch=true to enable cursor-based streaming. allowMultiQueries=true allows multiple SQL queries in a single statement. Settings: statement.setFetchSize(fetchSize) aims to set the fetch size for cursor-based streaming. SQL Statement: The sql string contains a semicolon-separated multi-query. Execution: statement.execute(sql) executes the multi-query. statement.getMoreResults() is used to handle multiple result sets. However, in JDBC 8.0.25 with the described configuration, the cursor-based streaming might not work as expected, causing all data to be loaded into the client, thereby potentially leading to an OOM error. This behavior does not occur in JDBC 5.1.40, indicating it might be considered a bug. Ensure the your_database, your_username, your_password, and table names (large_table1, large_table2) are replaced with actual values relevant to your setup.