Fatal error encountered attempting to read the resultset after 1 hour

[erdem yalçın]

I have a code piece to run a stored procedure in mysql. Code runs and completes when execution time is under 1 hour however when it takes more than 1 hour it gets an exception. I set command timeout to 7200 and I also tried to increase various other timeout parameters in code with following queries without any success:


SET SESSION wait_timeout=99999; SET SESSION interactive_timeout=99999;
set net_write_timeout=99999; set net_read_timeout=99999

When I use Dbeaver client procedure completes even if it takes more than 1 hour.

I'm using Mysql.Data version: 8.4. Mysql database version is:8.0.32

Code it as follows:

string sql = "EDW.SP_DENEME_20240503";
string connStr = @"Server=hostname;Port=port; Database=;Uid=xxxx;Pwd=pwd;AllowLoadLocalInfile=true;allowPublicKeyRetrieval=true;Connect Timeout=120";
using (var conn = new MySqlConnection(connStr))
using (MySqlCommand cancellableCommand = new MySqlCommand(sql, conn))
{
cancellableCommand.CommandTimeout = 7200;
cancellableCommand.CommandType = CommandType.StoredProcedure;

cancellableCommand.Parameters.Add(new MySqlParameter("v_ASOFDATE", 20240421));
cancellableCommand.Parameters.Add(new MySqlParameter("v_LOGTYPE", "M"));
conn.Open();
OpenConnection(conn, connectionRetryCount);
return cancellableCommand.ExecuteNonQuery();
}


Error message:

Exception msg :Fatal error encountered during command execution.
Exception inner exc :Fatal error encountered attempting to read the resultset.
Exception stacktrace : at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__111.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__111.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySql.Data.MySqlClient.MySqlCommand.<ExecuteNonQueryAsync>d__100.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

I would appreciate any suggestions regarding the issue.