Aborting long running DB queries with browser "Stop" button
am 06.02.2009 12:10:56 von G--000e0cd6b1a63310bd04623e1341
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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:
// print headers...
ignore_user_abort(TRUE);
system("( echo -n \"1: \" ; date ) >> /tmp/phptmplog", $rv);
print "
1
";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 "
2
";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 "
3
";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 "
4
";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?
--000e0cd6b1a63310bd04623e1341--