Aborting long running DB queries with browser "Stop" button

[email protected] (G)
Newsgroups php.db
Message-ID <[email protected]>
Hello,

I've run into a brick wall implementing functionality in my PHP scripts to
detect when the user has hit the "Stop" button so that I can abort my long
running DB queries. Apache 2.2.9, PHP 5.2.6-5 with Suhosin-Patch, MySQL
5.0.67 running on a Debian unstable machine.

As a simple test case, I wrote this script (edited for brevity) to figure
out how PHP scripts are terminated:

<?php
// print headers...

ignore_user_abort(TRUE);

system("( echo -n \"1: \" ; date ) >> /tmp/phptmplog", $rv);
print "<p>1</p>";
sleep(3);
flush();
if (connection_aborted()) {
    system("( echo -n \"Aborted 1: \" ; date ) >> /tmp/phptmplog", $rv);
    exit(0);
}

system("( echo -n \"2: \" ; date ) >> /tmp/phptmplog", $rv);
print "<p>2</p>";
sleep(3);
flush();
if (connection_aborted()) {
    system("( echo -n \"Aborted 2: \" ; date ) >> /tmp/phptmplog", $rv);
    exit(0);
}

system("( echo -n \"3: \" ; date ) >> /tmp/phptmplog", $rv);
print "<p>3</p>";
sleep(3);
flush();
if (connection_aborted()) {
    system("( echo -n \"Aborted 3: \" ; date ) >> /tmp/phptmplog", $rv);
    exit(0);
}

system("( echo -n \"4: \" ; date ) >> /tmp/phptmplog", $rv);
print "<p>4</p>";
sleep(3);
flush();
if (connection_aborted()) {
    system("( echo -n \"Aborted 4: \" ; date ) >> /tmp/phptmplog", $rv);
    exit(0);
}

// print headers...
?>

This script continues to execute a while after the browser terminates the
connection (lines are written to /tmp/phptmplog even though connetion is
closed); if I hit "Stop" after getting "1" in my browser the /tmp/phptmplog
file will have entries for "1: Fri Feb 6...", "2: Fri Feb 6...", "3 Fri Feb
6...", and "Aborted 3: Fri Feb 6...". But at least the script doesn't run
all the way through.

With ignore_user_abort(TRUE); I get an "Aborted" line meaning that
connection_aborted() has returned true, while with ignore_user_abort(FALSE);
the script stops writing to /tmp/phptmplog without writing an "Aborted"
line.

Using this knowledge I want to do something like this in my DB query script:

// initialize...
$a = mysql_connect('db', 'user', 'pwd', true);
$tid = mysql_thread_id($a);
$b = mysql_connect('db', 'user', 'pwd', true);
$r = mysql_unbuffered_query($potentially_slow_query, $a);
while (true) {
    $p = poll_mysql($r); // poll_mysql() doesn't exist!
    if (HAS_DATA == $p) {
        $row = mysql_fetch_row($r);
        handle_row($row);
    } else if (NO_MORE_DATA == $p) { // could also check mysql_fetch_row()
== FALASE
        break;
    }
    print " "; // ugly way to get some I/O going to detect closed connection
    flush(); // make sure I/O happens
    usleep(200); // don't waste too many CPU cycles; also gives us time to
detect that flush() failed sending data
    if (connection_aborted()) {
        mysql_query("KILL $tid", $b);
        break;
    }
}
// clean up...

But obviously this doesn't work as I have found no way to poll() unbuffered
mysql queries.

So my question is quite obvious: how do I solve this problem? Surely I can't
be the first one running into it, someone else should already have
encountered it and found a solution, but I can't find it by searching.
Perhaps I'm just not asking Google clever enough questions...

I've already optimized the DB queries, but a few cases are
hard-to-impossible to speed up so now I want to give users who
"accidentally" issue these queries a chance to abort.

I've done extensive web searches, read all of the PHP manual pages beginning
with mysql_, wasted days on trial-and-error experimenting (signal handling
etc.), seen the "[PHP-DB] Long running db queries and the "STOP" button"
thread from the end of 2005, but my problem remains.

Help! Please?
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.