Stop query through pdo

Question

I am using PDO, and the user should have the option of stop the request that he previous triggered.

For example, Now I click generate a report, however after the request I forgot to select one more field that makes the report useless. So I need to cancel the request, and make a new one.

Basically, how can I cancel the MYSQL query that is running?

if (!isset($_POST['cancel_request'])) { 
    //query
}else{
    //the user cancel the query
}

I know that I can use the kill command and the PID process, but this should running through PDO, and I don't know what is the PID.

Answer 1

The main problem here is to share PID between your async request that generates the report and the script that should stop it.

You can get your PID using:

    $stmt = $dbh->prepare("SELECT CONNECTION_ID()");
    $stmt->execute();
    $pid = $stmt->fetchColumn();

And you can use something like php-shared-memory to create a shared variable between your scripts. If you're not using Composer for your project dependancies, this library has a standalone release (1.5.0, here).

Implementation sample:

<?php

if (!include __DIR__ . '/vendor/autoload.php')
{
    die('You must set up the project dependencies.');
}

use Fuz\Component\SharedMemory\SharedMemory;
use Fuz\Component\SharedMemory\Storage\StorageFile;

// your intializations here

$storage = new StorageFile("/tmp/shared.{$user_id}.sync");
$shared = new SharedMemory($storage);

if (!isset($_POST['cancel_request']))
{
    $stmt = $dbh->prepare("SELECT CONNECTION_ID()");
    $stmt->execute();
    $pid = $stmt->fetchColumn();

    $shared->pid = $pid;

    // your long query here

    $shared->destroyStorage();
}
else
{
    // kills pid
    $pid = $shared->pid;
    if (!is_null($pid))
    {
        $dbh->exec("KILL {$pid}");
    }
}

Answer 2

As others mention, you can log in to your MySQL server, issue SHOW PROCESSLIST, locate the number for the query you want to abandon, and then issue a KILL number command.

It seems like you want this function to be self-service for your report-designing users, rather than having them call somebody in your net ops and ask them to do it, or teach them to use an administration tool.

I've done this sort of thing before. It takes three parts.

First, you need to arrange to insert a tag into the kind of queries your users might want to abandon. For example, if your user is doing this

SELECT cust_id, SUM(orders), COUNT(orders)
  FROM big_fat_table
 GROUP BY cust_id

you'll need to change the text of the query so it has a tag embedded in a comment, for example like so.

SELECT /*report:user2290084*/ cust_id, SUM(orders), COUNT(orders)
  FROM big_fat_table
 GROUP BY cust_id

Notice this tag has two items in it: report: and a user id. The user id needs to be something that does not relate to the connection upon which the query is run -- that connection will be tied up if you're trying to abandon the query running on it. The tag doesn't affect the execution of the query at all. It just shows up in the server's process table.

Second: your PDO code that runs those queries is going to need good and user friendly exception handling, because having the queries blow up underneath your code will become routine. You're going to need to experiment with that to get this abandon-query feature to be useful to your users.

Third: issue this query when you need to abandon the report query for user2290084

select id from information_schema.processlist where info like '%/*%report:user2290084*/%'

It looks through the process list to find the process id for a query with the appropriate tag. Then issue

kill <<that process id>>

and you've abandoned the query.

In php, it might look like this:

$q = 'select id from information_schema.processlist where info like :tag';
$tag = '%/*report:' . $userid . '*/%'
$stmt = $dbh->prepare($q);
$stmt->bindParam(':tag', $tag);
$stmt->execute();
$pids = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

foreach ($pid in $pids) {
    $dbh->query('KILL ' . $pid);
}        

Notice there's a loop in this code. It's possible that you will have more than one report query running for this user, or none.