SNOW-974730: DB connection issues. Error: Request reach its timeout. SqlState: , VendorCode: 270007

[mnhowa]

We are noticing some random closing of our Snowflake Connection, causing issues further down in our application.

1. What version of .NET driver are you using?

2.1.3

2. What operating system and processor architecture are you using?

Microsoft Windows Server 2016, 64-bit

3. What version of .NET framework are you using?

.NET Framework 4.7.2

4. What did you do?

Attempted to connect to Snowflake to query a table

using (var source = new CancellationTokenSource(connection.InternalUniversalTimeout ?? TimeSpan.FromMilliseconds(Int32.MaxValue)))
           using (var conn = new SnowflakeDbConnection())
           {
               conn.ConnectionString = connection.ConnectionString;
               await conn.OpenAsync(source.Token);

               var command = conn.CreateCommand();
               command.CommandTimeout = 0;

               var queryBuilder = new StringBuilder();
               queryBuilder.Append($"SELECT ProcessGroupName, StartTimeStamp, EndTimeStamp, JobStatus FROM test_db.{schema}.etlrunstatus WHERE ");
               queryBuilder.Append($"UPPER(ProcessGroupName) = UPPER('{database}') ");
               queryBuilder.Append($"AND UPPER(DatabaseName) = UPPER('{database}') LIMIT 1;");
               command.CommandText = queryBuilder.ToString();

               var reader = await command.ExecuteReaderAsync(source.Token);

               if (await reader.ReadAsync())
               {
                   DateTime? startTimestamp = null;
                   DateTime? endTimestamp = null;
                   string jobStatusValue = null;
                   JobStatus jobStatus = JobStatus.Failed;

                   if (reader["STARTTIMESTAMP"] != DBNull.Value && !string.IsNullOrEmpty(reader["STARTTIMESTAMP"].ToString()))
                   {
                       startTimestamp = Convert.ToDateTime(reader["STARTTIMESTAMP"]);
                   }

                   if (reader["ENDTIMESTAMP"] != DBNull.Value && !string.IsNullOrEmpty(reader["ENDTIMESTAMP"].ToString()))
                   {
                       endTimestamp = Convert.ToDateTime(reader["ENDTIMESTAMP"]);
                   }

                   if (reader["JOBSTATUS"] != DBNull.Value && !string.IsNullOrEmpty(reader["JOBSTATUS"].ToString()))
                   {
                       jobStatusValue = reader["JOBSTATUS"].ToString();

                       jobStatus = (JobStatus)Enum.Parse(typeof(JobStatus), jobStatusValue, true);
                       if (!Enum.IsDefined(typeof(JobStatus), jobStatus))
                       {
                           jobStatus = JobStatus.Failed;
                       }
                   }

                   return new ETLRunStatus
                   {
                       DatabaseName = database,
                       ProcessGroupName = database,
                       ProcessName = database,
                       JobStatus = jobStatus,
                       StartTimestamp = startTimestamp,
                       EndTimestamp = endTimestamp
                   };
               }
           }

5. What did you expect to see?

   Snowflake connects without issue

6. Can you set logging to DEBUG and collect the logs?

2023-11-19 09:36:10,032 [150] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| Open Connection Async.
2023-11-19 09:36:10,034 [150] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| SnowflakeDbConnectionPool::GetSessionAsync
2023-11-19 09:36:10,036 [150] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| SessionPool::GetSessionAsync
2023-11-19 09:36:10,038 [150] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| SessionPool::GetIdleSession
2023-11-19 09:36:10,040 [150] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| Send closeSessionRequest
2023-11-19 09:36:10,042 [176] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| Executing: 2925039706674 POST https://waystar.east-us-2.azure.snowflakecomputing.com/session?delete=true&requestId=2eba6dc8-1961-4fd9-9e92-aa6d59f6fcdb&request_guid=66872586-7363-495c-8036-92d8ab6f450b HTTP/1.1
2023-11-19 09:36:10,049 [176] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| Http request timeout : 00:00:16
2023-11-19 09:36:10,050 [176] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| Rest request timeout : 00:02:00
2023-11-19 09:38:16,231 [54] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| SF rest request timeout or explicit cancel called.
2023-11-19 09:38:16,242 [150] DEBUG|{JobID:133, MasterJobID:10, MasterJobRunID:163016, CreatedBy:0, DateCreated:1/1/0001 12:00:00 AM Msg:}| Close Connection.
2023-11-19 09:38:16,246 [150] ERROR|(null)| One or more errors occurred.
System.AggregateException: One or more errors occurred. ---> Snowflake.Data.Client.SnowflakeDbException: Error: Request reach its timeout. SqlState: , VendorCode: 270007, QueryId:  ---> System.Threading.Tasks.TaskCanceledException: A task was canceled.
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Snowflake.Data.Core.RestRequester.<SendAsync>d__10.MoveNext()
   --- End of inner exception stack trace ---
   at Snowflake.Data.Core.RestRequester.<SendAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Snowflake.Data.Core.RestRequester.<SendAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Snowflake.Data.Core.RestRequester.<PostAsync>d__4`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Snowflake.Data.Core.RestRequester.<>c__DisplayClass3_0`1.<<Post>b__0>d.MoveNext()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
   at System.Threading.Tasks.Task`1.get_Result()
   at Snowflake.Data.Core.RestRequester.Post[T](IRestRequest request)
   at Snowflake.Data.Core.SFSession.close()
   at Snowflake.Data.Core.Session.SessionPool.GetIdleSession(String connStr)
   at Snowflake.Data.Core.Session.SessionPool.GetSessionAsync(String connStr, SecureString password, CancellationToken cancellationToken)
   at Snowflake.Data.Core.Session.ConnectionCacheManager.GetSessionAsync(String connectionString, SecureString password, CancellationToken cancellationToken)
   at Snowflake.Data.Client.SnowflakeDbConnectionPool.GetSessionAsync(String connectionString, SecureString password, CancellationToken cancellationToken)
   at Snowflake.Data.Client.SnowflakeDbConnection.OpenAsync(CancellationToken cancellationToken)

7. What is your Snowflake account identifier, if any? (Optional)

[sfc-gh-dszmolka]

hi and thank you for raising this issue ! debugging connectivity issues in the user infrastructure can be a potentiall long process and best done by working closely with a support engineer who can guide you on the troubleshooting your infrastructure and network and if it eventually turns out to be a real issue with the driver, rather than the infra, can help with that too.

can you please open a case with Snowflake Support if you did not do so yet and work with us that way ?

thank you in advance !