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?
User contributions licensed under CC BY-SA 3.0