MySQL Bugs: #37965: Rare cases of single query is updated ?

Bug #37965	Rare cases of single query is updated ?
Submitted:	8 Jul 2008 11:58	Modified:	10 Jul 2008 19:01
Reporter:	newbie Shai	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.0.8.1	OS:	Windows (Xp)
Assigned to:		CPU Architecture:	Any

Description:
Dear Al,
        I have an application where in a particular function I have one update query and insert query where I have group them into 1 single transaction. The problem here is that in some very rare cases upon commit only my insert query is executed but the update query is not executed. This happens on a very rare basis but this causes integrity problem to my database.

How to repeat:
Difficult to set the repeat cases as it happens on a rare basis.

Thank you for the bug report. Your server version is quite older please upgrade if you get the same issue could you please provide a complete test case. Thanks in advance.

Dear Miguel,
             There are many different version. So which one is the best one do you recommend to be used ? My problem is that the version 5.0.27 is already been installed in many places. So what is the best way to replace all of them together ? Thanks.

Our newest version is MySQL 5.0.51b and we recommend to use this version.

Can you paste here the complete SQL statement please.

I mean from begin; ... commit;

Dear Susanne,
             Thanks I have download the latest version but normally I tried to un install my old version and install the new one always gives me some problems. Must I always manually delete the mysql folder after installation process? Below is my code which is giving me the problem. If you notice my code there is 2 queries one is the update and the other is insert and both I have declared under 1 transaction. The rare case happens when my insert queries is executed upon the commit statement but my update query is not executed. From what I notice this problem happens like 5 out of 400 records. Can you see any problem here ? Thanks.

            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 tblProduct " +
                                    "Set tblProduct.productBooked = tblProduct.productBooked + " + quantity +
                                    " Where tblProduct.productID=" + productID;

            //MessageBox.Show(myUpdateQuery1);
            MySqlCommand myCommand1 = new MySqlCommand(myUpdateQuery1);
            myCommand1.Connection = connectionLocal1;
            myCommand1.Transaction = transactionLocal1;
            try
            {
                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 tblTempReceipt " +
                                     "Set productType='Accessory', " +
                                     "productID=" + productID + ", " +
                                     "stockID ='" + accessoryRowCount + "', " +
                                     "stockSIQ ='" + quantity + "', " +
                                     "employeeID = '" + globalSettings.settingEmployeeID + "', " +
                                     "tempReceiptTimeStamp='" + DateTime.Now.ToString(("yyyy:MM:dd hh:mm:ss")) + "';";

            MySqlCommand myCommand2 = new MySqlCommand(myInsertQuery1);
            myCommand2.Connection = connectionLocal1;
            myCommand2.Transaction = transactionLocal1;
            try
            {
                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.");
                        }
                    }

                }
                finally
                {
                    connectionLocal1.Close();
                }
            }

I can't see something abnormal ...
Is the update query not executed or does it just don't have results?
I mean or is it just trying to update a row that has already the values to which it should be updated?

Can you look into the log, what happens there exactly?

Dear Susanne,
            The problem like what you see I also dont see anything abnormal. The update is not executed infact.Which logs do you want me to look into ? I have not enabled any of the log prior to this problem and latter only I learnt about the logs. The thing I want to hightlight to you is that most of the time both the queries work fine only for certain rare cases this happens so is kind  of difficult to trace the exact cause of this problem. Thanks for looking into this problem? Do you think is bug in the database transaction itself is that possible?

Newbie,

we don't have enough informations at the moment to say what/why happens here. At the moment it can happens because of anything.

And because it happens only sometimes we can't just write a test and reproduce it here for further analysis.

Of course we want to help you.

The easiest way for us and with a chance of 75 percent for you is, that you upgrade to 5.0.51b and let us know if the problem will occur there too.

The other way is, we have to plead you to do lots of stuff to find the cause and when we found the cause we can make tests with newest version and can give it to development for fixing ... if it is a bug at the end.

Anyway then you have to update to the upcoming version to get the benefit of fixed bug.

In your case I would recommend that we give it a try that you upgrade to the newest version now.

We fixed lots of stuff between version 5.0.27 and 5.0.51b especially at the transaction topic.

What I need when you won't upgrade is the rows from the general log when the update wasn't taken. I need three or four rows before that event and then the whole log during event.

As I told you before, I can't promise that this is the only thing we need for analysing and what we need after that depends on the log.

What do you think?

Dear Susanne, 
            First thing I would really like to thank you for all the time you took to write to me and handle this problem. Looks like from you advice I have no choice but then to uninstall and reinstall newer version of the server. So once I have installed to prepare for future occurence what are the logs that you want me to be prepared with and that I should be enabling ? I do agree you cant create a test case even I cant create a test case because when I try back again after noticing the problem the transaction is working fine. When you said you fixed a lof of transaction stuff is it for innodb engine? So please advice what are the other stuff I need to be prepare for future analyse. Really appreciate and touched by you.Thanks.

Newbie,

what we need to get a better overview is the general log from the occuring moment.

http://dev.mysql.com/doc/refman/5.0/en/query-log.html

You can start it without server restart by using:

SET GLOBAL LOG='ON';

When you want to switch it of again then:

SET GLOBAL LOG='OFF';

The log file will be stored in your data directory and the file will end with .log.

Dear Susanne,
             Ok besides this log settings is there anything else we will need to analyse incase this problem occurs again in the new version? Do you think I should also change the .net connector ? So you was saying there is improvement on the transaction is it in the innodb engine? Thanks again.

You didn't tell us, that you are using .net before.

It makes sense to use the newest version here too. But when you are using .net this also can be a .net problem.

Anyway we need the log first. I will change the category to .net.

Dear Susanne,
             Sorry for my missing information. I am using C# and .net connector 5.0.8.1. So what do you mean by changing to category .net? Are you going to post this problem to the .net connector guys ? I hope you can do that so that maybe they can also look into this rare case? Thanks.

Thank you for the feedback.

Currently we can not say if this is problem with Connector/NET, MySQL or your application. To get full information we need you test it with current version 5.0.51b and general log file. Please note general log file can be turned on only in configuration file in version 5.0. You can not turn it on or off online before version 5.1.

Also please check if both tables you do update and insert on are InnoDB tables and if you don't use non-transactional storage engines in the problem transaction.

Dear Sveta,
          Yes I have counter checked that bought my tables are innodb. So what version of the .net connector do you recommend to be used along with the mysql 5.0.51b ? Do you find anything wrong in my code from your perspective as the .net connector people ?

Thank you for the feedback.

Current verision of Connector/Net 5.0 is 5.0.9 and GA is 5.1.6

Dear Sveta,
           I am confused 5.0.9 and GA is 5.1.6? Which is to pick ? What is GA? Thanks.

GA means generally available.

I would recommend .NET version 5.1

Sorry I forget to answer a question from you before.

I changed category to .NET because this also could be a .NET bug and I want our .NET specialist to get a look into this too.

Dear Susanne,
            Thanks for your help. So are your .net connector experts are looking into it ready ?

This is exactly the same bug as 37790.  let's please keep further comments on that bug report.