MySQL Bugs: #37790: Transaction not working properly

Bug #37790	Transaction not working properly
Submitted:	2 Jul 2008 3:20	Modified:	18 Jul 2008 16:09
Reporter:	newbie Shai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.8.1	OS:	Windows (xp)
Assigned to:		CPU Architecture:	Any

Description:
Dear All,
        I have this application which is using Innodb engine and .net connector 5.0.8.1. So I am using C# for my coding. So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So why is this happening. I have separately attached below my function for the transactionConnectionLocal1  where I keep the connection details.

            int rollbackBoolean = 0;
            MySqlTransaction transactionLocal1 = null;
            MySqlConnection connectionLocal1 = null;
            transactionConnectionLocal1 callTransactionConnectionLocal1 = null;
            try
            {
                callTransactionConnectionLocal1 = new transactionConnectionLocal1();
                connectionLocal1 = callTransactionConnectionLocal1.localConnection1;
                connectionLocal1.Open();
                transactionLocal1 = connectionLocal1.BeginTransaction();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                rollbackBoolean = 1;
                MessageBox.Show("Error From Database Connection " + ex.Message);
            }
            catch (System.Net.Sockets.SocketException ex)
            {
                rollbackBoolean = 1;
                MessageBox.Show("Error Sockets From Database Connection " + ex.Message);
            }
            
            String myUpdateQuery1 = "Update tblStock " +
                                    "Set tblStock.stockStatus = 'b'" +
                                    "Where tblStock.stockSerial ='" + serial + "'" ;
            MySqlCommand myCommand1 = new MySqlCommand(myUpdateQuery1);

            try
            {
                myCommand1.Connection = connectionLocal1;
                myCommand1.Transaction = transactionLocal1;
                myCommand1.ExecuteNonQuery();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                rollbackBoolean = 1;
                MessageBox.Show("Error From myUpdateQuery1 " + ex.Message);
            }
            catch (System.Net.Sockets.SocketException ex)
            {
                rollbackBoolean = 1;
                MessageBox.Show("Error Sockets From myUpdateQuery1 " + ex.Message);
            }
            finally
            {
                myCommand1.Dispose();
            }

            String myInsertQuery1 = "Insert into tblTemp " +
                                     "Set stockSerial ='" + serial+ "', " +
                                     "tempTimeStamp='" + DateTime.Now.ToString(("yyyy:MM:dd HH:mm:ss")) + "';";
            MySqlCommand myCommand2 = new MySqlCommand(myInsertQuery1);

            try
            {
                myCommand2.Connection = connectionLocal1;
                myCommand2.Transaction = transactionLocal1;
                myCommand2.ExecuteNonQuery();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                rollbackBoolean = 1;
                MessageBox.Show("Error From myInsertQuery1 " + ex.Message);
            }
            catch (System.Net.Sockets.SocketException ex)
            {
                rollbackBoolean = 1;
                MessageBox.Show("Error Sockets From myInsertQuery1 " + ex.Message);
            }
            finally
            {
                myCommand2.Dispose();
            }

            if (rollbackBoolean == 1)
            {
                transactionLocal1.Rollback();
            }
            else
            {
                try
                {
                    transactionLocal1.Commit();
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    try
                    {
                        transactionLocal1.Rollback();
                    }
                    catch (MySqlException ex1)
                    {
                        MessageBox.Show("An exception of type " + ex.GetType() +
                                      " was encountered while inserting the data.");
                        if (transactionLocal1.Connection != null)
                        {
                            MessageBox.Show("An exception of type " + ex1.GetType() +
                                              " was encountered while attempting to roll back the transaction.");
                        }
                    }
                }
                catch (System.Net.Sockets.SocketException ex)
                {
                    rollbackBoolean = 1;
                    MessageBox.Show("Error Sockets From Commit Process " + ex.Message);
                }
                finally
                {
                    connectionLocal1.Close();
                }
            }

// connection details function 
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace mSytemNonFranchise
{
    public class transactionConnectionLocal1
    {
        public MySqlConnection localConnection1;
        public MySqlCommand command;

        public transactionConnectionLocal1()
        {
            this.localConnection1 = new MySqlConnection("Address='localhost';Database='localDB';User Name='root';Password='local12';Pooling='false'");
           // this.command = this.localConnection1.CreateCommand();
           // this.localConnection1.Open();
        }

        // destructor - explicitly
        ~transactionConnectionLocal1()
        {
            //this.command.Dispose();
            this.localConnection1.Close();
            this.localConnection1.Dispose();
        }
    }
}

How to repeat:
Difficult to see how to repeat this cause most of the times it works well.

Bug #37791 was set as duplicate of this bug here

does this code always fail?  My guess is that you have some subtle bug in how you've code the application.  You've written in what looks like 100 lines what you could have written in about 10.  You should avoid using a destructor for your class.

My suggestion would be to setup some breakpoints right after the update but before the insert and then manually check the database to see if the update actually is happening.  I truly don't believe this is a bug in the connector.

Dear Reggie,
            To you question of the frequence of failure is like 4 out of 500 records. Why I was using the destructor is that to clean up incase my connection is not close because earlier I use to have connection overflow problem. The problem if I set a breakpoint then the application will close rite? So as per your suggestion you said you can make it to 10 lines of code can you guide me with an example that will be helpful I guess. This is a very rare case this problem occurs, so is really difficult for me to build a test case. As the next time when I do it is ok with the same data both the update and insert works together? So I am kind of very consfuse cause this not a problem which stays forever. Thanks.

Here is one way you could do it with *alot* less code

public void CallWork()
{
  try
  {
    DoWork();
  }
  catch (Exception ex)
  {
    MessasgeBox.Show("Error doing work: " + ex.Message);
  }
}

public void DoWork()
{
  // the using statement will close and dipose the connection when it exits
  // rolling back the transaction if it is not committed therefore a rollback
  // is not necessary
  using (MySqlConnection connection = new MySqlConnection(connectionString))
  {
	connection.Open();
	MySqlTransaction txn = connection.BeginTransaction();

	MySqlCommand cmd = new MySqlCommand(updateQuery, connection);
	cmd.ExecuteNonQuery();
	cmd.CommandText = insertQuery;
	cmd.ExecuteNonQuery();
	txn.Commit();
  }
}

Dear Reggie,
            I appreciate your code. So I guess in this case I dont need any extra class to declare the connection and called it rite? So what I can do is keep the connectionString as a public value. My only concern here is that using your method incase there is some errors with my query be it the update or insert I would not be able to track it right where the source of the error ? Thanks.

Hi newbie. I would like to close this report now, if you agree, as it truly appears to be a problem in coding.

Dear Tonci,
           I have put some queries is my earlier question but have not been answered ? So I guess in this case I dont need any extra class to
declare the connection and called it rite? So what I can do is keep the connectionString as a public value. My only concern here is that using your method incase there is some errors with my query be it the update or insert I would not be able to track it right where the source of the error ? How to verify that my executenonquery actually really worked maybe some validation mechanism? Thanks.

Newbie, I am not at liberty to write code for you... for that you need support contract. Our role is to take compact and complete test case from you and verify if there is a bug in MySQL SW, or not. As I don't see the bug here I must rule this report as !Bg.

Thanks for your interest in MySQL.

Dear Tonci,
          I do agree with your job role I know you cant help me in full. How much does the support contract cause and what the kind of services do they offer to us? I mean from my perspective I am still very confuse because you say is due to my code then it should not be working at all. This strange type of cases happen only on a very minimal case like 5 out of 400 records. I am looking from objective view obiviously it can be the code right because why it can generate so many correct values in the first place? Thanks.

Hello newbie. Paid support on using our products is available for a reasonable fee. Please see details at http://www.mysql.com/support/.

Thank you for your interest in MySQL.