1

I am beginner to MySQL and SQL and I am learning these technologies from MySQL and MyISAM websites.

I am learning the KILL command now and have learned how to use KILL CONNECTION. But when I come to learn KILL QUERY from MySQL and MyISAM websites where they simply provided information about KILL QUERY that they terminate query that the specified connection id is executing.

Other user (not me) executes INSERT and DELETE queries but when i have used KILL QUERY by getting the connection id of other user via SHOW PROCESSLIST command, it says

Query OK, 0 rows affected (0.01 sec)

But nothing happen to other user.

I just want to know that what is the exact use of this command. What is the purpose of this KILL QUERY command. Can anyone tell me?

Martin
  • 22,212
  • 11
  • 70
  • 132
  • Imagine you have some legacy database processes with race conditions so every now and then users will run queries that get stuck because of some other user has locked the table/record they need to access. Having a mechanism to kill the original problem query causing the locks is *very* important to any RDBMS. –  May 09 '17 at 19:20

1 Answers1

2

Some useful pointers:

How can I stop a running MySQL query?

MySQL Kill query

How to kill all processes for a Specific user

MySQL Manaul - Kill

You need to have some clarity here KILL will kill the connection, but KILL QUERY will only kill that query and will leave that connection intact.

Really, if you are using KILL QUERY on another users command, this can only really be noticable if their query is running for longer than your Kill Query command, so if it's a slow query, because [most] queries will execute and close within a fraction of a second, so by the time you've KILLed the other users query, their query may well have completed and closed anyway.

I suspect this is what you are seeing, that you're trying to kill a query that closes and is finished before the kill command can execute.

Killing queries seems (from literature) only useful for slow and long running queries, and why this is so should be obvious from the above.

Edit:
(I had this ready to post on my original answer and then took it out as being irrelevant, but seems to be more relevant now due to comments!)

From the MySQL Manual:

Note

You cannot use KILL with the Embedded MySQL Server library because the embedded server merely runs inside the threads of the host application. It does not create any connection threads of its own.

So to answer your question in the comment, I think that you may be running MySQL embedded so your kill calls will not function.

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132
  • thank you for your great words. I have inserted 5000 records to database table and then i have executed the SELECT command to retrieve all the 5000 rows from that table from session A and on that same moments when the 5000 records were retrieving for session A i have executed KILL QUERY command from session B with the session A connection_id which says "Query OK, 0 rows affected (0.01 sec)" but session A SELECT query were still retrieving the records without killing the query. In fact session A is running long query. Why is that happening can you tell ? –  May 09 '17 at 22:45
  • @SunnyKhan I added an update to my answer. If you're sure that you're ***not*** running MySQL embedded and it's still not killing the process, then I'm not sure why that would be .... – Martin May 09 '17 at 23:15
  • @SunnyKhan it may also be a permissions issue, too, I can't say as I don't know enough about your setup, but [this link](http://stackoverflow.com/a/26659484/3536236) might be useful to you ? – Martin May 09 '17 at 23:17
  • Thank you @Martin. Can you share some links that helps me to read that what kind of queries are considered as "Long Running Queries". –  May 09 '17 at 23:19
  • @SunnyKhan [this post](http://stackoverflow.com/questions/21030690/mysql-innodb-and-long-running-queries) may be useful to read, although personally I think long running queries are usually slow because the tables they are running on are [inefficiently indexed](https://webmonkeyuk.wordpress.com/2010/09/22/what-makes-a-good-mysql-index-part-1-column-size/), or because the [query itself](https://dba.stackexchange.com/questions/71469/how-to-make-the-sql-query-more-efficient) is inefficient ad verbose. I hope this helps you `:-)` – Martin May 09 '17 at 23:23
  • According to my knowledge i have read that KILL command requires SUPER privilege to KILL other threads and statements and i have SUPER privilege but let me inspect your link too may be it helps. –  May 09 '17 at 23:24
  • @SunnyKhan the MySQL manual states that KILL check is only checked when the `SELECT` statement is in `ORDER BY` or `GROUP BY` so if your select is slow and is just reading rows then it won't know it will be killed, until it is finished reading the rows (and moved on to ordering results, etc.) – Martin May 09 '17 at 23:28
  • Yes, i was also thinking about that because simple SELECT statement is not considered as LONG RUNNING QUERY. May be when multiple complicated conditions and clauses are exist in the query so it will be considered as long running query. –  May 09 '17 at 23:31
  • @SunnyKhan I really do not know much about long running queries. I can suggest that you can look up questions on [Database StackExchange](https://dba.stackexchange.com/) for more specific SQL stuff `:-)` – Martin May 09 '17 at 23:32
  • Selecting 5000 rows may take very little time to execute, then a lot of time to send "across the wire". Could it be that the select had finished, but the transfer had not? – Rick James May 10 '17 at 01:09
  • 1
    Here's a way to experiment: On session A, do `SELECT SLEEP(30);` On session B, do some sort of `KILL` and see what happens. on A. – Rick James May 10 '17 at 01:10