Pomelo.entityframeworkcore.mysql: Not Support MySQL on Azure

[yukozh]

All operations will throw an exception

MySqlException: Access denied for user 'hdtest-ucdb%uc'@'10.0.0.24' (using password: YES)
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
System.Runtime.CompilerServices.TaskAwaiter.GetResult()
System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable+ConfiguredValueTaskAwaiter.GetResult()
MySql.Data.Serialization.MySqlSession+<ResetConnectionAsync>d__23.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
MySql.Data.MySqlClient.ConnectionPool+<GetSessionAsync>d__0.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
MySql.Data.MySqlClient.MySqlConnection+<CreateSessionAsync>d__55.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
MySql.Data.MySqlClient.MySqlConnection+<OpenAsync>d__11.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
MySql.Data.MySqlClient.MySqlConnection.Open()
Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalConnection.Open()
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.BufferlessMoveNext(bool buffer)
Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute<TState, TResult>(Func<TState, TResult> operation, Func<TState, ExecutionResult<TResult>> verifySucceeded, TState state)
Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute<TState, TResult>(IExecutionStrategy strategy, Func<TState, TResult> operation, TState state)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.MoveNext()
Microsoft.EntityFrameworkCore.Query.QueryMethodProvider+<_ShapedQuery>d__3.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.MySqlQueryingEnumerable+MySqlEnumerator.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+<_TrackEntities>d__15.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor+EnumeratorExceptionInterceptor.MoveNext()
System.Linq.Enumerable.LongCount<TSource>(IEnumerable<TSource> source)
Microsoft.AspNetCore.Mvc.Paging.Divide<T>(ref IEnumerable<T> src, int PageSize, int Page)
Microsoft.AspNetCore.Mvc.BaseController.PagedView<TModel>(IEnumerable<TModel> Source, int PageSize, string ViewPath)
Hongding.WeChat.UserCenter.Controllers.AdminController.Application(string alias, Nullable<ApplicationStatus> status) in AdminController.cs
lambda_method(Closure , object , Object[] )
Microsoft.AspNetCore.Mvc.Internal.ObjectMethodExecutor.Execute(object target, Object[] parameters)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeActionMethodAsync>d__27.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
System.Runtime.CompilerServices.TaskAwaiter.GetResult()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeNextActionFilterAsync>d__25.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeNextResourceFilter>d__22.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ResourceExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeAsync>d__20.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
System.Runtime.CompilerServices.TaskAwaiter.GetResult()
Microsoft.AspNetCore.Builder.RouterMiddleware+<Invoke>d__4.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
System.Runtime.CompilerServices.TaskAwaiter.GetResult()
Microsoft.AspNetCore.Session.SessionMiddleware+<Invoke>d__9.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.AspNetCore.Session.SessionMiddleware+<Invoke>d__9.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
System.Runtime.CompilerServices.TaskAwaiter.GetResult()
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware+<Invoke>d__7.MoveNext()

@caleblloyd @bgrainger Pomelo 1.0.0 works fine with mysql on azure but pomelo >=1.0.1(With MySqlConnector) doesn't work.

[yukozh]

@bgrainger Does MySqlConnector accept a % in UserID？

[bgrainger]

Does MySqlConnector accept a % in UserID？

It should, but I don't think there's a test that verifies that. I can add a test to make sure.

[yukozh]

It seems not related to userid. But it is really not works with mysql on azure.

[yukozh]

Should I provide the conn str?

[bgrainger]

A Wireshark (or similar) packet capture would probably be the most helpful diagnostic output (if it doesn't use SSL, or you can force it to be cleartext).

[mguinness]

@Kagamine Can you clarify your issue? Is it just an issue w/ MySqlConnector or do you also get same issue w/ Connector/Net? Are you using ClearDB on Azure or MySQL in a VM? I assume mysql -u user_name works w/o problems?

[yukozh]

@bgrainger

Filter: (ip.dst == 139.219.4.246 ) || (ip.src == 139.219.4.246)

wireshark_log.zip

The version should be 5.7, may be the mysql on azure is a forked version by Microsoft. They still send version 5.5.

[yukozh]

@mguinness This issue doesn't occur with Pomelo.EntityFrameworkCore.MySql 1.0.0 (Based on Pomelo.Data.MySql). But use MySqlConnector without pomelo is ok.

pomelo ef + pomelo ado = ok
mysql connector = ok
pomelo ef + mysql connector = fail

[bgrainger]

This connection uses TLS, so I can't diagnose the problem just from the packet capture.

Could it be possible that the user you're logging in as can only access a certain database, but the Database (or Initial Catalog) property isn't being set in the connection string?

Should I provide the conn str?

Yes, what is your connection string? (You can mask the password.)

[yukozh]

@bgrainger

host: hdtest-ucdb.mysqldb.chinacloudapi.cn
userid: hdtest-ucdb%uc
pwd: ********
port: 3306

[bgrainger]

Thanks; with that information I've been able to reproduce a failure creating an auth switch response to MySQL on Azure.

[yukozh]

Thx

[bgrainger]

I can reproduce the failure with the official MySql.Data connector:

var csb = new MySqlConnectionStringBuilder
{
    Server = "hdtest-ucdb.mysqldb.chinacloudapi.cn",
    UserID = "hdtest-ucdb%uc",
    Password = "********",
    SslMode = MySqlSslMode.None, // not required, but enables packet capture & inspection
    Pooling = true, // this is true by default, but key to reproducing the bug
    ConnectionReset = true, // this is false by default for the official connector; true for MySqlConnector
};

using (var connection = new MySqlConnection(csb.ConnectionString))
{
    connection.Open();
    connection.Close();
    connection.Open(); // resets an existing connection from the pool; crashes
}

Next step is to test MySQL Server 5.5 locally to see if I can repro with that, or if it's specific to Azure.

[yukozh]

Not sure the mysql version. I've selected the MySQL 5.7, and it displayed 5.7 in azure portal. But the server responsed 5.5.

When I using Pomelo 1.0.0, JsonObject could stored successfully, I was confused the version of the MySQL on Azure. Maybe it is not a standard mysql server.

[bgrainger]

I can't repro with MySQL Server 5.5.54 on Windows.

connection.ServerVersion returns 5.5.0.0 from the Azure server.

[bgrainger]

If I force this if statement to be true, then connecting to Azure (and resetting the session) works just fine. Unfortunately, MySqlConnector doesn't go down that code path by default because Azure MySQL is reporting its version as 5.5.0.0. but we require 5.7.3 as a minimum for supporting the "reset connection" command. This seems to prove that Azure is actually running 5.7 code, but seemingly misreporting the version?

Since the first general availability for MySQL Server 5.5 was 5.5.8, maybe it'd be OK to assume 5.5.0.0 does not represent a valid MySQL Server version, but assume it's Azure and at least 5.7.3?

[bgrainger]

There are two possible workarounds with the current code:

1. Set MySqlConnectionStringBuilder.Pooling = false: disable connection pooling and the library will never need to reset a connection when retrieving it from the pool. This could cause performance issues.
2. Set MySqlConnectionStringBuilder.ConnectionReset = false: disable resetting of a connection when retrieving it from the pool. This could cause correctness issues (because settings, temp tables, variables, etc.) from the previous use of the connection will be retained.

See mysql-net/MySqlConnector#169 for discussion of any potential fixes to be made to the MySqlConnector library.

[yukozh]

move to upstream mysql-net/MySqlConnector#169

[bgrainger]

@Kagamine Is this a ClearDB database on Azure?

[yukozh]

Dunno, I will call the customer service after spring festival. But I think it is not.

[bgrainger]

I'm not able to repro this problem after adding a "MySQL Database" by ClearDB from the Azure Marketplace (to a free account). Its hostname is us-cdbr-azure-west-b.cleardb.com and reports its version as 5.5.45-log, so it seems to be something different.

@Kagamine Can you provide instructions about how the type of database you're using is added to an Azure account?

[yukozh]

@bgrainger That's not a third-party mysql service, it has been provided by Microsoft officially. I've created mysql service in the official portal.

[yukozh]

It seems that there are some differences between Azure CN and Azure Global

[yukozh]

I confirmed this mysql service has been provided by Microsoft, not cleardb.

[yukozh]

@bgrainger I've asked the Microsoft staff, the told me they have not support the reset connection operation yet.

[bgrainger]

FYI, COM_RESET_CONNECTION does actually work; it's the "change user" command (COM_CHANGE_USER) that's not functioning correctly.

[yukozh]

The mysql service which provided by Microsoft officially will be published to azure global soon.

[JeffreySu]

mark

[yukozh]

@JeffreySu To add Reset Connection=false, it will works fine.

[bgrainger]

Now that Azure Database for MySQL is available globally, I've been able to reproduce this issue and report it in the MSDN forums.

[yukozh]

Yes, I am the member of that team. I've tested your provider before public preview. And it works well, r u sure you still can repro this issue?

In this Saturday our Principal PM will make a speech and include your lib and Pomelo.

[bgrainger]

And it works well, r u sure you still can repro this issue?

Yes, I just did about an hour ago (against a Azure Database for MySQL 5.7 instance in US West). With Pooling=True;Connection Reset=True in the connection string, I got an "Access denied" exception from both MySql.Data (Connector/NET) and MySqlConnector when reusing a connection from the pool.

I'd be happy to connect with someone offline and send packet captures or debug changes to the client. (Note that it's just COM_CHANGE_USER that is broken; COM_RESET_CONNECTION works just fine with 5.7.)

In this Saturday our Principal PM will make a speech and include your lib and Pomelo.

👍 😍 Will that be public and/or recorded anywhere?

[yukozh]

Hi @bgrainger. Could you provide the results of your investigation and contact to Xiangyu.

[yukozh]

I will take photos for you.

[bgrainger]

@Kagamine I will contact Xiangyu with more details in the next few days.

[yukozh]

@bgrainger Thanks.