3

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 sent 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

Muaz Mian
  • 45
  • 1
  • 4
  • Well, not sure how can it help, but you can issue 'kill *process_id*' command if your db user allowed to do such a trick. But you have to know somehow the *process_id*, and that's not possible unless you perform some dirty tricks, like appending *user_id* as a comment to each sql or something like that. And yes, thats could be unsafe, you could kill connection that started by some other user or pose some other security implications. – J0HN Aug 16 '11 at 12:20
  • phpmyadmin does it...it should be possible... – Catalin Aug 16 '11 at 15:15

4 Answers4

4

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

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
  • I was already using LIMIT 1, but I just wanted look for a way to actually kill a query, never the less I will keep on using it :) one question though.. is there any difference between LIMIT 1 or LIMIT 0? – Muaz Mian Aug 17 '11 at 06:37
  • 1
    There is difference, as with LIMIT 0 MySQL won't actually execute the query (the optimizer will tell impossible where clause) and no rows will be actually read from the disk. In any way it won't be slower then limit 1, it can only be faster :) How much faster - best check, as it depends on a lot of conditions – Maxim Krizhanovsky Aug 17 '11 at 08:25
1

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.

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Thanks, but I can't risk on killing the complete process it was just an additional requirement so I wanted to see if there is an easy way to do it. – Muaz Mian Aug 17 '11 at 06:41
  • eh? The thread is only ever going to run the one instance of the query - the risk is that a subsequent request may create a thread using the same thread_id. And you're not "killing the .... process" you're asking mysqld to terminate the query. – symcbean Aug 17 '11 at 09:03
  • hmm.. interesting.. I will take a closer look at the code thanx :) – Muaz Mian Aug 23 '11 at 09:28
0

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

flaschenpost
  • 2,205
  • 1
  • 14
  • 29
0

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)

genesis
  • 50,477
  • 20
  • 96
  • 125