MySQL Bugs: #54644: Auto QUERY KILL

Bug #54644	Auto QUERY KILL
Submitted:	20 Jun 2010 16:15	Modified:	30 Jul 2010 16:03
Reporter:	newbie Shai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	.net connector 6.2.3	OS:	Any (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Dear All,
        I have a scenario where I notice in my log file for few different instances there is one query running and another one is called and kills the former running query.I have attached two sample cases but I have many samples to be shown.

Sample 1
If you see this statement 320 Query KILL QUERY 319
thereafter the next statement is only missing not in the database butthe rest are in the database.
319 Query Insert into tblReceiptDetails Set receiptID=2162,receiptDetailsID = 3687, outletID = 11,stockID = 3120, productID= 3007,productType = 'Imei', productQuantity = 1 , productSIQ ='359478030011584', costPrice = 257, sellingPrice = 290, profitAmount =33, profit = 'y' 
Sample 2
100612 14:15:49 780 Query KILL QUERY 766
Then the query 766 is never run successfully and the delete neverhappen.  I am very sure I never code a KILL QUERY statement in my program. So what bug is this can anyone help me on this? Thank you.

How to repeat:
There is too many cases which I can show.

Do you use any connection pool? 

Also, I'd say that this is a duplicate of bug #54642. Whatever the problem is, it is NOT a server bug. Server does NOT send KILL commands to other connection by itself. This is initiated by some client software.

Dear Valeriy,
             No all my pooling is off. Ok talking about my client program is just a C# application but from which aspect can my application send the kill query cause is quite a lot it too.The problem I have check there is no line in my program where I send the KILL QUERY. Thank you.

Are you sure there are no other applications working with the same server? Please, send two sets of results of

SHOW FULL PROCESSLIST;

with 1 minute interval in between.

Dear Valeriy,
            Let me give you a bried desription. This current machine is the service running only one application where I receive stock and also distribute stock to other outlets. Then each outlet have 3 application one is the frontend(to do sales etc),backend(to receive stock from the main server and also to transfer stock among the outlet) and another windows service program to receive update of product and also update all the receipts to the central server on a 30 minutes interval). So this main server is link to around 8 different outlets at present time.All this are running with C# so these are the applications running and I can not figure where the KILL QUERY is coming from. So for your requirement I have attached to samples of the full processlist called as sample1PL and sample2PL.

Dear Valeriy,
            I would like to add what I notice is that when run I "SHOW FULL PROCESSLIST" on some of my other outlets those which just show one line of ID is not having the kill query problem. The rest each time I run get extra of 2/3 lines even is idle and not doing anything. I even tried on my local machine when is not linked to any system is also showing 2/3 lines. One is always sleeping. I hope this gives a clearer idea of what is going wrong.

The kill might come from the query timeout (MySqlCommand.CommandTimeout parameter). 

If timeout is expired (in some network call on the client side), connector sends a "KILL QUERY", the timed out operation will also throw TimeoutException then.

Dear Valeriy,
            I have check through both my MySqlCommand.CommandTimeout and even my.cnf file there is not place I have set any time out settings. Further to that what I after going through whole of my log file I notice is this one connection is made then on average another one will kill it in 30 seconds. But then there some other cases where the query runs over few minutes and suddenly it get killed. The problem is the behaviour is not consistent which bring difficulty and bring the whole integrity of my data to a problem. What do you think must be done here? If you refer back to my sample case 1. The said query did not run for that query number but all the rest was succesfully completed into database. So the problem this "funny" scenario have many different outcomes. Hope you can look into it might be a bug some where can it be?

30 seconds you mention is consistent with default Connector/NET command timeout.

re. consistency, I do not know your application, so it is hard to advice anything here. Command timeout will throw exception, and you probably need to catch exception and do a rollback. If you want to completely avoid command timeouts, then you can  set them to a high value (MySqlCommand.CommandTimeout=100000 or something in this range)

Dear Vladislav,
              Basically I have one which is a main server where you receive goods and from there transfer to relevant outlets accordingly(Hq Main System).In the Hq Main System user can create new product, product code,employee and all the relevant administrative data. Then once transfer product from Hq will be received at the relevant outlet backend system (Outlet Backend System). Then once the product recieve it will be sold via the (Front end system) in each single outlet and one copy will be saved in the local and db and one copy be saved into a log file all the sales data. Then a windows service will run to pick and update to the Hq database on a time interval. So basicaly 2 parts one is the Hq main system then each outlet got 3 things one is the backend,fronted and update service.So that is why if you see my log file got many different connection many different database. So this my system in short. Ok I agree about the 30 seconds but why then there cases more than 30 seconds too. Where does the KILL QUERY comes from? If you look in first sample case it has KILL the query but the whole query have still been updated into the db except for one line which I have stated there. So this are there inconsistenty which I am afraid of. Thank you.

>Where does the KILL QUERY comes from?
Likely from timeout handler in connector/NET itself. Less likely, but still possible, this might come from MySqlCommand.Cancel() in your code. Even less likely it will come from somewhere else (I can imagine monitoring software that kills queries that run for too long).

Here is how timeout works: MySqlCommand.ExecuteReader catches TimeoutException from the underlying stream, handles exception by creating new connection, that sends KILL QUERY, and rethrows TimeoutException as MySqlException (which has TimeoutException as inner exception).

How to handle this exception is up to the application. You might want to issue a rollback, close connection or increase command timeouts, up to you.

Dear Vladislav,
               I dont have MySqlCommand.Cancel() so most probably is from the time handler of .net connector. There is no software of I know that kills the queries either. The problem now is that in my service program I do delete from log file local and run the query on the central server.
The problem now below is a snippet of my code how I do the distributed transaction.
First I open 2 transaction separately

try
                            {
                                connectionLocal1.Open();
                                transactionLocal1 = connectionLocal1.BeginTransaction();
                                localStatus1 = true;
                            }
                            catch (MySql.Data.MySqlClient.MySqlException ex)
                            {
                                localStatus1 = false;
                                st.Write("\n" + "PROBLEM CONNECTIN LOCAL 1" + "  " + ex.Message + "\n");
                            }
                            catch (System.Net.Sockets.SocketException ex)
                            {
                                localStatus1 = false;
                                st.Write("\n" + "PROBLEM Sockets CONNECTIN LOCAL 1" + "  " + ex.Message + "\n");
                            }

                            try
                            {
                                connectionCentral1.Open();
                                transactionCentral1 = connectionCentral1.BeginTransaction();
                                centralStatus1 = true;
                            }
                            catch (MySql.Data.MySqlClient.MySqlException ex)
                            {
                                centralStatus1 = false;
                                st.Write("\n" + "PROBLEM CONNECTIN CENTRAL 1" + "  " + ex.Message + "\n");
                            }
                            catch (System.Net.Sockets.SocketException ex)
                            {
                                centralStatus1 = false;
                                st.Write("\n" + "PROBLEM  Sockets CONNECTING CENTRAL 1" + "  " + ex.Message + "\n");

                            }
Then in between I do my processing of update central and delete from local db.
The finally this is how I do my commit

if (rollback1 == true)
                            {
                                if (localStatus1 == true)
                                {
                                    transactionLocal1.Rollback();
                                }
                                if (centralStatus1 == true)
                                {
                                    transactionCentral1.Rollback();
                                }
                            }
                            else
                            {
                                try
                                {
                                    if (localStatus1 == true && centralStatus1 == true)
                                    {
                                        transactionLocal1.Commit();
                                        transactionCentral1.Commit();
                                    }
                                    else if (localStatus1 == false || centralStatus1 == false)
                                    {
                                        if (localStatus1 == true)
                                        {
                                            transactionLocal1.Rollback();
                                        }
                                        if (centralStatus1 == true)
                                        {
                                            transactionCentral1.Rollback();
                                        }
                                    }
                                }
                                catch (MySql.Data.MySqlClient.MySqlException ex)
                                {
                                    try
                                    {
                                        if (localStatus1 == true)
                                        {
                                            transactionLocal1.Rollback();
                                        }
                                        if (centralStatus1 == true)
                                        {
                                            transactionCentral1.Rollback();
                                        }
                                    }
                                    catch (MySqlException ex1)
                                    {
                                        //MessageBox.Show("An exception of type " + ex.GetType() +
                                        //              " was encountered while inserting the data.");
                                        if (transactionLocal1.Connection != null)
                                        {
                                            //  Console.WriteLine("An exception of type " + ex1.GetType() +
                                            //                    " was encountered while attempting to roll back the transaction.");
                                        }
                                    }
                                    //Console.WriteLine("An exception of type " + ex.GetType() +
                                    //                  " was encountered while inserting the data.");
                                    //Console.WriteLine("Neither record was written to database.");
                                }
                                finally
                                {
                                    connectionLocal1.Close();
                                    connectionCentral1.Close();
                                }
                            }
Do you think can this method be giving any problem to the invoke the "KILL QUERY" too? Thank you.

I think in your case with 2 separate connections and self-made distributed transaction. I'd guess it might be a good idea to handle exceptions (including timeout exception)  from MySqlCommand.Execute... methods by trying to rollback both transactions.

Dear Vladislav,
              Do you think the method of 2 separate connections and self-made distributed transaction is risky of loosing the ACID property at any time of error? Do you have any other suggestion which you might think will not cause any bigger problem in future. Ok for instance below is my update code. So then I must set like this is it myCommandCentral1.Connection.ConnectionTimeout = 10000; then how about the catch how will the catch looks like? Thank you.

MySqlCommand myCommandCentral1 = new MySqlCommand(myReader1.GetValue(0).ToString());
                                myCommandCentral1.Connection = connectionCentral1;
                                myCommandCentral1.Transaction = transactionCentral1;
                                try
                                {
                                    myCommandCentral1.ExecuteNonQuery();
                                    st.Write("\n" + "RUNNING SCRIPT ON CENTRAL " + myReader1.GetValue(0).ToString() + "\n");
                                }
                                catch (MySql.Data.MySqlClient.MySqlException ex)
                                {
                                    rollback1 = true;
                                    st.Write("\n" + "PROBLEM RUNNING SCRIPT ON CENTRAL " + myReader1.GetValue(0).ToString() + "  " + ex.Message + "\n");
                                }
                                catch (System.Net.Sockets.SocketException ex)
                                {
                                    rollback1 = true;
                                    st.Write("\n" + "PROBLEM Sockets RUNNING SCRIPT ON CENTRAL " + myReader1.GetValue(0).ToString() + "  " + ex.Message + "\n");
                                }

The problem with self-made distributed transaction is a possible situation where you successfully commit on one connection and cannot successfully commit at the same time on another, on whatever reasons (e.g the database goes down).

But as we do not offer XA support, this is probably the only option for you.
Setting CommandTimeout to very high value helps to avoid query timeouts and exceptions assiciated with that , and "kill query".

Dear Vladislav,
               Beside this self made distributed transaction I saw some are using the method of "using transaction scope" will that help in disributed transaction to achieve ACID properties? So in your opinion what is the safest for .net connector to achieve distributed transaction. Second thing how do you catch the timeout I dont see any example on it? Thank you.

Using transaction scopes is correct in theory, however currently there is a limitation in Connector/NET that does not allow using 2 different connections under transaction scope (due to lack of XA support). So currently this won't be useful for your scenario.

Dear Vladislav,
              So what option am I left, any other better then what I am doing now? So when will the transaction scope will be supporting distributed transaction any soon? You forgot to tell me how to catch time out exception in .net connector? Thank you.

it is the best to do what you're doing now and distributed transactions are not an easy rask and it won't be implemented very soon.
catching exception is rather straightforward. Simplified version would do something like this:

try
{
  do something queries with transaction1 and transaction 2, 
  each transaction belongs to different connection.
}
catch(MySqlException e)
{
  try 
  {
    transaction1.Rollback();
  }
  catch(Exception)
  {
  }
  
  try 
  {
    transaction2.Rollback()
  }
  catch(Exception)
  {
  }
}

If you need to know exactly about TimeoutException, you can traverse the inner exception chain of MySqlException and look for TimeoutException in the chain.

I'm closing this bug, because I do not see a real bug in behavior, the "KILL QUERY" question is already answered now.

Dear Vladislav,
               Thank you for your confirmation I was thinking "using" method will solve the problem unfortunately is not is ok but only is there any way I can still improve like this. In case first one is one rollback then I will not commit second that is fine. The problem if first one committed ready then if second one have problem can I still rollback the first one? So for the timing exception I have to first capture the normal error and then in it I have to traverse for the time error thank you for that too.

My idea is like below 

try {
transaction1.Rollback();
rollback1=1;
}
catch(Exception)
{} 
try 
{
transaction2.Rollback();
rollback2=1;
}
catch(Exception){}
if(rollback1==0 && rollback2==0)
{
   try 
   {
     transaction1.Commit();
     commit1=1;
   }
   catch(Exception)
   {
    transaction1.Rollback();
    rollback1=1;
   } 
   if(commit1==1)
   {
      try 
      {
      transaction2.Commit();
      commit2=2;
      }
      catch(Exception)
      {
      transaction1.Rollback();
      transaction2.Rollback();

      rollback2=1;
      } 
   }
}

After commit, you cannot rollback anymore. Transaction is over, and the changes are stored persistently in the database.

Dear Vladislav,
              So is there anyway I can maintain the integrity and to make sure both my database are committed and completed. What my worry is that one might get committed and the other might rollback? See if you have any idea on that? About the timeout exception I can just do like this rite. Thank you once again.

catch (TimeoutException ex)
{
  rollback1=1;
}

Dear Vladislav,
              I would like to share with you one case where I notice my local transaction was completed successfully. But when I notice my centralDb and the copy of the localDb in central both are not updated due to "3667 Quit and 3668 Quit". So what possible would have made this to quit is is the network connection? This the risk of my manual distributed transaction method. Thank you. I have attached my sample log file as "quitSample1.txt".

Shai, I do not know. Perhaps, it is a network disconnect if there is nothing in your program that can cause connection to close in the middle of transaction.

Please do not get me wrong, but I think the bug database is not exactly an appropriate place to discuss your design of your application, this place here is merely to discuss valid (ideally reproducible) bugs in our software.

There are however MySQL forums, which perhaps would be a lot more useful in your case, maybe there are people around who already solved exactly the same or similar problem  to what you're trying to solve.

Dear Vladislav,
               Thank you for the confirmation. I will do appropriately as per your suggestion. I highly appreciate all your ideas. Do you think your team already have any plans to start on the distributed transaction support with the .net connector? Thank you.

We have looked at XA functionality in MySQL. Unfortunately, the functionality is limited, and some of the core pieces required to support TransactionScope is missing (for example you cannot "join" an already running transaction).

So XA in Connector/NET is not planned for now. 

But if you wish to explore it yourself without TransactionScope support, you can do it - e.g you can issue XA statements yourself, using XA statements XA BEGIN, PREPARE etc with plain old ExecuteNonQuery().

Dear Vladislav,
              Thank you for the information because I have been wandering for years and at last you have confirmed all for me. Anyway I have gone to this site http://dev.mysql.com/doc/refman/5.0/en/xa-statements.html. So if I follow this site how am I going to launch the XA statements off course it wont be via the .net connector statement or can I still use them too? Where can I see a related example to C# application I hope you can show me one will be appreciated. Thank you.

You can use XA statements, but there is no support for XA in TransactionScope.
so if you want to use this XA, you will just open a commection, create a command and issue cmd.ExecuteNonQuery("XA BEGIN") in your code.

This way you can use it in C#, but as I mentioned, high-level constructs like TransactionScope() won't work.

Dear Vladislav,
              Ok based on your help and my reading I tried something below but it gives me error as "Error	1	No overload for method 'ExecuteNonQuery' takes '1' arguments" for the 	cmd.ExecuteNonQuery("XA BEGIN") and cmd.ExecuteNonQuery("XA COMMIT"). Thank you.

using (MySqlConnection conn1 = new MySqlConnection("DataSource='localhost';Database='test';UserID='user1';Password='pw123';PORT=3306;logging=true"))
                {
                    conn1.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn1;
                    cmd.ExecuteNonQuery("XA BEGIN");
                    cmd.CommandText = "INSERT INTO tblbank VALUES(100,'test','test','y')";
                    cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery("XA COMMIT");
                }

ok, my instructions are too sketchy and do not compile..

cmd.CommandText = "XA BEGIN";
cmd.ExecuteNonQuery();

would be more helpful of course.

Dear Vladislav,
              I have tried this method too. Sorry no luck either now it tells me this error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Below is my codes

 conn1.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn1;
                    cmd.CommandText = "XA BEGIN";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO tblbank VALUES(100,'test','test','y')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "XA COMMIT";
                    cmd.ExecuteNonQuery();

Shai, the error message is correct.
If you check the manual, the syntax is "XA BEGIN xid", and it is up to you application to construct this xid correctly, according to format described in 
http://dev.mysql.com/doc/refman/5.0/en/xa-statements.html

Dear Vladislav,
              Ok I have read it is not clear with an example. What format is the xid is it string or number? For example I have tried both cmd.CommandText = "XA BEGIN 200: 21 [, 2 [, 200 ]]"; and also cmd.CommandText = "XA BEGIN 200"; both is still giving me the previous error of syntax. So then how is this going to cater for the another connection on the other distributed database? Thank you.

Dear Vladislav,

     Ok I have tried like this below but now I get error at the last statement as "XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state".

                    conn1.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn1;
                    cmd.CommandText = "XA BEGIN '200'";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO tblbank VALUES(100,'test','test','y')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "XA COMMIT '200'";
                    cmd.ExecuteNonQuery();