How can I make PHP scripts timeout gracefully while waiting for long-running MySQL queries?
- by Mark B
I have a PHP site which runs quite a lot of database queries. With certain combinations of parameters, these queries can end up running for a long time, triggering an ugly timeout message. I want to replace this with a nice timeout message themed according to the rest of my site style.
Anticipating the usual answers to this kind of question:
"Optimise your queries so they don't run for so long" - I am logging long-running queries and optimising them, but I only know about these after a user has been affected.
"Increase your PHP timeout setting (e.g. set_time_limit, max_execution_time) so that the long-running query can finish" - Sometimes the query can run for several minutes. I want to tell the user there's a problem before that (e.g. after 30 seconds).
"Use register_tick_function to monitor how long scripts have been running" - This only gets executed between lines of code in my script. While the script is waiting for a response from the database, the tick function doesn't get called.
In case it helps, the site is built using Drupal (with lots of customisation), and is running on a virtual dedicated Linux server on PHP 5.2 with MySQL 5.