Bug #96786 Retrieving streaming result set fails when closeOnCompletion is enabled
Submitted: 7 Sep 2019 16:09 Modified: 9 Sep 2019 7:06
Reporter: Iwao Abe (OCA) Email Updates:
Status: Verified 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] Umesh Shastry
Hello Iwao Abe,

Thank you for the report and test case.

regards,
Umesh