No announcement yet.

Automatic KILL QUERY

  • Filter
  • Time
  • Show
Clear All
new posts

  • Automatic KILL QUERY

    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 check into my application there is no place I have "KILL QUERY" what is the possible of this happening? Any idea? Thank you.

  • #2
    It's not automatic, something or someone is actually executing a KILL.


    • #3
      Dear Xaprb,
      I am using .net connetor in my C# application but none from application is the "KILL QUERY" that I am very sure. Besides what I notice is that even though I have a fresh installation of mysql and not any system linked to it when I do a "SHOW FULL PROCESSLIST" some houw there will 2 or 3 connections sleeping? Any reasons?


      • #4
        I think the best thing to do would be to enable the general query log so you can find out who/what is running KILL.


        • #5
          Dear Xarpb,
          Yes I already enable the log is from that log I saw the KILL Query statements. Is there I am lost as I dont know why there is one query started then one query kill the former query. I can post some samples of it maybe can give you some clue? Thank you.


          • #6
            Try searching your C# code for things like .Cancel()

            Maybe your code is abandoning a query without fully understanding the impact on the server.

            Sleeping connections in the processlist are normal. They will terminate if they arent used for a while


            • #7
              Dear Carpii,
              I have seach my whole code there is no place I call the .Cancel(). I dont get when you say "Maybe your code is abandoning a query without fully understanding the impact on the server." Do you want me to attach a snippet of the log file?


              • #8
                Sure, attach a bit of the log. Maybe it will help

                >> I dont get when you say "Maybe your code is abandoning a query
                >> without fully understanding the impact on the server."

                I just meant that, if you're sure nobody is running KILL manually, then maybe its a side effect of your code, or the .NET connector, which is generating the KILL without you realising

                I don't know whether .Cancel() would do such a thing, but it was worth checking )


                • #9
                  Dear Carpi,
                  Below is sample of my log file.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'

                  100606 15:32:42 319 Connect root@ on mpcms11 319 Query SET autocommit=0100606 15:32:43 319 Query SHOW VARIABLES100606 15:32:44 319 Query SHOW COLLATION100606 15:32:45 319 Query SET character_set_results=NULL100606 15:32:48 319 Init DB mpcms11100606 15:33:27 319 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 319 Query BEGIN100606 15:33:28 319 Query Update tblID Set tblID.lastValue = lastValue + 1 Where tblID.tableName='tblReceipt'100606 15:33:29 319 Query Insert into tblReceipt Set receiptID=2162, outletID=11, employeeID=26, customerName='Cash', customerPhone='', totalCostAmount=257, totalSellingAmount=290, totalProfitAmount=33, changeAmount=0, profit='y', receiptDate = '2010:06:06', receiptTime = '15:18:34' 320 Connect root@ on mpcms11 320 Query SET autocommit=0100606 15:33:30 320 Query SHOW VARIABLES100606 15:33:31 320 Query SHOW COLLATION100606 15:33:32 320 Query SET character_set_results=NULL 319 Query Update tblID Set tblID.lastValue = lastValue + 1 Where tblID.tableName='tblReceiptDetails' 320 Init DB mpcms11 319 Query Update tblStock Set tblStock.stockStatus = 's' Where tblStock.stockID=3120100606 15:33:33 320 Query KILL QUERY 319 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'100606 15:33:34 320 Quit 319 Query Update tblProduct Set tblProduct.productTotalStock = tblProduct.productTotalStock -1, tblProduct.productTotalAmount = tblProduct.productTotalAmount -257 Where tblProduct.productID=3007100606 15:33:37 319 Query Insert into tblTransaction Set transactionID = 2162, transactionDetailsID=3687, stockID = 3120, productID= 3007, outletFromID = 11, outletToID = 11, totalAmountBefore = 514, totalAmountAfter = 257, totalQuantityBefore = 2, totalQuantityAfter = 1, averageCostBefore = 257, averageCostAfter = 257, quantity = 1, costPrice = 257, transactionPrice = 290, transactionEmployeeID = 26, transactionDate='2010:06:06', transactionTime='15:18:34', transactionType = 's' 319 Query Update tblID Set tblID.lastValue = lastValue + 1 Where tblID.tableName='tblPaymentDetails'100606 15:33:40 319 Query Insert into tblPaymentDetails Set paymentDetailsID=2354, outletID = 11,receiptID = 2162, paymentTypeID= 1, amount = 290100606 15:33:41 319 Query COMMIT100606 15:33:42 319 Quit


                  • #10
                    Because of all the bumph when the connection is opened, I would guess this is being done by some gui tool, or maybe by a connectivity library (as opposed to someone logging to mysql from the command line).

                    The 320 connection opens when the 319 INSERT has been running, so maybe it is taking a while and someone has opened a database manager to terminate the process?
                    Its impossible to say what is causing it really.

                    If I were you, Id start by assigning a unique login to each software accessing the server (this includes your application, but also any database gui should have a seperate login, so its easier to monitor).

                    This is standard practice, and even if you dont want to do this, letting all software log in as mysql root is really not a great idea.
                    Give each user account only the minimum privileges it needs in order to function properly. Your problem will become much clearer this way I think


                    • #11
                      check for any cron job also


                      • #12
                        Dear Yogesh,
                        No there is no any cron job as I am not running it on a server.What else can be a mistake is the commandtimeout? Thank you.