How do I stop a running mysql query programmatically?
如何以编程方式停止正在运行的mysql查询？
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.
我面临的问题是使用用户提供的数据构建查询，并且可能偶尔需要很长时间才能执行（大表15到3000万行）。我想为用户提供一个取消选项，但不知道如何停止当前正在执行的mysql查询。
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.
该应用程序是一个Java applet-servlet：用户在applet中指定条件，该条件传递给servlet，在servlet中创建处理程序类来处理请求。这个handler-class是自包含的re connect - query - disconnect to mysql。
Given this scenario, how do I cancel a running mysql-query?
鉴于这种情况，如何取消正在运行的mysql查询？
3 个解决方案
#1
40
SHOW PROCESSLIST;
KILL <thread_to_be_killed>;
Refer to the documentation for additional details
有关其他详细信息，请参阅文档
显示进程列表
杀死正在运行的线程
#2
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.
需要单独的JDBC连接，这意味着您必须创建新的数据库处理程序实例。执行SHOW PROCESSLIST语句，然后遍历结果并为给定用户找到的每个线程ID执行KILL语句。
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.
我能想到的唯一可靠的方法是每个用户都必须使用不同的用户名登录数据库，然后获取该用户的所有线程ID并终止所有线程。
#3
0
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.
您可以通过MySQL提供的标准库发出MySQL特定命令并运行KILL QUERY。可能最好列出流程并找出你需要首先杀死的线程。
Depending on your application this might create a security issue though (since you need to give more privileges to the connecting application user).
根据您的应用程序，这可能会产生安全问题（因为您需要为连接的应用程序用户提供更多权限）。
#1
40
SHOW PROCESSLIST;
KILL <thread_to_be_killed>;
Refer to the documentation for additional details
有关其他详细信息，请参阅文档
显示进程列表
杀死正在运行的线程
#2
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.
需要单独的JDBC连接，这意味着您必须创建新的数据库处理程序实例。执行SHOW PROCESSLIST语句，然后遍历结果并为给定用户找到的每个线程ID执行KILL语句。
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.
我能想到的唯一可靠的方法是每个用户都必须使用不同的用户名登录数据库，然后获取该用户的所有线程ID并终止所有线程。
#3
0
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.
您可以通过MySQL提供的标准库发出MySQL特定命令并运行KILL QUERY。可能最好列出流程并找出你需要首先杀死的线程。
Depending on your application this might create a security issue though (since you need to give more privileges to the connecting application user).
根据您的应用程序，这可能会产生安全问题（因为您需要为连接的应用程序用户提供更多权限）。