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