"The connection is closed" errors in System.Data.SqlClient on Linux without MARS. #54
Comments
|
As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues. |
David-Engel
added
the
Area\Managed SNI
|
I have same problem only on Linux. Error occurs on Ubuntu 18.04 with .NET Core 3.0 preview 5. StackTrace is like in first example of @JulianRooze public async Task AddReportAsync(Report report, Func<Task> onSuccess = null, CancellationToken ct = default)
{
if (report == null)
throw new ArgumentNullException("отчет не может быть неопределенным");
Logger?.Invoke($"SQL Добавление отчета Id={report.Id}");
using var command = new SqlCommand("dbo.AddReport");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Id", SqlDbType.VarChar, 28).Value = report.Id;
command.Parameters.Add("@UserId", SqlDbType.VarChar, 28).Value = report.UserId;
command.Parameters.Add("@Status", SqlDbType.VarChar, 20).Value = report.Status;
command.Parameters.Add("@CancelOrDeclineReason", SqlDbType.NVarChar, 254).Value = (object)report.CancelOrDeclineReason?.Trim() ?? DBNull.Value;
command.Parameters.Add("@Defects", SqlDbType.NVarChar, 254).Value = (object)report.Defects ?? DBNull.Value;
command.Parameters.Add("@AgreementDate", SqlDbType.DateTime).Value = (object)report.AgreementDate ?? DBNull.Value;
command.Parameters.Add("@AgreementNumber", SqlDbType.NVarChar, 254).Value = (object)report.AgreementNumber ?? DBNull.Value;
command.Parameters.Add("@ProductType", SqlDbType.NVarChar, 254).Value = (object)report.ProductType ?? DBNull.Value;
command.Parameters.Add("@BsoNumber", SqlDbType.NVarChar, 254).Value = (object)report.BsoNumber ?? DBNull.Value;
command.Parameters.Add("@Comments", SqlDbType.NVarChar, 254).Value = (object)report.Comments?.Trim() ?? DBNull.Value;
command.Parameters.Add("@PaymentType", SqlDbType.NVarChar, 20).Value = (object)report.PaymentType ?? DBNull.Value;
command.Parameters.Add("@OrderId", SqlDbType.Int).Value = report.OrderId;
command.Parameters.Add("@TimeSpend", SqlDbType.Int).Value = (object)report.TimeSpend ?? DBNull.Value;
command.Parameters.Add("@Cost", SqlDbType.Int).Value = (object)report.Cost ?? DBNull.Value;
command.Parameters.Add("@IssueTime", SqlDbType.DateTime).Value = report.IssueTime;
command.Parameters.Add("@Deposit", SqlDbType.Int).Value = report.Deposit ?? (object)report.Cost ?? DBNull.Value;
command.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = (object)report.ToDate ?? DBNull.Value;
command.Parameters.Add("@IsAccepted", SqlDbType.Bit).Value = report.IsInner;
if (report.UsedDetails.Count > 0)
{
using var dt = new DataTable();
dt.Columns.Add("Number", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Count", typeof(int));
dt.Columns.Add("Price", typeof(int));
var number = 0;
foreach (var detail in report.UsedDetails)
dt.Rows.Add(number++, detail.Name, detail.Count, detail.Price);
command.Parameters.Add(new SqlParameter("@Details", SqlDbType.Structured)
{
TypeName = "dbo.DetailsType",
Value = dt
});
}
//When this is true
if (report.Photos.Count > 0)
{
using var dt = new DataTable();
dt.Columns.Add("Number", typeof(int));
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("Code", typeof(byte[]));
foreach (var photo in report.Photos)
dt.Rows.Add(photo.Number, photo.Type, photo.Code);
command.Parameters.Add(new SqlParameter("@Photos", SqlDbType.Structured)
{
TypeName = "dbo.PhotosType",
Value = dt
});
}
//Causes error
await ExecuteForMaxAttemptsAsync(command, ct, onSuccess).ConfigureAwait(false);
}
protected async Task ExecuteForMaxAttemptsAsync(SqlCommand command, CancellationToken ct = default, Func<Task> callback = null)
{
if (command == null)
throw new ArgumentNullException(nameof(command), "Комманда не может быть неопределенной");
byte attempts = 1;
while (true)
try
{
using var sc = new SqlConnection(Config.ConnectionString);
await sc.OpenAsync(ct).ConfigureAwait(false);
using var transaction = sc.BeginTransaction();
command.CommandTimeout = Config.CommandTimeout;
command.Connection = sc;
command.Transaction = transaction;
//Exception is thrown here
await command.ExecuteNonQueryAsync(ct).ConfigureAwait(false);
if (callback != null)
await callback().ConfigureAwait(false);
transaction.Commit();
break;
}
catch (TaskCanceledException)
{
throw;
}
catch (Exception ex)
{
if (attempts >= Config.MaxAttempts)
throw ex;
Logger?.Invoke($"SQL Ошибка выполнения транзакции: {ex.GetType().Name} {ex.Message}. Попытка {attempts}");
attempts++;
await Task.Delay(Config.RetryInterval).ConfigureAwait(false);
}
} |
|
Same error only on Linux. |
|
Thank you @JulianRooze for the great write up. We also see this intermittently on services running in linux docker containers (dotnetcore 2.2.3 on alpine 3.9). Enabling MARS on the connection as a workaround also worked. The SQLConnection's StateChange event is called unexpectedly indicating a transition from Open to Closed, and the same "connection is closed" exception is thrown to the code executing the sql call. We have logic verifying that a single SQLConnection is not being used concurrently. With a retry added in the code, before enabling the workaround we would sometimes see multiple retries fail with the same, on other occasions one retry worked. Currently we have only seen this on calls that have table-valued parameters (it may be more likely for larger parameter payloads). Sometimes (but not always) on a fail there is a sql-server logged error message which is usually: |
|
We also found this only while running on Azure SQL ( most likely it's Linux underneath), while calling Stored Procs with a larger TVP, and while there are multiple calls happening, all connections from connection pool. This error did not occur if no other query was running. Only with parallel requests (web requests anyway), could this be consistently reproed. P.S - Running on .net core 3.0 on alpine3.9 |
|
We also found this issue while running on Azure SQL with Alpine using I'm doing a bulk insert calling a stored procedure with a TVP that has 1000 rows in it. We are using a datatable + dapper to call the stored proc: public static async Task MergeCustomerTokensAsync(this IDbConnection connection, List<CustomerTokenEntity> customerTokens, IDbTransaction transaction = null, CancellationToken cancellationToken = default)
{
var dataTable = new DataTable();
dataTable.Columns.Add("CustomerId", typeof(Guid));
dataTable.Columns.Add("Token", typeof(string));
dataTable.Columns.Add("UserAgent", typeof(string));
dataTable.Columns.Add("Processed", typeof(DateTimeOffset));
foreach (var item in customerTokens)
{
dataTable.Rows.Add(item.CustomerId, item.Token, item.UserAgent, item.Processed.HasValue ? (object)item.Processed.Value : DBNull.Value);
}
var param = new
{
CustomerTokens = dataTable.AsTableValuedParameter()
};
await connection.ExecuteAsync(new CommandDefinition("dbo.[BulkMergeCustomerTokenImportV1]", param, commandType: CommandType.StoredProcedure, cancellationToken: cancellationToken));
} |
|
We're seeing this issue as well with Microsoft.Data.SqlClient versions 1.1.X - 2.X running on Linux containers without MARS. This repros easily with a query that has a large number (thousands) of TVP values. The longer the round-trip time the easier this seems to occur. I created a forked repository https://github.com/arivaltaoja/SqlClient that contains a modified DockerLinuxTest that reproduces the issue almost 100% when executed against Azure SQL, especially if you're on a slow connection. Based on quick debugging the issue seems to be that async void SNIPacket.WriteToStreamAsync and therefore the underlying stream's WriteAsync are called while the previous task is still being executed. This results in System.NotSupported exception being thrown from the underlying stream. System.NotSupported is later on cough and rethrown as System.InvalidOperationException. |
|
@arivaltaoja |
|
I tested my repro case against PR #579 and it seems to fix this issue as well 👍 |
|
We are also seeing the same error in our environment. We have a .NET CORE 3.1 background service hosted in AKS on Linux containers with the SqlClient nuget package Microsoft.Data.SqlClient (1.1.1). AKS Cluster is located in WEU and the Databases are located in WEU and EUS. What the background service does, is inserting around 2.5K rows into a table via a stored procedure. Rows to be inserted are passed into the SP as a TVP. However, something we noted is that we have not yet seen this error while the background service is connecting to the WEU database. But we see this error every time when inserting to the database located in EUS. We are thinking of turning MARS in connection string as the mentioned workaround in here. But we are bit skeptical to do so since there are different set of issues when MARS is ON in a Linux environment as mentioned in #422. Any idea when will this be fixed? |
|
@asankaf Why don't you use latest version (Microsoft.Data.SqlClient 2.0.1)? |
|
@DenSmoke We can of course upgrade the nuget package. But as per one of the above comments from @arivaltaoja, upgrading the nuget package doesn't necessarily fix the issue. |
|
I had the same (or similar) issue with Microsoft.Data.SqlClient 1.1.3 (came from Microsoft.EntityFrameworkCore.SqlServer 3.1.9) in linux containers. Resolved by explicitly referencing Microsoft.Data.SqlClient 2.0.1 |
|
Maybe I have a similar Issue #792 , but Upgrade to Microsoft.Data.SqlClient 2.0.1 did not help. (also not any other preview) |
|
@cheenamalhotra Will there be a preview package released with this fix in the near term? Do you have a MyGet feed or similar for nightly builds? |
|
Hi @alexmg You may access nightly build feed here: https://dev.azure.com/sqlclientdrivers-ci/sqlclient/_packaging?_a=feed&feed=Microsoft.Data.SqlClient.dev We'll be releasing this fix soon! Thanks! |
* Creat TVP prior to opening sql connection Data Table creation is slow. connection may be timing out while we create it. * USe MARS-enabled connections to fix connection issue dotnet/SqlClient#54
* Creat TVP prior to opening sql connection Data Table creation is slow. connection may be timing out while we create it. * USe MARS-enabled connections to fix connection issue dotnet/SqlClient#54 (cherry picked from commit 9841502)
* Creat TVP prior to opening sql connection Data Table creation is slow. connection may be timing out while we create it. * USe MARS-enabled connections to fix connection issue dotnet/SqlClient#54
We've recently run into an issue where executing SQL commands results in
"Invalid operation. The connection is closed."exceptions being thrown.MultipleActiveResultSets(MARS) in the connection string seems to eliminate this error entirely.SqlConnectionis pretty standard: they come from a connection pool, and we don't use them concurrently.The exception that gets thrown is this one:
https://github.com/dotnet/corefx/blob/a74bf4200926c47c11ba0383e2bddeaa59227266/src/System.Data.SqlClient/src/System/Data/Common/AdapterUtil.SqlClient.cs#L395
The exception gets thrown (or rethrown) from these three points in the code:
Example stacktrace:
Example stacktrace:
Example stacktrace:
So it either happens when writing data to SQL Server or reading a result set and it seems to happen on longer running operations, but probably only because a longer query makes it more likely for the error to show up.
Our connection string looks like this:
Server=tcp:<DB>.database.windows.net,1433;Initial Catalog=<DB>;Persist Security Info=False;User ID=<USER>;Password=<PW>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;The text was updated successfully, but these errors were encountered: