0

I have a .NET Core program that uses the MySqlConnection class. My Database is a ClearDB Database that is stored in Azure.

When I launch the program it is working like it should. But when I wait for like 10 minuts doing nothing, it wont connect to the database anymore(Timeout?). Restarting the program and it works again.

When looking at the connections on the ClearDB webpage it isn't closing when I close it in my program. After 10 minuts or so it closes automaticly, as I see in ClearDB webpage. But with the program still running it wont connect to the database anymore. Restarting program is only solution.

Code for now looks something like this:

private static async Task<uint> getDeviceId(string macAddress)
{
    using (var connection = new MySqlConnection(ConnectionString))
    {
        uint returnvalue = 0;
        var cmd = connection.CreateCommand() as MySqlCommand;
        cmd.CommandText = @"SELECT id FROM devices WHERE mac = '" + macAddress + "'";
        connection.Open();
        Console.WriteLine(connection.State);

        DbDataReader reader = await cmd.ExecuteReaderAsync();
        using (reader)
        {
            while (await reader.ReadAsync())
            {
                returnvalue = await reader.GetFieldValueAsync<uint>(0);
            }
        }
        reader.Dispose();
        cmd.Dispose();
        return returnvalue;
    }
}

I have tried the following:

  • Using statement
  • Close/dispose connection,reader and command
  • Pooling=false in connectionstring

But none of them works. Somebody got an idea?

mmarkvoort
  • 11
  • 3

2 Answers2

0

Assuming MySql provider is like the MSSQL provider, it does not actually close the connection in the database, it just releases it back to the pool.

You do not want to disable pooling, you will kill efficiency.

This is by design, and what you want.

Crowcoder
  • 11,250
  • 3
  • 36
  • 45
  • I only tired disable pooling because other suggested it on the internet. But when the program is running for 10 minuts or so I will close the connection in ClearDB. After that it wont connect when the program is still running. – mmarkvoort Jun 20 '17 at 15:14
  • @mmarkvoort I'm not convinced the failure to connect is a result of connection pool exhaustion. Like I said, it is normal for the connections to persist in the actual database. Unless you are throwing exceptions (in which case all of your disposals may not be happening) I find it unlikely the code you have shown is the problem. – Crowcoder Jun 20 '17 at 19:58
  • The code isn't the problem, I know. I am probably doing something wrong opening a new connection after the connection closed after 10min. – mmarkvoort Jun 21 '17 at 06:49
0

The using statement from the code snippet should close your connections. However, I'm not sure how that interacts with async, or how ClearDB differs from normal MySql. Given the issues in the question and that lack of clarity, you might try this, just to see if it helps:

private static async Task<uint> getDeviceId(string macAddress)
{
    uint returnvalue = 0;
    MySqlConnection connection;
    try
    {
        connection = new MySqlConnection(ConnectionString);

        var cmd = connection.CreateCommand() as MySqlCommand;
        //Don't EVER(!) use string concatenation like that in a query!
        cmd.CommandText = @"SELECT id FROM devices WHERE mac = @macAddress";
        cmd.Parameters.Add("@macAddress", MySqlDbType.VarChar, 18).Value = macAddress;
        connection.Open();
        Console.WriteLine(connection.State);

        DbDataReader reader = await cmd.ExecuteReaderAsync();
        using (reader)
        {
            while (await reader.ReadAsync())
            {
                returnvalue = await reader.GetFieldValueAsync<uint>(0);
            }
        }
        reader.Dispose();
        cmd.Dispose();

    }
    finally
    {
        connection.Close();
        connection.Dispose();
    }
    return returnvalue;
}

A using block basically just re-writes your code as try/finally anyway, so doing this step by-hand can sometimes make debugging easier (you can log where it hits the .Close() call).

If this does resolve the problem, I wouldn't stop there, but rather start from there and see just how close to "normal" code you can get. I'm also concerned here that you have disabled connection pooling, and that this method is static.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thanks for the quick answer, but your solution doesn't work. When I start program. It is working first time, but when I force quit the connection within cleardb webpage and try again. It wont connect anymore. And I didn't disable connection pooling, I only tried that if that would make a difference. My method wasn't static. – mmarkvoort Jun 20 '17 at 15:10
  • Forgot to add the parameter. – Joel Coehoorn Jun 20 '17 at 15:12
  • "`when I force quit the connection within cleardb webpage and try again. It wont connect anymore`" Don't do that! – Joel Coehoorn Jun 20 '17 at 15:13
  • Why not? It is the same as waiting 10 minuts when ClearDB will close it automaticly, right? – mmarkvoort Jun 20 '17 at 15:15
  • No, it's not. Opening a new connection involves (among other things) network latency and complicated/expensive negotiations that include validating or computing a password hash or certificate. The connection pool tries to avoid this by keeping several connections open in the background for re-use instead of always closing them immediately. You're _supposed_ to see some lingering open connection in the ClearDB console, and when you force close those connections you are breaking that mechanism. – Joel Coehoorn Jun 20 '17 at 15:23
  • The connection pool will eventually close things on it's own, if given the chance, but if you also force close your app, you'll leave those pool connections hanging there until they time out on their own. – Joel Coehoorn Jun 20 '17 at 15:23
  • Oke, I get that thanks! But it has the same behaviour when I wait for the connection to close. So I thought I could reproduce the error more quickly. – mmarkvoort Jun 21 '17 at 06:44