PHP's PDO is ignoring the ATTR_TIMEOUT option for MySQL when server cannot be reached

Question

I'm testing scenarios where the mysql server cannot be reached by putting in a random IP to try to connect to. I set PDO's options to time out after one second using PDO::ATTR_TIMEOUT => 1. However, it still takes 30 seconds to throw an exception. I'm guessing this timeout only applies to the actual mysql connection time, not to the server on which mysql is running.

What PHP options do I need to change to time out the connection to the mysql server?

Answer 1

Just put

ini_set("default_socket_timeout", 2);

before your PDO() connect string.

(Tested on Windows, should also be fine on Linux.)

---

Why?

Chasing this through the manual:

The mysqlnd driver uses sockets for the underlying connection, and that to set timeouts you need to use the socket (stream) timeout functions. (Ref: http://php.net/manual/en/mysqlnd.notes.php)

Using mysqlnd means using PHP streams for underlying connectivity. For mysqlnd, the PHP streams documentation (Streams) should be consulted on such details as timeout settings, not the documentation for the MySQL Client Library.

---

If you want more control, then you might be able to control more specifically the actual socket: I've not tested this as it's unix only. To set the socket mysqlnd uses, you can specify the socket using ini settings (Ref: http://php.net/manual/en/ref.pdo-mysql.connection.php)

If PDO_MYSQL is compiled against mysqlnd a default socket can be set thru the pdo_mysql.default_socket setting.

See http://php.net/manual/en/ref.pdo-mysql.php#ini.pdo-mysql.default-socket about that setting

You might be able to then set the timeout using http://php.net/manual/en/function.stream-set-timeout.php

But probably easier to set default and then reset once you're done...

Answer 2

On the php.ini you can update this config variable:

mysql.connect_timeout = 1

Answer 3

Pass the options to the PDO constructor:

https://github.com/phalcon/cphalcon/blob/2.0.0/phalcon/db/adapter/pdo.zep#L135-L149

Answer 4

For me, if you put the option in the constructor of PDO, it works. And ignores any settings in php.ini

$options = array(PDO::ATTR_TIMEOUT => 1); // must be in the constructor
$db = new DB($dsn, $username, $password, $options);

Since here we are testing the initial connection, it makes sense that it needs to have this option, when opening the connection. So below should work for any queries AFTER the initial connection.

$db = new DB($dsn, $username, $password, $options);
$db->setAttribute(PDO::ATTR_TIMEOUT, 1);

Answer 5

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.

The document clearly stated that it might not mean a connection timeout interval or sometimes driver does not support it.

Answer 6

    $host = '192.168.1.36';
    $dbname = 'test';
    $username = 'test';
    $password = '';

    $start = time();
    try {
        $db = new PDO(
            "mysql:host=$host;dbname=$dbname", 
            $username, 
            $password,
            array(
                PDO::ATTR_TIMEOUT => 1,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            )
        );
    } catch(Exception $e) {
        echo time() - $start;
        echo "\n";
        echo $e->getMessage();
    }

This code snippet should solve your problem.

difference of mysqli, mysqlnd, pdo-mysql:

• (deprecated) mysqlnd means msyql native driver which functions are procedural
• mysqli object form of mysql diriver
• pdo-mysql is plugin for pdo database abstraction to support connecting to mysql database.