CODEWITHSUNDEEP
phpmysqlpdo
datasn.io
datasn.io

Reputation: 12877

PHP PDO execute to abort when MySQL query taking more than X seconds?

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?

Upvotes: 4

Views: 3315

Answers (2)

Raptor
Raptor

Reputation: 54258

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.

Upvotes: 1

Ben Shoval
Ben Shoval

Reputation: 1750

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.

Upvotes: 4

Related Questions