GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

No commit and query statements

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • No commit and query statements

    Dear All,
    I have a problem here where first my application will do a select statement then immediately it will run begin and commit statement. I have attached my log file too. The problem now is that if you notice for all the Id such 76,78,80,82, 84 and 86 you have proper begin and commit. I dont understand why for 88 there is a begin statement but no other statement like commit. Is something very suprising because if there is problem with my application then all should fail but I am puzzled why is it not committed.

  • #2
    Are you using InnoDB tables?

    Is there an uncommitted transaction in SHOW INNODB STATUS?

    Comment


    • #3
      Dear Xaprb,
      Yes I am using all innodb tables.Attached is my file for the innodb engine status. I am dont see any uncommited transaction. Actually what this status gives you in depth about what is happening to my db is it ?

      Comment


      • #4
        It is possible that you have AUTOCOMMIT set, so BEGIN and COMMIT are useless anyway.

        Comment


        • #5
          Dear Xaprb,
          So can you help me point out what is my mistake here? Is it due to Autocommit is it? So what must I do can you guide me ? Thank you.

          Comment


          • #6
            No, I can't help you because I don't understand what you are asking. You haven't even really stated the problem you're experiencing, just some observations you've made. (IS there a problem at all?) But I've pointed you in the direction of some things that I think you should learn about.

            Comment


            • #7
              Dear Xaprb,
              Ok maybe I have confuse you earlier. Let me rephrase my problem. My problem is simple among few transaction why there is one which just show a begin statement but no commit statement. I have attached my log file in my first write up of this forum. I hope I am clear now.

              Comment


              • #8
                The application code is buggy. It needs to commit its transaction.

                Comment


                • #9
                  Dear Xaprb,
                  I dont deny the code can be buggy.But to my suprise why all my other inserted and updated well and using the same code for the commit.I have included a snippet of my code below.

                  [CODE]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.stockSIQ='" + stringSerial + "' And tblStock.stockID=" + stockID; ;

                  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 tblTempTransfer " +
                  "Set productType='Coupon', " +
                  "productID=" + productID + ", " +
                  "stockID ='" + stockID + "', " +
                  "stockSIQ ='" + stringSerial + "', " +
                  "employeeID = '" + globalSettings.settingEmployeeID + "', " +
                  "tempTransferTimeStamp='" + 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();
                  }
                  }

                  Comment


                  • #10
                    Dear Xaprb,
                    I have done this settings init_connect='set autocommit=0' in my my.ini file. But when I run this query select @@autocommit; it still shows the answer as 1. Why is this happening? Thank you.

                    Comment


                    • #11
                      I can't answer that. You need to troubleshoot. Start by learning about global and session variables in the MySQL manual, and check the global value. If that's not the problem, then also check what your mysql client is doing. (Is it command-line, sqlyog, phpmysqladmin, etc? Check what it does.) Look at SELECT CONNECTION_ID() and then look at what queries that session has executed in the log.

                      Comment


                      • #12
                        Dear Xaprb,
                        Any hints or tips. Cause I did the settings according to the manual. I have tried SELECT CONNECTION_ID(). I just give me some integere numbers. I am using sqlyog. Anything else must I do ? Thank you.

                        Comment

                        Working...
                        X