[CALCITE-5099] ResultSetEnumerable.enumeratorBasedOnStatement cause the backend connection leak

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.30.0
• Component/s: avatica, core
• Labels: connection connection-pooling leak
• Assignee: itxiangkui
• Reporter: itxiangkui
• Created: 19/Apr/22 03:56
• Updated: 24/Jun/22 06:39

Description

We have extended JdbcSchema and JdbcCatalogSchema in order to connect to the back-end mysql database.
At the same time, HikariDataSource was used to replace the original BasicDataSource. When the connection pool leak detection of HikariDataSource was activated, a connection leak was detected.

java.lang.Exception: Apparent connection leak detected
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
	at org.apache.calcite.runtime.ResultSetEnumerable.enumeratorBasedOnStatement(ResultSetEnumerable.java:267)
	at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:257)
	at org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
	at org.apache.calcite.jdbc.CalciteMetaImpl.fetch(CalciteMetaImpl.java:674)
	at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:245)
	at org.apache.calcite.avatica.remote.Service$FetchRequest.accept(Service.java:1395)
	at org.apache.calcite.avatica.remote.Service$FetchRequest.accept(Service.java:1362)
	at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
	at org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:52)
	at org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:133)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(Thread.java:748)

Navigating to the snippet is

  private Enumerator<T> enumeratorBasedOnStatement() {
    Connection connection = null;
    Statement statement = null;
    try {
      connection = dataSource.getConnection();
      statement = connection.createStatement();
      setTimeoutIfPossible(statement);
      if (statement.execute(sql)) {
        final ResultSet resultSet = statement.getResultSet();
        statement = null;
        connection = null;
        return new ResultSetEnumerator<>(resultSet, rowBuilderFactory);
      } else {
        Integer updateCount = statement.getUpdateCount();
        //noinspection unchecked
        return Linq4j.singletonEnumerator((T) updateCount);
      }
    } catch (SQLException e) {
      throw Static.RESOURCE.exceptionWhilePerformingQueryOnJdbcSubSchema(sql)
          .ex(e);
    } finally {
      closeIfPossible(connection, statement);
    }
  }

obviously when you set:
statement = null;
connection = null;

if (statement.execute(sql)) {
        final ResultSet resultSet = statement.getResultSet();
        statement = null;
        connection = null;
        return new ResultSetEnumerator<>(resultSet, rowBuilderFactory);
      } 

the connection may leak,closeIfPossible(connection, statement); will close a null connection

Issue Links

• relates to: CALCITE-524 Connection timeout and keep-alive in remote JDBC driver
• relates to: CALCITE-1739 backend jdbc connection does not returned to connection pool after execute a query.
• relates to: CALCITE-4676 Avatica client leaks TCP connections
• relates to: CALCITE-636 Connection isolation for Avatica clients
• relates to: CALCITE-637 Implement Avatica close connection RPC
• relates to: CALCITE-640 Avatica server should expire stale connections/statements