System.Data.SqlClient.SqlException: The connection is broken and recovery is not possible

[divega]

From @robbpriestley on March 23, 2016 19:27

Hello,

I'm pretty new to EF and .NET Core, but I just thought I'd report an issue I had today and have been able to diagnose somewhat. It has to do with the runtime and applying a migration. In my ASP.NET Core project, when I removed the dnx451 framework from project.json (intending instead to rely entirely on the dnxcore50 framework) and I perform the following actions:

dnvm use default -r coreclr
dnx ef database update

I consistently get the below exception. To resolve, I need to restore the dnx451 framework in the project.json and use the mono runtime. Then, it works.

My database is SQL Server running on Amazon RDS.

Exception message:

Applying migration '20160323185819_Update1'.
System.Data.SqlClient.SqlException: The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlConnection.ValidateAndReconnect(Action beforeDisconnect, Int32 timeout)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.<>c.<ExecuteNonQuery>b__13_0(DbCommand cmd, IRelationalConnection con)
   at Microsoft.Data.Entity.Storage.Internal.RelationalCommand.Execute[T](IRelationalConnection connection, Func`3 action, String executeMethod, Boolean openConnection, Boolean closeConnection)
   at Microsoft.Data.Entity.Storage.RelationalCommandExtensions.ExecuteNonQuery(IEnumerable`1 commands, IRelationalConnection connection)
   at Microsoft.Data.Entity.Migrations.Internal.Migrator.Execute(IEnumerable`1 relationalCommands)
   at Microsoft.Data.Entity.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.Data.Entity.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.Data.Entity.Commands.Program.Executor.Execute(Action action)
ClientConnectionId:7f7dac22-d9a0-49cc-950b-5a532b754754
The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.

Copied from original issue: aspnet/EntityFramework#4891

[divega]

@robbpriestley thanks for reporting this. Could you provide more data on how to repro? E.g. what operating system and exact version of our bits are you using?

If you are using Linux what you are seeing might be related to https://github.com/dotnet/corefx/issues/4273, but that issue is supposedly fixed in RC1.

[divega]

From @robbpriestley on March 31, 2016 16:22

Hello @divega

Sure thing. I am running OSX and here's my dnvm list

Active Version              Runtime Architecture OperatingSystem Alias
------ -------              ------- ------------ --------------- -----
       1.0.0-rc1-update1    coreclr x64          darwin          
  *    1.0.0-rc1-update1    mono                 linux/osx       default

I skimmed the dotnet/corefx#4273 issue and it does seem to be similar. At least my gut feeling is that The connection is broken and recovery is not possible is specific enough to suggest a connection. If it was fixed for Linux, perhaps not for OSX. (Although, I'm entirely not sure what MARS is)

Here's my project.json dependencies section as well

  "dependencies": {
    "System.Net.Security": "4.0.0-beta-22416",
    "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
    "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
    "Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
    "Microsoft.AspNet.Tooling.Razor": "1.0.0-rc1-final",
    "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
    "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
    "Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final",
    "EntityFramework.Core": "7.0.0-rc1-final",
    "EntityFramework.Commands": "7.0.0-rc1-final",
    "EntityFramework.Relational": "7.0.0-rc1-final",
    "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
    "EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final"
  },

[divega]

@robbpriestley MARS is the ability to process multiple active query results on the same connection at the same time. It is enabled in the connection string with this: "MultipleActiveResultSets=True".

I will move this issue to CoreFx since it seems to be a problem with SqlClient for .NET Core (and so that the right people can investigate) but please mention me if anything is required from the EF side.

[divega]

@YoungGah, @saurabh500 This looks like it could be a similar issue to https://github.com/dotnet/corefx/issues/4273 but in OSX.

In summary: the customer explains that error "System.Data.SqlClient.SqlException: The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection." occurs consistently when targeting a SQL Server instance in the cloud (Amazon RDS) if using SqlClient for .NET Core but not with Mono's built-in SqlClient implementation.

[saurabh500]

@robbpriestley @divega Do you folks know if the issue happens intermittently or consistently?

@robbpriestley what is the size of the data of the data you are querying from the server? Does this reproduce for an on premise server as well?

[divega]

@saurabh500 I haven't tried to repro this myself. Hopefully @robbpriestley can help.

[saurabh500]

@divega Thanks. I will wait for @robbpriestley I was looking to get the kind of operations being tried by @robbpriestley so that we can try to replicate them.

[robbpriestley]

I tried switching back and forth from coreclr to mono twice and it was consistent (worked in mono, not in coreclr).

It wasn't really a database query, you see it was a database migration. Still, minimal size. Basically a demo.

And sorry, I do not have an on-premise server to test on so I cannot say.

[saurabh500]

@robbpriestley What was the migration scenario? Was it copying a small database from one server to another? Are there any steps that I can try to reproduce this issue?

[robbpriestley]

No, I mean, it's an Entity Framework migration.

I'm still pretty new to EF, but I think it's a very simple scenario. Repro is roughly like this:

• In AWS RDS I set up a basic SQL Server instance and test to be able to connect to it from code etc.
• In code, I set up a simple model. Like one or two classes resolving via EF to one or two DB tables.
• I try to migrate the EF model to the database as follows:

dnvm use default -r coreclr
dnx ef database update

Let me know if you need any additional information.

[saurabh500]

Thanks @robbpriestley this is helpful.

[robbpriestley]

@saurabh500 you're welcome. I realize this could be a difficult actual repro due to significant environmental specifics (Mac, AWS, SQL Server, etc)

Not to mention I may soon switch to MySQL anyway for other reasons.

But, I thought I'd report it anyway because I am an enthusiastic early adopter.

[saurabh500]

@robbpriestley Thanks for reporting the bug. We will try to get this to repro.

[divega]

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]

Closing as stale and not repro.