.NET MySQL Unable to connect to any of the specified mysql hosts

-1

I know this exception has been discussed before, but I can't find my exact scenario anywhere.

I'm developing a Windows Forms application that interacts with a local MySQL Database. This is my connection string: "datasource=localhost;port=3306;database=mydb;username=root;password=mypwd" and this is my code:

MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;database=mydb;username=root;password=mypwd");
MySqlCommand command;

Data = null;

try
{
    connection.Open();
    command = new MySqlCommand(query, connection);
    MySqlDataReader MyReader = command.ExecuteReader();

    if (MyReader.HasRows)
    {
        DataTable dt = new DataTable();
        // DataTable.Load automatically advances the reader to the next result set
        dt.Load(MyReader);
        Data = dt;
    }

}
catch(Exception ex)
{
    log.Error($"[ReadMySQL] - {ex.Message}!");
}
finally
{
    connection.Close();
}

What I'm experiencing is, after about 10 minutes of inactivity in terms of database interaction, when calling connection.Open(), the application hangs for up to a minute and then throws Unable to connect to any of the specified mysql hosts exception. After catching this exception, when I call the read function again, everything suddenly works, but if another period of inactivity occurs, it hangs and throws the exception again.

MySQL Workbench never throws any error, either.

EDIT:

I've changed my code to an "using" statement as suggested by Panagiotis Kanavos below, but the issue wasn't resolved.

This is the code now

MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;database=mydb;username=root;password=mypwd");
            Data = null;

            try
            {
                using(connection)
                using (MySqlCommand com = connection.CreateCommand())
                {
                    connection.Open();
                    com.CommandText = query;
                    com.CommandTimeout = int.MaxValue;
                    using (MySqlDataReader reader = com.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            DataTable dt = new DataTable();
                            // DataTable.Load automatically advances the reader to the next result set
                            dt.Load(reader);
                            Data = dt;
                        }
                    }
                }

            }

            catch (Exception ex)
            {
                log.Error($"[ReadMySQL] - {ex}!");
                log.Error($"[ReadMySQL] - {ex.GetBaseException()}!");
                string innerMessage = ex.InnerException == null ? ex.Message : ex.Message + " --> " + ex.InnerException.Message;
                log.Error($"[ReadMySQL] - {innerMessage}!");
            }

Here is the full exception message

MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.Common.StreamCreator.GetStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.Driver.Open()
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
   at MySql.Data.MySqlClient.MySqlPool.GetConnection()
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at agrana_v1.mysql_db.ReadMySQL(String query, DataTable& Data)

and this is the BaseException

MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.Common.StreamCreator.GetStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.NativeDriver.Open()

The server is not configured to "sleep" when inactive, unless that's a default setting that I don't know about?

c#
mysql
.net
windows
asked on Stack Overflow May 20, 2021 by Iulian Leon • edited May 20, 2021 by Iulian Leon

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0