MySQL C# async methods doesn't work?

Question

i have a database in a server and it seems like the async method doesn't work.

Here is my code:

static async void Example()
{
    string connectionString =
        "Server=mydomainname.com;" +
        "Port=3306;" +
        "Database=scratch;" +
        "Uid=Assassinbeast;" +
        "Password=mypass123;" +
        "AllowUserVariables= true;";

    MySql.Data.MySqlClient.MySqlConnection sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
    await sqConnection.OpenAsync();

    Console.Write("Opened. Now Click to close");
    Console.ReadLine();

    sqConnection.Close();
}
static void Main(string[] args)
{
    Console.ReadLine();
    Example();
    Console.WriteLine("Done");
    Console.ReadLine();
}

At the "await" statement, its actually supposed to jump back to the Main() function and writeout "Done". But its not doing that. Its just running synchronously like it wasn't an async method and it will first write "Done" once the function is fully completed.

So what am i doing wrong? Is it a bug?

UPDATE

Okay, so after i got some answers, i actually still couldn't see any difference between the OpenAsync() and Open().

I began trying to test more things out and i think i can conclude that the async method DOES NOT WORK

Here is my new code:

static async Task Example()
{
    string connectionString =
        "Server=mydomainname.com;" +
        "Port=3306;" +
        "Database=scratch;" +
        "Uid=Assassinbeast;" +
        "Password=mypass123;" +
        "AllowUserVariables= true;";

    using (var sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
    {
        Console.WriteLine("Opening");
        await sqConnection.OpenAsync();
        Console.WriteLine("Opened. Now Closing");
    }
}
static async Task Example2()
{
    //Lets pretend this is a database that my computer will try to connect to

    Console.WriteLine("Opening");
    await Task.Delay(1000); //Lets say it takes 1 second to open
    Console.WriteLine("Opened. Now Closing");
}
static void Main(string[] args)
{
    Console.ReadLine();

    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Console.WriteLine("Done"));
    Console.ReadLine();
}

Here when i run the Example() 5 times, it will output like this:

It takes up to 3 seconds before it will write out "Done". Note that my computer is not working on the CPU at all, because it is only waiting and connecting to the database which takes around 1 second to connect to.

So its actually blocking my computers thread and not running multithreaded, otherwise it would write out "Done" immediedtly.

So if i called Example2() instead of Example(), then i get this result which is what i want and what i would expect:

Here, it is true async method, because i can do over 6 things at a time on my computer which has only 2 cores. But the first example, i could only do two things at a time because MySQL async method does not work.

I also tested it with sqConnection.Open(), which had the exact same result as sqConnection.OpenAsync()

So right now, i just cant figure out how to connect to the database 5 times at the same time.

Answer 1

Judging from some old code (6.7.2), it appears that the mysql ADO.NET provider does not implement any of the async functionality correctly. This includes the TAP pattern and the older style Begin..., End... async patterns. In that version, the Db* async methods appear to not be written at all; they would be using the base class ones in .NET which are synchronous and all look something like:

public virtual Task<int> ExecuteNonQueryAsync(...) {
    return Task.FromResult(ExecuteNonQuery(...));
}

(100% synchronous with the added overhead of wrapping it in a task; reference source here)

If the Begin and End versions were written correctly (they aren't) it could be implemented something like this:

public override Task<int> ExecuteNonQueryAsync(...) {
    return Task<int>.Factory.FromAsync(BeginExecuteNonQueryAsync, EndExecuteNonQueryAsync, null);
}

(reference source for that method for SqlCommand)

Doing this is dependent on some sort of callback api for the underlying socket to eventually deal with in a pattern where the caller sends some bytes over the socket and then a registered method gets called back from the underlying network stack when it is ready.

However, the mysql connector doesn't do this (it doesn't override that method in the first place; but if it did, the relevant begin and end methods aren't async on some underlying socket api). What the mysql connector does instead is build a delegate to an internal method on the current connection instance and invokes it synchronously on a separate thread. You cannot in the meantime for example execute a second command on the same connection, something like this:

private static void Main() {
    var sw = new Stopwatch();
    sw.Start();
    Task.WaitAll(
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync());
    sw.Stop();
    Console.WriteLine(sw.Elapsed.Seconds);
}

private static DbCommand GetDelayCommand() {
    var connection = new MySqlConnection (...);
    connection.Open();
    var cmd = connection.CreateCommand();
    cmd.CommandText = "SLEEP(5)";
    cmd.CommandType = CommandType.Text;
    return cmd;
}

(assuming you are connection pooling and the number of tasks there is more than the maximum pool size; if async worked this code would get a number depending on the number of connections in the pool instead of a number depending on both that and the number of threads that can run concurrently)

This is because the code has a lock on the driver (the actual thing that manages the network internals; *). And if it didn't (and the internals were otherwise thread safe and some other way was used to manage connection pools) it goes on to perform blocking calls on the underlying network stream.

So yeah, no async support in sight for this codebase. I could look at a newer driver if someone could point me to the code, but I suspect the internal NetworkStream based objects do not look significantly different and the async code is not looking much different either. An async supporting driver would have most of the internals written to depend on an asynchronous way of doing it and have a synchronous wrapper for the synchronous code; alternatively it would look a lot more like the SqlClient reference source and depend on some Task wrapping library to abstract away the differences between running synchronously or async.

* locking on driver doesn't mean it couldn't possibly be using non-blocking IO, just that the method couldn't have been written with a lock statement and use the non-blocking Begin/End IAsyncResult code that could have been written prior to TAP patterns.

Edit: downloaded 6.9.8; as suspected there is no functioning async code (non-blocking IO operations); there is a bug filed here: https://bugs.mysql.com/bug.php?id=70111

Update July 6 2016: interesting project on GitHub which may finally address this at https://github.com/mysql-net/MySqlConnector (could probably use more contributors that have a stake in its success [I am no longer working on anything with MySql]).