Killing a MySQL query during execution with PHP and AJAX

I am creating a custom query builder, when user has created his query he can verify the query syntax by clicking a button. When the user click on the button to verify, An AJAX call is 开发者_如何学Csent to the server with the query and execution of the query starts, during this time user can see a modal on his screen with a cancel button. If by any chance user clicks on the cancel button I want the send another AJAX call to the server to kill the execution of the query.

Currently I am only able to kill the AJAX call which I originally send and my page works fine at user end

but I am looking for a PHP code to stop the MYSQL query at server side because some queries can be quite heavy and run for a long time

First of all, if the purpose of the query is just to check the syntax, do not execute it! execute explain, or add limit 0, or execute against empty database.

As for the killing, you have to connect to database with root privileges, and issue KILL command (but you need to know the query id). Or you can kill the entire thread. Take a look to mysqli::kill

Edit: seems you don't need root privileges, to see queries by your user, use SHOW PROCESSLIST command

I believe that thread_id reported by CONNECTION_ID() is the same as the thread id used in mysqladmin....

You'd need to capture the process id for the connection before starting the query...

$qry="SELECT CONNECTION_ID() AS c";
$row=mysql_fetch_assoc(mysql_query($qry));
$_SESSION['mysql_connection_id']=$row['c'];

then when the user clicks the button do something like....

exec('mysqladmin -u $user -p$password killproc ' 
          . $_SESSION['mysql_connection_id']);

But this opens up the possibility of users killing other peoples' queries. You could inject a comment key, e.g. the php session id, at the beginning of each query (mysqladmin truncates text of the query) then use mysqladmin processlist to check the ownership and/or retrieve the thread id before killing it.

No. It's not possible, as you're closing your connection everytime executions ends. There's no way (through PHP) to control old "sessions" (connections)

You would have to allow the application to kill database queries, and you need to implement a more complex interaction between Client and Server, which could lead to security holes if done wrong.

The Start-Request should contain a session and a page id (secure id, so not 3 and 4 and 5 but a non-guessable but unique hash of some kind). The backend then connects this id with the query. This could be done in some extra table of the database, or a redis-way if you have redis anyway, but also via comments in the SQL query, like "Session fid98a08u4j, Page 940jfmkvlz" => s:<session>p:<page>.

/* s:fid98a08u4jp:940jfmkvlz */ select * from ...

If the user presses "Cancel", you send a Cancel-request with session and page id to the server. The php-code then fetches the list of your running SQL Queries with show processlist and searches for session and page to extract the query id.

Then the php sends a

kill query <id>

to the MySQL-server.

This might lead to trouble when not using transactions, and this might damage replication. And even a kill query might take some time in the state 'killing'.

So this should be the last possible of several variants. But sometimes it has to be done, I even had once a program where you could list your own running queries to kill them, which was needed because of "quotas" (you could not run more than two or three reporting requests at the same time).