Exception thrown while establishing a new JDBC connection after Thread.stop

[MohamedKamarudeen]

Scenario:

1. Establish a new Snowflake JDBC connection
2. Execute any query by using the PreparedStatement
3. Do a Thread.stop() call to the query execution thread while query execution is in progress
4. After Thread.stop() has successfully completed, try establishing a new JDBC connection, below exception is thrown

Exception Message:
Message: Invalid state: Connection pool shut down. A potential cause is closing of a connection when a query is still running..

Exception Stack Trace:

Caused by: java.lang.IllegalStateException: Connection pool shut down
	at net.snowflake.client.jdbc.internal.apache.http.util.Asserts.check(Asserts.java:34) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.conn.PoolingHttpClientConnectionManager.requestConnection(PoolingHttpClientConnectionManager.java:269) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:176) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:186) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.RedirectExec.execute(RedirectExec.java:110) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:108) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.RestRequest.execute(RestRequest.java:160) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:665) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:610) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:552) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.core.SessionUtil.newSession(SessionUtil.java:588) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.core.SessionUtil.openSession(SessionUtil.java:286) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.core.SFSession.open(SFSession.java:457) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initialize(DefaultSFConnectionHandler.java:104) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initializeConnection(DefaultSFConnectionHandler.java:79) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.SnowflakeConnectionV1.initConnectionWithImpl(SnowflakeConnectionV1.java:116) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.SnowflakeConnectionV1.<init>(SnowflakeConnectionV1.java:96) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:176) ~[snowflake-ingest-sdk-1.0.2-beta.5.jar:?]
	at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[?:1.8.0_241]
	at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[?:1.8.0_241]

Not able to establish a new connection once this exception has occurred.

Possible cause :
Internally a field isShutDown is maintained by net.snowflake.client.jdbc.internal.apache.http.impl.conn.PoolingHttpClientConnectionManager which might be set to true when Thread.stop() is called. On subsequent calls as this flag is set to true new connection seems to be rejected by the above exception, as net.snowflake.client.jdbc.internal.apache.http.impl.conn.PoolingHttpClientConnectionManager is maintained as SingletonObject (static).

Able to establish a new JDBC connection after this Java application is killed and restarted. Is there any other way to establish a new connection without kill and restart?

[rajeshfss]

Does anyone know if the latest jdbc jar addresses this issue ?

[sfc-gh-wfateem]

@rajeshfss @MohamedKamarudeen This should be directed to snowflake-jdbc instead, however, your assessment is exactly right. When you called Thread.stop() you basically shut down the Apache HTTP client's connection pool, which really should never happen. Ideally, you shouldn't really call the Thread.stop() method.
Having said that, there might be something we'll want to look at more closely to see if we need to first check if the HTTP connection pool is initialized first before making an HTTP request.
If you're using plain Java JDBC code, what's the purpose of the Ingest SDK library here in your application?

[MohamedKamarudeen]

@sfc-gh-wfateem - Thanks for the response. Logged a ticket to snowflake-jdbc repo.
LINK: snowflakedb/snowflake-jdbc#1461

Does any versions of the JDBC driver that address this issue?

Noticed that the Thread.interrupt() call takes more than a minute when executing the query. Is there any way to reduce the long interrupt time?

[sfc-gh-wfateem]

@MohamedKamarudeen I'll close this for now and we can carry on with the conversation in snowflake-jdbc#1461