CloseAsync won't close SFSession Causing Authentication Exception due to IDLE Connection

[omamoo]

Issue description

We are using snowflake driver in one of our components which can execute a query against Snowflake once a 1-6 hours. Since there is a 4 hours limitation to a connection, if the query sent after over than 4 hours we are closing and opening the connection again, to refresh the authentication token (worked flawless for version 2.0.11).

code example:

            public async Task ExecuteAsync(
                string databaseName,
                CommandDefinition commandDefinition)
            {
                if (_activityTime.OlderThan(1.Hours()))
                {
                    await _snowflakeDbConnection.CloseAsync(commandDefinition.CancellationToken);
                    await OpenAsync(commandDefinition.CancellationToken);
                }

                if (_databaseName != databaseName)
                {
                    await _snowflakeDbConnection.ExecuteAsync(
                        new CommandDefinition(
                            $"USE DATABASE {databaseName}",
                            commandTimeout: 10,
                            cancellationToken: commandDefinition.CancellationToken));
                    _databaseName = databaseName;
                }

                await _snowflakeDbConnection.ExecuteAsync(commandDefinition);
                _activityTime = DateTime.UtcNow;
            }

We upgraded to 2.0.15 and saw that the connection using connection pooling that cannot be disabled, also the pool size cannot be set. and when executing query after over than 4 hour we are closing the connection, but since the pool is not full the connection only appended to the pool of the connections and the SFSession is not closing and therefore, after 4 hours we are receiving auth exception since the connection was IDLE.

Example code

Run this sample of code once and than after > 4 hours

public async Task ExecuteAsync(
                string databaseName,
                CommandDefinition commandDefinition)
            {
                if (_activityTime.OlderThan(1.Hours()))
                {
                    await _snowflakeDbConnection.CloseAsync(commandDefinition.CancellationToken);
                    await OpenAsync(commandDefinition.CancellationToken);
                }

                if (_databaseName != databaseName)
                {
                    await _snowflakeDbConnection.ExecuteAsync(
                        new CommandDefinition(
                            $"USE DATABASE {databaseName}",
                            commandTimeout: 10,
                            cancellationToken: commandDefinition.CancellationToken));
                    _databaseName = databaseName;
                }

                await _snowflakeDbConnection.ExecuteAsync(commandDefinition);
                _activityTime = DateTime.UtcNow;
            }

Error log

Snowflake.Data.Client.SnowflakeDbException (0x80004005): Error: Authentication token has expired.  The user must authenticate again. SqlState: , VendorCode: 390114, QueryId: 
   at Snowflake.Data.Core.SFSession.renewSession()
   at Snowflake.Data.Core.SFStatement.ExecuteAsync(Int32 timeout, String sql, Dictionary`2 bindings, Boolean describeOnly, CancellationToken cancellationToken)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 646
   at Ermetic.Sil.Common.SnowflakeClient.Connection.ExecuteAsync(String databaseName, CommandDefinition commandDefinition) in /build/src/Common/DataStores/Snowflake/SnowflakeClient.cs:line 156
   at Ermetic.Sil.Common.SnowflakeClient.ExecuteAsync(CommandDefinition commandDefinition, String databaseName) in /build/src/Common/DataStores/Snowflake/SnowflakeClient.cs:line 86
   at Ermetic.Sil.Common.SnowflakeTable`1.CreateTemporaryTableAsync(CancellationToken cancellationToken) in /build/src/Common/DataStores/Snowflake/SnowflakeTable.cs:line 122
   at Ermetic.Sil.Common.SnowflakeTable`1.UpsertManyAsync(IReadOnlyCollection`1 rows, CancellationToken cancellationToken) in /build/src/Common/DataStores/Snowflake/SnowflakeTable.cs:line 91
   at Ermetic.Sil.Worker.BiUpdater.<>c__DisplayClass4_0.<<UpdateCustomerAsync>g__UpdateCustomerAsync|0>d.MoveNext() in /build/src/Worker/Utilities/BiUpdater.cs:line 63
--- End of stack trace from previous location ---
   at Ermetic.Sil.Worker.BiUpdater.UpdateCustomerAsync(CustomerContext customerContext, CancellationToken cancellationToken) in /build/src/Worker/Utilities/BiUpdater.cs:line 50

Configuration

Driver version: 2.0.15

Dotnet framework and version: .net 6

Server version: 6.27.0

Client OS: alpine

[ivancesar-tt]

We have the same issue. Anyone to assist?

[omamoo]

@ivancesar-tt unfortunately no one assists..

[ivancesar-tt]

We are trying to revert to 2.0.13 as it doesn't use Connection pool. Fix would be to make it public and disable (I guess), but we'll see if revert fixes it for us.

[omamoo]

We are trying to revert to 2.0.13 as it doesn't use Connection pool. Fix would be to make it public and disable (I guess), but we'll see if revert fixes it for us.

Revert fixed it for us, however it will be much better if anyone will solve this issue.

[omamoo]

Anyone?

[omamoo]

keeping the issue active..

[sfc-gh-spanaite]

@omamoo Release v2.0.18 introduced the CLIENT_SESSION_KEEP_ALIVE property to prevent a session from timing out. Have you tried upgrading the connector?

[sfc-gh-jtang]

@sfc-gh-spanaite please close it if there's no response from the users.

[sfc-gh-spanaite]

@omamoo Since we've not received any feedback from you and taking into account that a property to prevent a session from timing out (CLIENT_SESSION_KEEP_ALIVE) has been introduced with release 2.0.18 I will be closing this issue. If you still encounter issues after upgrading to 2.0.18 at least feel free to open a new issue.