3

Is there a feature in PHP PDO or in MySQL that automatically aborts and returns with an empty result set when it's executing more than, say, 2 seconds?

Did some searches and nothing helpful was found. I think this would probably be great for user experience, especially with trivial stats data that are not so important.

Tried to come up with a way in PHP to do this but not sure how to monitor the execution time of PDO:execute() when it's not returned yet.

Any idea?

datasn.io
  • 12,564
  • 28
  • 113
  • 154
  • Have you tried [`PDO::ATTR_TIMEOUT`](https://stackoverflow.com/questions/21403082/setting-a-connect-timeout-with-pdo)? The manual doesn't specify if that applies to queries or not but you could give it a try – Phil Oct 25 '17 at 03:21
  • You could also try this ~ https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query – Phil Oct 25 '17 at 03:28

2 Answers2

3

You can do this my setting PDO::ATTR_TIMEOUT, like this:

$db = new PDO(
    "mysql:host=$host;dbname=$dbname", 
    $username, 
    $password,
    array(
      PDO::ATTR_TIMEOUT => 10
    )
  );

This is cause a timeout after 10 seconds.

Ben Shoval
  • 1,732
  • 1
  • 15
  • 20
  • 1
    Have you tested this? It's not clear from the manual (or the linked question with the exact same answer) if this applies to queries or only the connection – Phil Oct 25 '17 at 03:25
  • @Phil I have used it in my own code for this purpose. So, yes, it's been "tested". I don't access to the questioner's database, do I have no way to test it against his specific implementation. – Ben Shoval Oct 25 '17 at 04:07
  • I meant have you tested it to check for long-running query timeouts? – Phil Oct 25 '17 at 04:25
  • @Phil Yes. In my own code I use it automatically timeout long queries. It does lead to a timeout for me on occassion. – Ben Shoval Oct 25 '17 at 04:27
  • 1
    So what happens? Does it throw an exception or something? – Phil Oct 25 '17 at 04:28
  • Add in your example the setAttribute if the user wants to have it for specific prepared statements, $pdo->setAttribute(PDO::ATTR_TIMEOUT, 10); – Stark Oct 25 '17 at 04:30
  • 1
    This does not work for me at all. Using the query from [this answer](https://stackoverflow.com/a/23319827/283366) and a timeout attribute setting of 2 seconds, does not abort the query. – Phil Oct 25 '17 at 04:37
  • @Phil That answer uses MySQLi, not PDO. Even if you used PDO, it's possible PDO::ATTR_TIMEOUT wouldn't work for you. These things depend upon the way your MySQL implementation is setup. – Ben Shoval Oct 25 '17 at 04:50
  • I said I used the query (ie, the long running one), not the answer. – Phil Oct 25 '17 at 05:40
1

There is a global variable named MAX_EXECUTION_TIME in MySQL. You can set:

SET GLOBAL MAX_EXECUTION_TIME = 1200;

Or you can use PDO library's ATTR_TIMEOUT option, which the unit is seconds (Manual here).

PDO::ATTR_TIMEOUT: Specifies the timeout duration in seconds. Not all drivers support this option, and its meaning may differ from driver to driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval. Requires int.

Raptor
  • 53,206
  • 45
  • 230
  • 366
  • But this would then also affect important queries that MUST take its time to complete? How to revert it back to its original value? – datasn.io Oct 25 '17 at 03:25
  • 1
    Yes. Or you can use `SET SESSION MAX_EXECUTION_TIME = 1200;` to specify which session to use such timeout – Raptor Oct 25 '17 at 03:26