24

How do I stop a running mysql query programmatically?

The problem I'm facing is that a query is constructed using user supplied data, and may occasionally take a very long time to execute (large tables 15 - 30 million rows). I want to offer the user a cancel-option, but don't know how to stop the currently executing mysql-query.

The application is a Java applet-servlet: the user specify criteria in the applet which is passed to the servlet where a handler-class is created to handle the request. This handler-class is self-contained re connect - query - disconnect to mysql.

Given this scenario, how do I cancel a running mysql-query?

slashmais
  • 7,069
  • 9
  • 54
  • 80
  • Do it carefully, if you're killing a query will crash your table – vkGunasekaran Sep 21 '11 at 11:35
  • @slashmais can you help me with the code you have user to stop or kill the query using java-servlets, i also have same kind of issue –  Mar 15 '19 at 09:59
  • @dheerajkumar: I wrote the code for a client & do not have access to it anymore. IIRC I read through the answers and constructed a scheme that worked. – slashmais Mar 16 '19 at 14:36
  • @slashmais yupp i also have to do something else because this is causing issues –  Mar 16 '19 at 15:17

3 Answers3

40
SHOW PROCESSLIST;
KILL <thread_to_be_killed>;

Refer to the documentation for additional details

Show The Process List

Kill A Running Thread

Community
  • 1
  • 1
George Johnston
  • 31,652
  • 27
  • 127
  • 172
  • using KILL QUERY is slightly preferable to KILL for this case. That way the query is killed, but not the connection ref# the comment in "http://stackoverflow.com/questions/3787651/how-can-i-stop-a-running-mysql-query" – Chris Sim Jan 31 '14 at 07:20
2

A separate JDBC connection is required, which means you'll have to create a new database handler instance. Execute SHOW PROCESSLIST statement and then loop through the results and execute a KILL statement for each thread id found for the given user.

The only reliable way I can think to do this is every user would have to log in to the database under a different user name, then get all thread id's for that user and kill all their threads.

GuiGS
  • 2,070
  • 1
  • 19
  • 18
Mark
  • 16,772
  • 9
  • 42
  • 55
1

You can issue MySQL specific commands through the standard libraries provided by MySQL and run KILL QUERY. Possibly it's best listing the processes and figuring out which thread you need to kill first.

Depending on your application this might create a security issue though (since you need to give more privileges to the connecting application user).

mtmk
  • 6,176
  • 27
  • 32