Description:
When Statement#closeOnCompletion() is called in advance, a query with streaming result set (i.e. Statement#setFetchSize(Integer.MIN_VALUE)) fails.
When result set streaming is enabled, the driver internally executes a query setting `net_write_timeout` in the following method.
com.mysql.cj.jdbc.StatementImpl.setupStreamingTimeout(JdbcConnection)
And this method subsequently calls the following method which closes the result set.
com.mysql.cj.jdbc.StatementImpl.executeSimpleNonQuery(JdbcConnection, String)
The problem is that this action (closing result set of the internal query) triggers `closeOnCompletion` action and the statement is closed before executing the user query.
Workaround :
Calling `closeOnCompletion()` after retrieving result set seems to be working [1].
```java
try (Connection conn = getConnection()) {
try (Statement stmt = conn.createStatement()) {
stmt.execute("drop table if exists test");
stmt.execute("create table test (id int, name varchar(8))");
stmt.execute("insert into test (id, name) values (1, 'U1')");
}
try (PreparedStatement pstmt = conn.prepareStatement("select * from test")) {
pstmt.setFetchSize(Integer.MIN_VALUE);
try (ResultSet rs = pstmt.executeQuery()) {
pstmt.closeOnCompletion();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
}
assertTrue(pstmt.isClosed());
}
}
```
Please let me know if you prefer a PR on GitHub.
[1] Spec ( https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/Statement.html#closeO...) ) says...
> a call to closeOnCompletion does effect both the subsequent execution of statements, and statements that currently have open, dependent, result sets.
How to repeat:
```java
try (Connection conn = getConnection()) {
try (Statement stmt = conn.createStatement()) {
stmt.execute("drop table if exists test");
stmt.execute("create table test (id int, name varchar(8))");
stmt.execute("insert into test (id, name) values (1, 'U1')");
}
try (PreparedStatement pstmt = conn.prepareStatement("select * from test")) {
pstmt.setFetchSize(Integer.MIN_VALUE);
pstmt.closeOnCompletion();
try (ResultSet rs = pstmt.executeQuery()) {
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
}
assertTrue(pstmt.isClosed());
}
}
```
Expected result : all assertions pass
Actual result : NPE (see below)
```
java.lang.NullPointerException
at com.mysql.cj.AbstractPreparedQuery.fillSendPacket(AbstractPreparedQuery.java:238)
at com.mysql.cj.AbstractPreparedQuery.fillSendPacket(AbstractPreparedQuery.java:220)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:980)
at test.MysqlStreamingResultSetTest.testGh1654(MysqlStreamingResultSetTest.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:132)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:124)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:74)
at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:202)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at java.util.ArrayList.forEach(ArrayList.java:1257)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at java.util.ArrayList.forEach(ArrayList.java:1257)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)
at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
```
Suggested fix:
It's not pretty, but temporarily disabling closeOnCompletion n `com.mysql.cj.jdbc.StatementImpl.setupStreamingTimeout(JdbcConnection)` seems to be working.
```java
protected void setupStreamingTimeout(JdbcConnection con) throws SQLException {
int netTimeoutForStreamingResults = this.session.getPropertySet().getIntegerProperty(PropertyKey.netTimeoutForStreamingResults).getValue();
if (createStreamingResultSet() && netTimeoutForStreamingResults > 0) {
boolean closeOnCompletionOriginal = this.closeOnCompletion;
this.closeOnCompletion = false;
executeSimpleNonQuery(con, "SET net_write_timeout=" + netTimeoutForStreamingResults);
this.closeOnCompletion = closeOnCompletionOriginal;
}
}
```
Description: When Statement#closeOnCompletion() is called in advance, a query with streaming result set (i.e. Statement#setFetchSize(Integer.MIN_VALUE)) fails. When result set streaming is enabled, the driver internally executes a query setting `net_write_timeout` in the following method. com.mysql.cj.jdbc.StatementImpl.setupStreamingTimeout(JdbcConnection) And this method subsequently calls the following method which closes the result set. com.mysql.cj.jdbc.StatementImpl.executeSimpleNonQuery(JdbcConnection, String) The problem is that this action (closing result set of the internal query) triggers `closeOnCompletion` action and the statement is closed before executing the user query. Workaround : Calling `closeOnCompletion()` after retrieving result set seems to be working [1]. ```java try (Connection conn = getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.execute("drop table if exists test"); stmt.execute("create table test (id int, name varchar(8))"); stmt.execute("insert into test (id, name) values (1, 'U1')"); } try (PreparedStatement pstmt = conn.prepareStatement("select * from test")) { pstmt.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs = pstmt.executeQuery()) { pstmt.closeOnCompletion(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); } assertTrue(pstmt.isClosed()); } } ``` Please let me know if you prefer a PR on GitHub. [1] Spec ( https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/Statement.html#closeO...) ) says... > a call to closeOnCompletion does effect both the subsequent execution of statements, and statements that currently have open, dependent, result sets. How to repeat: ```java try (Connection conn = getConnection()) { try (Statement stmt = conn.createStatement()) { stmt.execute("drop table if exists test"); stmt.execute("create table test (id int, name varchar(8))"); stmt.execute("insert into test (id, name) values (1, 'U1')"); } try (PreparedStatement pstmt = conn.prepareStatement("select * from test")) { pstmt.setFetchSize(Integer.MIN_VALUE); pstmt.closeOnCompletion(); try (ResultSet rs = pstmt.executeQuery()) { assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); } assertTrue(pstmt.isClosed()); } } ``` Expected result : all assertions pass Actual result : NPE (see below) ``` java.lang.NullPointerException at com.mysql.cj.AbstractPreparedQuery.fillSendPacket(AbstractPreparedQuery.java:238) at com.mysql.cj.AbstractPreparedQuery.fillSendPacket(AbstractPreparedQuery.java:220) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:980) at test.MysqlStreamingResultSetTest.testGh1654(MysqlStreamingResultSetTest.java:32) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675) at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125) at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:132) at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:124) at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:74) at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115) at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105) at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104) at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62) at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43) at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35) at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104) at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:202) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135) at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125) at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122) at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80) at java.util.ArrayList.forEach(ArrayList.java:1257) at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125) at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122) at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80) at java.util.ArrayList.forEach(ArrayList.java:1257) at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125) at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135) at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123) at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122) at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80) at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32) at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57) at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51) at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229) at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197) at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211) at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191) at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137) at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209) ``` Suggested fix: It's not pretty, but temporarily disabling closeOnCompletion n `com.mysql.cj.jdbc.StatementImpl.setupStreamingTimeout(JdbcConnection)` seems to be working. ```java protected void setupStreamingTimeout(JdbcConnection con) throws SQLException { int netTimeoutForStreamingResults = this.session.getPropertySet().getIntegerProperty(PropertyKey.netTimeoutForStreamingResults).getValue(); if (createStreamingResultSet() && netTimeoutForStreamingResults > 0) { boolean closeOnCompletionOriginal = this.closeOnCompletion; this.closeOnCompletion = false; executeSimpleNonQuery(con, "SET net_write_timeout=" + netTimeoutForStreamingResults); this.closeOnCompletion = closeOnCompletionOriginal; } } ```