Aborting long running DB queries with browser "Stop" button

[G <tjmadwja@xxxxxxxxx>]

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 " Long running db queries and the "STOP" button"
thread from the end of 2005, but my problem remains.

Help! Please?