Bug #96786 Retrieving streaming result set fails when closeOnCompletion is enabled
Submitted: 7 Sep 2019 16:09 Modified: 18 Dec 2024 23:57
Reporter: Iwao Abe (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: Streaming

[7 Sep 2019 16:09] Iwao Abe
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;
    }
}
```
[9 Sep 2019 7:06] MySQL Verification Team
Hello Iwao Abe,

Thank you for the report and test case.

regards,
Umesh
[18 Dec 2024 23:57] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 9.1.2 changelog: 

"If closeOnCompletion() was enabled on a PreparedStatement, after enabling a streaming ResultSet (for example by calling setFetchSize()), the ResultSet was closed immediately even before any query executions. This patch eliminated the immature closing of the ResultSet, so that queries can be run normally."