c# - Azure 上的 EF6 连接到远程 MySQL DB 时出现间歇性问题

Question

我在 Azure 免费订阅上有一个站点，该站点是 EF6 上的 C# 连接到远程主机上的 MySQL 数据库。应用程序将正常运行一段时间，但对于经过身份验证的用户，它将在 ObjectDataSource 选择事件上出错，并显示以下内容:

Inner exception information (level 1):
    Exception type: System.Data.Entity.Core.EntityException
    Exception message: The underlying provider failed on Open.

Inner exception information (level 2):
    Exception type: MySql.Data.MySqlClient.MySqlException
    Exception message: Unable to connect to any of the specified MySQL hosts.

Request information:
    Request URL: https://xxx.azurewebsites.net:443/default.aspx
    Request path: /default.aspx
    User host address: 111.111.111.111
    User: xxx
    Is authenticated: True
    Authentication Type: ApplicationCookie
    Thread account name: IIS APPPOOL\xxx

Thread information:
    Thread ID: 24
    Thread account name: IIS APPPOOL\xxx
    Is impersonating: False
    Stack trace:    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance)
   at System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
   at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
   at System.Web.UI.WebControls.ListControl.PerformSelect()
   at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
   at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
   at System.Web.UI.WebControls.DropDownList.LoadPostData(String postDataKey, NameValueCollection postCollection)
   at System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, NameValueCollection postCollection)
   at System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

其他时候，对于未经身份验证的用户，它不会首先加载，因为:

    Inner exception information (level 1):
    Exception type: System.Data.Entity.Core.ProviderIncompatibleException
    Exception message: An error occurred accessing the database. This usually means that the connection to the database failed. Check that the connection string is correct and that the appropriate DbContext constructor is being used to specify it or find it in the application's config file. See the inner exception for details of the failure.

Inner exception information (level 2):
    Exception type: System.Data.Entity.Core.ProviderIncompatibleException
    Exception message: The provider did not return a ProviderManifestToken string.

Request information:
    Request URL: https://xxx.azurewebsites.net:443/
    Request path: /
    User host address: 111.111.111.111
    User: 
    Is authenticated: False
    Authentication Type: 
    Thread account name: IIS APPPOOL\xxx

Thread information:
    Thread ID: 64
    Thread account name: IIS APPPOOL\xxx
    Is impersonating: False
    Stack trace:    at System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app)
   at System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers)
   at System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context)
   at System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context)
   at System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext)

或

Thread information:
    Thread ID: 27
    Thread account name: IIS APPPOOL\xxx
    Is impersonating: False
    Stack trace:    at System.Data.Entity.Core.EntityClient.EntityConnection.<OpenAsync>d__8.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 System.Data.Entity.Core.Objects.ObjectContext.<EnsureConnectionAsync>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 System.Data.Entity.Core.Objects.ObjectContext.<ExecuteInTransactionAsync>d__3d`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 System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<GetResultsAsync>d__e.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 System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
   at System.Data.Entity.Internal.LazyAsyncEnumerator`1.<FirstMoveNextAsync>d__0.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 System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.<FirstOrDefaultAsync>d__25`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 Microsoft.AspNet.Identity.EntityFramework.UserStore`1.<FindAsync>d__d.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 Microsoft.AspNet.Identity.AsyncHelper.RunSync[TResult](Func`1 func)
   at Microsoft.AspNet.Identity.UserManagerExtensions.Find[TUser,TKey](UserManager`2 manager, UserLoginInfo login)
   at TDT.Account.RegisterExternalLogin.Page_Load()
   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.<LoadRecursiveAsync>d__4.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 System.Web.Util.WithinCancellableCallbackTaskAwaitable.WithinCancellableCallbackTaskAwaiter.GetResult()
   at System.Web.UI.Page.<ProcessRequestMainAsync>d__14.MoveNext()

我的连接字符串如下所示:第一个用于 Identity 内容的 ApplicationDbContext，第二个用于 EF 模型:

<add name="IdentityConnection" connectionString="server=xxx.xxx.xx.xxx;user id=xxx;database=xxx;password=Password;persistsecurityinfo=True" providerName="MySql.Data.MySqlClient" /> 
<add name="context" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyM‌​odel.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=111.111.11.11;user id=xxx;database=MyDB;password=password;persistsecurityinfo=True&quot;" providerName="System.Data.EntityClient" />

由于它位于免费的 Azure 上，并且您可以免费拥有 10 个站点，因此我已将该站点的相同版本上传到另一个 Azure 子域，因此当第一个站点出现错误时，我会检查第二个站点以及该站点与数据库的连接完美，应用程序加载正常。有人知道发生了什么事吗？是对多个用户的免费 Azure 订阅的限制还是一次对用户/连接数量的限制？我读到 ClearDB MySQL 的连接数有 4 个限制，但这个 MySQL 是完全独立的，不应该有这些限制。尽管第二个站点在第一个错误时确实会加载，但如果长时间使用，第二个站点也会产生错误。谢谢

Answer 1

我的一项连接到外部 MySQL 服务器的 Java 服务也遇到了类似的问题。在 Azure 技术人员的帮助下，我终于解决了这个问题。

如果问题与我的相关 - 它是由用于 Azure 上路由的 SNAT 表引起的，here is a post详细描述了该问题。

我最终为所有 TCP/IP 套接字连接发送了保持事件数据包。另一种解决方案是分配 PIP 地址，但我不确定是否可以使用 Azure 网站来做到这一点。

我观察到重新连接到我们的 MySQL 所需的时间差异很大，因此结论是，虽然您的远程服务器认为连接仍然打开，但它不会允许来自同一 IP(您网站的 VIP)的流量(Azure 术语)在 TCP 级别。

您可以尝试将数据库连接的保活时间设置为 4 分钟以下(我们的应用程序设置为 60 秒):

server=xxx.xxx.xx.xxx;user id=xxx;database=xxx;password=Password;persistsecurityinfo=True;Keepalive=60;

我几乎没有 C# 经验，因此请检查 KeepAlive 选项对于您的连接器是否有效。好像是根据this网站。