Problem with browser timeout during mysql_query

chadman

First off, I am new to PHP/mySQL, so bear with me. I may say some things here that don't make sense. If so, please call me out on them.

I have a problem that I have seen mentioned here many times, but haven't seen any real resolution for. Please help if you can.

I am performing a mysql_query that takes a lot of time to process (minutes). On particularly large queries, while I am waiting for the results to be returned, the web browser timeout kicks in (I think), and I get a 'page not available' error. I have seen many suggestions to resolve this, such as changing max_execution_time in the php.ini, etc., but these values seem to have nothing to do with it. As proof, when attempting a long query from my home PC, the browser times out after 100 seconds, consistently. From work, attempting any long query, the browser will timeout after 200 seconds, consistently. Both browsers are IE 6.0 on WinXP. There must be some browser setting that can adjust this.

Question #1: Does anyone know how to adjust a browser timeout?

Well, even if I figure out how to adjust that, the problem will still exist for other users. I believe that I will need some type of 'hearbeat' or 'progress' page, something dumped to the browser while the query is running. This should keep the browser from timing out. Again, I have seen a few methods mentioned, but they inject and dump strings to a screen from within a script loop. I need something that will work while the mysql_query statement is being run. This means that I will not be cycling through a script, my PHP code will essentially be stuck on one line of code, the query, until said query is completed. (can anyone confirm that this is correct?).

Question #2: How do I send a 'progress' page out to the browser while mysql_query is running, to avoid a browser timeout?

I have seen mention of utilizing child processes for the query, but if that is the answer, I don't understand how that would be implemented.

Any help/comments are appreciated. Thanks.

sneakyimp

That sounds like a really gnarly query. Is there any way you can break it into smaller bits somehow? If so, you could probably report progress as each bit is completed.

If not, you will probably have to set up a PHP script that triggers some other mechanism (like a shell script) that runs your query and, when finished, outputs a signal to a text file or to your database. Your main PHP script could then forward to a PHP script that just checks to see if the action is complete and occasionally refreshes itself with a META tag. Your main PHP script could do something like this:

MAIN SCRIPT
1) set query_complete flag to FALSE in the DB or a text file
2) trigger the query mechanism in a shell script
3) forward to the wait script

WAIT SCRIPT
1) check the complete flag in the DB or text file to see if its finished.
IF complete
forward to results
ELSE
echo 'waiting on query'
echo META tag to refresh itself

chadman

Regarding #1, Browser Timeout, I have attemtping in vain to increase this timeout (from current ~100 seconds), modifying the ReceiveTimeout, KeepAliveTimeout, and other registry values. No luck. Again, I am using IE 6.0 with WinXP.

Has anyone out there had any luck changing the browser timeout for IE 6.0, or have any ideas?

#2, thanks for your response. It looks like this is where I'm headed. I have no experience in running shells (and very little in scripts). Please post a code example of how to run an SQL query in the background, go to an intermediate 'Please wait" page, and then continue to the display query results page when the query is done. I know I think if I can just get started down the right path, I'll be ok.

Thanks a million for any help you can provide. (anyone else feel free to chime in as well)

sneakyimp

#1 - Are you sure it's not a PHP timeout?

Generally speaking, when a server is in SAFE MODE (which i know little about) there is a limit on the amount of time a script can run when executed by a user accessing the script in a web browser that cannot be changed within PHP at all. I have had some scripts that time out in a browser but I can easily run them from the command line when logged in as root.

If you are trying to run your script just for yourself, try running it from the command line. Login using SSH or something and try:

php -q my_long_script.php

If the script is timing out due to your browser's end and you are expecting to try and change everyone's browser timeout, I would discourage you from trying to change their browser's behavior. If you want this to work for the general case for people visiting with their browser, I would recommend trying to find another solution.

#2 - I have no experience writing shell scripts, but I found this linux one in phpGallery:

#!/bin/sh
# $Id: configure.sh,v 1.11 2004/06/09 21:48:15 cryptographite Exp $

chmod 755 setup

if [ ! -f config.php ]; then
    touch config.php
fi

if [ ! -f .htaccess ]; then
    touch .htaccess
fi

chmod 666 config.php .htaccess

echo ""
echo "You are now in setup mode. Your Gallery installation"
echo "can be configured by pointing your web browser"
echo "to the URL to 'setup' in this directory."
echo ""

I'm not certain, but I believe shell scripts are not subject to timeouts.

Here's an approach that could get your long query to run. If your PHP script runs from the command line, you might be able to get it to run by calling it using exec() to call the php file. If you manage to write a shell script that runs the query, you can exec that shell script instead.

You would need to create two extra php pages.
-- run_script.php --

<?
// this function would set some flag in a file or database to FALSE
// i leave it up to you to specify how this might be done
set_flag_to_false();

exec("php -q my_long_script.php")
  or die("Script did not run!");

// if you had to use a shell script, you could change
// that line above to run your shell script:

//exec("long_query.sh")
//  or die("shell script did not run");

header("location: wait_script.php");
?>

-- wait_script.php --

<?
// this function would check to see if the flag is true
// IMPORTANT:  you will need to alter your long script to set the flag
// to TRUE when it finally finishes
// i leave it up to you to specify how this might be done
if (flag_is_true()) {
  echo "QUERY IS FINALLY FINISHED!  HOORAY!";
} else {
?>
<html>
<head>
<meta http-equiv="refresh" content="3;url='wait_script.php'">
</head>
<body>
WAITING FOR QUERY TO FINISH...
</body>
</html>
<?
}

?>

I'm not sure if all this is going to work....i'm really not good at the META tag stuff but I think that might work.

chadman

Thanks Sneakyimp. I feel like I'm well on my way now. I have a problem with exec() hanging, I am not sure why this is. I am not in safe mode.

Here is my flow structure.

selectquery.php <set parameters for query>
createquery.php <uses passed values from above to assemble query, then passes query to runquery.php using a $_SESSION variable>
runquery.php <using $SESSION above, calls mysql_query(); and waits for query to finish, then saves query results to $SESSION['query_result']>
showquery.php <this will display the results of the query using $_SESSION['query_result']>

If I call all of these in order (redirect to the next page after each), everything works great. What I don't like is that when runquery.php is called, the browser hangs at createquery.php, waiting for the sql query to process. I can put a "please wait" message here, but if this wait time exceeds 100 seconds, my browser times out.

I would be happy if I could show a continuously refreshed "please wait" message while waiting in createquery.php (checking the flag for query_done), or move on to a seperate waiting.php page, and check for the flag there.

Is there an easier way to accomplish this than by using an executable shell (like exec or system)? And are these commands truly shells (allowing other activity after being called) when they are used to run sql queries?

Thanks again.

sneakyimp

hm. you are still having a timeout issue. I was under the impression that you could call exec() on something and continue on your merry way without waiting for the result, but having read the documentation a bit more, I'm not so sure that's the case. your php script that calls exec() will probably hang until exec returns with something meaning that my advice won't really fly.

I don't really follow what your scripts are doing. Seems to me that 'runquery.php' should be a separate process or something. Sadly, I don't think I can solve this problem for you. We need Shrike or Weedpacket or one of the heavy hitters in here to suggest something 🙁

chadman

I can't seem to get a command line to run a PHP file, whether I try system, exec, or backtick. When I use invalid syntax, it dies immediately. But when it doesn't just die, it hangs my browser. I'm thinking the problem is that I don't know what I'm doing. I've looked thru all the doc at php.net on these commands, but nothing is helping. Sadly, if you search this baord, it is littered with people who can't get these commands to work, and often with no resolution.

A command like this:

exec("php -q http://.../runquery.php");

should work, shouldn't it? Do I have to specify the location of php.exe? I don't need any return values, runquery.php will be saving its work to SESSION vars.

I'd like to get this working first, before I start to worry about doing runquery.php in a 'SHELL'.

Any suggestions? Thanks for any help, all suggestions welcome.

sneakyimp

you shouldn't use "http://..." in your exec. you should use a relative path reference. I would recommend logging in to the server and trying it from the command line first. You might need to specify the location of php.exe.