Make sure we close the connection and don't leak it in java sources.

[tuliren]

Summary

• There seems to be connection leaks in some of the database connector.
• We should verify this by adding a leak detection in acceptance test, and fix them.

Description

While investigating #9744, I found two phenomena:

1. When a Redshift table is empty, reading it in a streaming does not seem to ever reach the onClose method of the stream, which is supposed to close the connection and now it is not executed, leaving the connection open.
2. The StreamingJdbcDatabase may have connection leak. It calls getConnection from the data source, and it is left for the user to close those connection. When the 1) issue happens, the connection won't get closed.

Update

The issue is not from Redshift when the table is empty. It is connection leak. A Java Stream is not automatically closed. The onClose callback won't be triggered unless the Stream is manually closed. Many of the JdbcDatabase methods return a Stream, which needs to be manually closed or put in a try block. It is not always the case currently, resulting in connection leaks.

Reference

• https://blog.jooq.org/how-to-prevent-jdbc-resource-leaks-with-jdbc-and-with-jooq/
• https://vladmihalcea.com/the-best-way-to-detect-database-connection-leaks/

[grishick]

Support has been reporting issues with JDBC connectors during connection set up that may point to connection leaks as described in this blog post: https://mkyong.com/jdbc/hikaripool-1-connection-is-not-available-request-timed-out-after-30002ms/

[bleonard]

let's catch exceptions and close the stream if it's still open.
ideally, we can come up with a test to prevent regressions.
try with resources?