Connection must be valid and open to rollback transaction int C# and MySql DB

Question

I develop a programe use c# Framework2.0 and Database is mysql. I use transaction but always has error

System.InvalidOperationException: Connection must be valid and open to rollback transaction at MySql.Data.MySqlClient.MySqlTransaction.Rollback()

Here is some of source code:

 try
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                MySqlTransaction myTrans = connection.BeginTransaction();
                command.Connection = connection;
                command.Transaction = myTrans;

                try
                {

                   string sql ="XXXXX";
                   command.CommandText =sql;
                   command.ExecuteNonQuery();

                   sql="yyyy";
                   command.CommandText =sql;
                   command.ExecuteNonQuery();

                   .........

                  myTrans.Commit();

                }
                catch (Exception ex)
                {
                    myTrans.Rollback();
                    throw ex;
                }
            }
        }
        catch (Exception ex)
        {
            FileHelper.WriteLog(ex);
        }

I check the sample from:

http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqltransaction.html

The source code should be ok.

The error said the connection seems close already. I think use Using() should be ok. it will close automatically.

Can Some help me with this?

Answer 1

Finally I find the solution .

The MySqlCommand.CommandTimeout default is 30 seconds.

maybe the server is old and data is big. so timeout

so show error

Connection must be valid and open to rollback transaction

Just Set MySqlCommand.CommandTimeout=1200

this one can work fine.

it's really hard to find the root cause.