Hello everybody,
I am new on this forum. I have an issue on MySQL commands timeout.
My configuration is:
- MySQL server 5.6.23
- .NET 4.0
- Connector C#
- MySQL.Data.dll 6.9.5.0
The problem is: I would like to set a specific command timeout to launch long running stored procedure. I tried both to set it directly the property of the MySQLCommand object and to set it in the connection string, but the connection seems to fail after the double of the number of seconds I configure. I mean: if I set 10 seconds the DB call fails after 20 seconds, if is set 5, it fails after 10 seconds, and so on.
Am I missing something? Thanks for any help.
Dario
This is the code which resumes my tests:
1/3) C# CODE:
---------------------------------------------------------------
DateTime dtStart, dtEnd;
MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["tdm_local"].ConnectionString);
connection.Open();
// Create sql command
using (MySqlCommand sqlCommand = new MySqlCommand("Infinity", connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
Console.WriteLine("Connection Timeout: {0}; Command Timeout = {1}", sqlCommand.Connection.ConnectionTimeout, sqlCommand.CommandTimeout);
// set parameters
sqlCommand.Parameters.Clear();
dtStart = DateTime.Now;
try
{
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
dtEnd = DateTime.Now;
Console.WriteLine("Inner time: {0}", (dtEnd - dtStart).TotalSeconds);
}
}
connection.Close();
connection.Dispose();
----------------------------------------------------
2/3) CONNECTION STRING
----------------------------------------------------
<add name="tdm_local" connectionString="server=localhost;user id=youruser;password=yourpassword;database=yourdatabase;pooling=true;default command timeout=5;" />
3/3) DB LONG RUNNING STORED PROCEDURE
----------------------------------------------------
DROP procedure IF EXISTS `Infinity`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Infinity`(
)
BEGIN
select sleep(900);
END$$
DELIMITER ;