MySQL Bugs: #70111: Asynchronous functions in MySql connector/Net executes synchronously

Bug #70111	Asynchronous functions in MySql connector/Net executes synchronously
Submitted:	21 Aug 2013 18:31	Modified:	18 Jan 2023 17:24
Reporter:	Anders Fleron	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.7.4.0	OS:	Windows (7)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	async, asynchronous, await, connector, NET

Description:
The asynchronous functions in MySql Connector/Net does not behave as expected. They execute synchronously.

F.ex. the function MySqlConnection.OpenAsync() does not return immediately but blocks the calling thread.

How to repeat:
1) Create a new c# console application named MySqlAsyncBug running with .Net 4.5 
2) Add reference to mysql.data.dll
3) Paste the following code into Program.cs to illustrate the bug

using System;
using System.Data.SqlClient;
using System.Threading;
using MySql.Data.MySqlClient;

namespace MySqlAsyncBug
{
    class Program
    {
        private const string connectionString = "Server=disco;Database=disco;Uid=disco;Pwd=disco;";
        private AutoResetEvent sync = new AutoResetEvent(false);

        static void Main(string[] args)
        {
            new Program().Run();
        }

        public void Run()
        {
            Console.WriteLine("Running. Takes a minute to complete...");
            Console.WriteLine("Correct order: 12");

            Console.WriteLine("MSSql: ");
            WorkingAsync();
            Console.WriteLine("1");
            sync.WaitOne();

            Console.WriteLine("MySql: ");
            MySqlAsyncBug();
            Console.WriteLine("1");
            sync.WaitOne();

            Console.Write("Finished. Press any key to exit...");
            Console.Read();
        }

        private async void MySqlAsyncBug()
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    // Mysql connector blocks calling thread here
                    await conn.OpenAsync();
                }
                catch (MySqlException)
                {
                    Console.WriteLine("2");
                    sync.Set();
                }
            }
        }

        private async void WorkingAsync()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    await conn.OpenAsync();
                }
                catch (SqlException)
                {
                    Console.WriteLine("2");
                    sync.Set();
                }
            }
        }
    }
}

Suggested fix:
Let functions like MySqlConnection.OpenAsync and MySqlCommand.ExecuteReaderAsync return awaitable tasks

Thank you

Has there been any update on when this feature might be added ?

I just checked out the sources and it seems that it was fixed in 6.8.1 beta
However, it seems that "fix" is just a dirty hack.
From source:
    public Task OpenAsync()
    {
      return Task.Factory.StartNew(() =>
      {
        Open();
      });
    }
So it just creates new thread instead of doing async IO - user can do it himself if creating new threads is acceptable solution

Does the Connector/Net team have an official position on this? Does they intend to implement *real* async methods on future versions? Also, making ExecuteNonQueryAsync create a new thread on v6.8 only made things worse, as this creates massive overhead when executing multiple queries and creates problems with threading and connections.

MySql team should tell us when a **REAL** implementation of async/await methods will be provided. As others said, the current implementation is a dirty hack.

Hello.

Please read the following points:
1. Async-Await was not added on Connector 6.7.X, the methods that you see are the inherited from the .Net Framework classes.

2. It will be very hard to see any benefit using Async-Await on a Console Application in the way that you are testing, because using Async-Await is intended to run on a Synchronization Context which a Console App doesn't have, so I'll suggest you to do some tests on an application that uses UI (WPF, ASP Net, etc), or you can refer to the following links where it's explained how to work with a Synchronization Context using Async-Await in Console Apps: 
http://blogs.msdn.com/b/pfxteam/archive/2012/01/20/10259049.aspx (part 1)
http://blogs.msdn.com/b/pfxteam/archive/2012/01/21/10259307.aspx (part 2)
http://blogs.msdn.com/b/pfxteam/archive/2012/02/02/10263555.aspx (part 3)

3. We are aware of the code that is on Connector 6.8.x, and we are working to change the current async methods, the update will be released soon.

Thanks for your time

Please can you confirm that your intended async implementation will be be end-to-end e.g. awaiting on network IO completion?

Also, will the new "real" async methods follow the new async - await pattern (TAP) on .NET 4.5? I was looking at the Connector source the other day and I saw that there was some new async implementation - but with Begin..Async and End..Async. It's already something, but really, having to setup callbacks and whatnot isn't very pleasing.

Hello.

@Ian: I'm not pretty sure if I understand your question, but the changes applied to the current code removes the part that generates a new running task, instead of that now just a task is returned without creating any other process/thread while the method is running.

@Andre: maybe the methods that you saw are some old ones, the new implementation is intended to work using tasks and not using APM (Begin...End approach).

Thanks for your time.

Oh, ok thanks for the answer. Can I ask one more question? With the new async methods, will it be possible to run two commands at the same time on a single connection, or will it throw a "There is already an open data reader associated with this connection" MysqlException as it does currently?

Hello André.

If you want to run the async method at the same time using the ExecuteReaderAsync, yes you will get the exception, this because the async methods are trying to use the same resource at the same time, and to reuse a connection on a different data reader you must first close the connection even if you're not using the async version.

But if you use ExecuteNonQueryAsync or ExecuteScalarAsync at the same time you'll not have any problem.

Thanks for your time.

Are there any plans to implement real async support for the .NET Connector?

Has been 2 years since the request was opened, and nothing so far.

Hello Natan.

The Async-Await support is available for Connector/Net 6.9.X since the 6.9.2 RC Version, you can take a look to the change log here:
http://dev.mysql.com/doc/relnotes/connector-net/en/connector-net-news-6-9-2.html

Please let us know any issue that you have regarding this functionality.

Thanks for your time.

Fancisco,

I have been using MySql.Data for a long time, going through all the versions, and currently I'm using 6.9.7 and I can confirm there is nothing async implemented in the API and probably never was.

Looking at the sources for 6.9.7, I can see there is no implementation of connection.OpenAsync(), command.ExecuteNonQueryAsync(), command.ExecuteReaderAsync() or command.ExecuteScalarAsync(), which effectively makes the API non-existent.

From what I see, it is just using the inherited implementations from DbCommand which are not async, but are just syncrhonous shims returning Task.FromResult() to expose the API.

There are some implementations in MySqlHelper, but they are incorrect. For example, MySqlHelper.ExecuteScalarAsync creates a TaskCompletionSource, calls ExecuteScalar syncrhonously and use tcs.SetResult. This is effectively the same as Task.FromResult(command.ExecuteScalar()) and adds no benefits.

It is really common that most people even in very known open source projects don't understand what the async api means. But in short, the current implementation is completely incorrect and useless.

First, all async operations in the data provider should be implemented overriding the base async methods, that is, DbConnection.OpenAsync, DbCommand.ExecuteXyzAsync(), etc, not in helpers, otherwise components that depend on provider factories won't be able to use it.

Then, unless the code uses the async API internally in some way, and frees the thread when it is awaiting for IO operations, it is not async. It just looks like it. You should also never, ever, ever use Task.Run or Task.StartNew to create async APIs. That is not how it works and this is not the purpose of these methods.

Take a look into SqlClient implementation for example, it's implemented using wrappers around Begin/End calls.

http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,520c...

But then take a look at how BeginExecute works, it actually leaves the thread and creates continuations to run when it finishes:

http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,195b...

This won't work in MySql.Data because from what I see in the sources, all Begin/End calls also are shims calling the syncrhonous counterparts, and that means there is effectively no async API in MySql.Data.

I'm interested in seeing this implemented, and currently it isn't even though you believe it is because there are methods that look like it. I'd be glad to help in some way.

And I'm sorry if I feel arrogant or pedantic here, it's not my intention.

Could we get an update on this story?  Does the team plan to implement this feature soon?  Will the source code to this connector be made available on Github sometime soon so that the community can look at implementing something.

I'm curious to see what comes out of this. Any updates would would be greatly appreciated.

Francisco,

Is there any update on this topic?

Any updates on this from the dev team?

Natan,

I've started a new project that is a truly async implementation of the ADO.NET API for MySQL. (It also supports .NET Core and non-Windows platforms.) The code is on GitHub (https://github.com/bgrainger/MySqlConnector) and a pre-release package is on NuGet (https://www.nuget.org/packages/MySqlConnector/).

Since it's a new implementation, it doesn't support all the features of MySql.Data but should be sufficient for "simple" data access scenarios. If you want to try it out, I'd be interested in any feedback you have.

So MySQL is not a serious option for intensive-use applications via .Net then? .Net is not a significant share of the market, it is just an afterthought, a "Non-critical" issue in the eyes of MySQL devs?

So your philosophy is essentially: "Look at our amazing product, see what it can do! Sure it works on Windows! But only as a toy"

When the bug will be resolved?  It's a crucial feature for asp .net core projects.  And please migrate the project to .net 6!

MySqlConnector is fully async and v2.0.0 supports .NET 6 today: https://www.nuget.org/packages/MySqlConnector/

Time for the yearly comment on this. This is a massive, massive problem. Is there any way to pay oracle to fix this?

Posted by developer:
 
This fix is part of the WL#15484.
The asynchronous methods for the Classic protocol has been re implemented so now it is async all the way down to the I/O operations.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.33 release, and here's the proposed changelog entry from the documentation team:

Asynchronous methods used with classic MySQL protocol connections now are
implemented to ensure asynchronous behavior at the level of I/O
operations. Previously, some asynchronous methods were executed in a
synchronous context.

Thank you for the bug report.