How to stop a running mysql query

Question

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?

Answer 1

SHOW PROCESSLIST;
KILL <thread_to_be_killed>;

Refer to the documentation for additional details

Show The Process List

Kill A Running Thread

Answer 2

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).

Answer 3

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.