Bug #54642 One query killing another query.
Submitted: 20 Jun 2010 10:35 Modified: 21 Jul 2010 18:43
Reporter: newbie Shai Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:mysql-5.1.46 OS:Any (Windows XP)
Assigned to: CPU Architecture:Any

[20 Jun 2010 10:35] newbie Shai
Dear All,
        I have a very funny scenario where I notice in my log file for few different instance there is a query made and it kills a different running query.
I have attached two sample cases where 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 but the 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 never happen. 

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.
[20 Jun 2010 10:35] newbie Shai
Sample 1

Attachment: killQuerySample1.txt (text/plain), 2.54 KiB.

[20 Jun 2010 12:10] MySQL Verification Team
this is not a bug.  something runs kill query and the general query log will contain those queries. it might be some automated query killer, or some timeout option in the connector or application. ctrl-c in the mysql client can also kill query..
[20 Jun 2010 16:09] newbie Shai
Dear Shane,
          Ok I am not sure of any automated query killer of which I have implemented unless there is some settings in the database done. Yes I am using .net connector so I will post there maybe they will know it better. Thank you.
[28 Jun 2010 7:17] Tonci Grgin
Probably you just need to set longer Command timeout so that no forced kill's occur. Copying code without understanding what's going on can be dangerous. So, instead of, for example:
        cmd.CommandTimeout = 50;
        cmd.CommandTimeout = 0;
[28 Jun 2010 17:06] newbie Shai
Dear Tonci,
          So your advice is not to use the catch(TimeOutException) but to use the cmd.CommandTimeout? Which code do you mean that I copy and is dangerous I hope you can guide me on that ? Thank you.
[17 Jul 2010 4:47] newbie Shai
Dear All,
        So which settings must I use cmd.CommandTimeout = 0; or something else ? Can you explain what is the best. Because 0 means it will timeout after 0 second right is that correct? Thank you.
[19 Jul 2010 7:28] Tonci Grgin
Shai, as I already said:
 So, instead of, for example:
        cmd.CommandTimeout = 50;
        cmd.CommandTimeout = 0; <<<
[21 Jul 2010 18:43] newbie Shai
Dear Tonci,
          Why I would like to double confirm with you is that I happen to be reading from this site http://www.codeguru.com/forum/showthread.php?t=499061 this statement "DO NOT UNDER ANY CIRCUMSTANCES set it to 0. Ever. That's bad juju. Setting it to 0 will cause it to wait indefinitely. should you set it to 0, and then accidentally start a command that gets lose from you (it happens)... or run a really super intensive query, it can lock up your app, and could pose a load threat to the server." I would just like to get the final confirmation from your side will by setting it to 0 will cause other problems? Thank you.