how to get/set timeout for MySQL PDO connection

sneakyimp

I have a few automated scripts that access a database table, sometimes locking it in the process. One of these is a multithreaded daemon process that loops infinitely, performing tasks dictated by records in the database. Occasionally, one of the less frequent processes locks up a particular db table and the daemon process has its db connection timeout, causing the script to crash. I'm hoping to fix the script so that it will be a bit more robust, but I would also like to make it more patient when the database is busy. That is to say I want to make the script very tolerant of slow behavior from the db server. I.e., I want to increase the timeout for queries.

I'm using PDO/MySQL to access the database and as best I can tell, PDO::ATTR_TIMEOUT seems to be the value I'm interested in. Do I have that right? The docs are really vague about what this value means.

Anyways, it appears to have a default value of 2 seconds -- which seems really short. Having watched the log file while my script was experiencing such a crash, it seemed to me that the timeout was considerably longer -- e.g. 30 seconds or so.

Also, the docs are very vague on the parameters available for the various PDO drivers. The 4th parameter of PDO::__construct is for $driver_options but the docs otherwise give no detail, simply saying that it is:

A key=>value array of driver-specific connection options

The MySQL-specific PDO page doesn't mention any timeout-specific values nor does it indicate whether one might use these driver-specific options in the $driver_options array.

Weedpacket

PDO::ATTR_TIMEOUT is just a named constant - it's actual value isn't significant, except to distinguish it from the other named constants that might be used (PHP doesn't have enums), it's name is what's important.

You use it with [man]PDO::getAttribute[/man] and [man]PDO::setAttribute[/man] to get the current timeout value or set a new one respectively.

sneakyimp

Aha! OK that clears up why I was getting a value of 2 on every machine when I checked its value. I was thinking it was a static variable or something. My office must be covered in lead paint or something. My mind is slipping.

sneakyimp

Well I tried getAttribute and got an error:

<?php
$dsn = "mysql:host=localhost;dbname=chump";
$user = "foo";
$pass = "bar";

try {
  $pdo = new PDO($dsn, $user, $pass);
  echo "success\n";
  $to = $pdo->getAttribute(PDO::ATTR_TIMEOUT);
  echo "timeout $to\n";
} catch (Exception $e) {
  die("Exception:" . $e->getMessage());
}
?>

The error:

PHP Warning:  PDO::getAttribute(): SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute in /home/jaith/biz/valunite/ImageDaemon/chump.php on line 11
PHP Stack trace:
PHP   1. {main}() /home/jaith/biz/valunite/ImageDaemon/chump.php:0
PHP   2. PDO->getAttribute() /home/jaith/biz/valunite/ImageDaemon/chump.php:11

I suppose that's what the docs mean by "Note that some database/driver combinations may not support all of the database connection attributes. "

And, somewhat curiously, this script works, simply outputting "success\n"

$dsn = "mysql:host=localhost;dbname=chump";
$user = "foo";
$pass = "bar";

try {
  $pdo = new PDO($dsn, $user, $pass);
  $pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
  echo "success\n";
} catch (Exception $e) {
  die("Exception:" . $e->getMessage());
}

:queasy:

johanafm

sneakyimp;10994825 wrote:
Well I tried getAttribute and got an error:
(…)
And, somewhat curiously, this script works, simply outputting "success\n"

Have you tried this?

SHOW VARIABLES
WHERE Variable_name='connect_timeout'

Not that it matters if you wish to set it to a particular value, but this should retrieve the current one as I understand it.