C and PHP MySQL clients can set a connection timeout before a connection is established to MySQL. The MySQL C API manual states about MYSQL_OPT_CONNECT_TIMEOUT , which is equal to PHPs MYSQLI_OPT_CONNECT_TIMEOUT:
MYSQL_OPT_CONNECT_TIMEOUT
Connect timeout in seconds.
From: http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html
That is half of the story. The actual behaviour depends on the library you use, the operating system and the transport protocol (TCP/IP, Unix domain sockets, Windows named pipes, Shared Memory).
C users can choose between the MySQL Client Library, which ships with the MySQL server, and the MySQL Connector/C, a new standalone version of the client library. PHP users have a third library option. PHP users can also choose to use the MySQL native driver for PHP (mysqlnd). All three libraries create TCP/IP connections differently resulting in different behaviour.
This article focusses on PHP and TCP/IP. Nevertheless it may be a valuable read for C developers, because PHP itself is written in C. Therefore, PHP inherits all limitations of the underlying MySQL Client Library, just like any other C client.
| Library | Supports connection timeout since | TCP/IP timeout works on Windows? |
|---|---|---|
| MySQL Client Library | MySQL 3.23 (= ever since?) | No |
| MySQL Connector/C | 6.0 (= first release) | No |
| mysqlnd | PHP 5.3.1 | Yes |
PHP 5.3.0 vs. PHP 5.3.1
I would have had never cared much about the connection timeout, if we had not gotten bug reports on it in PHP 5.3.0, mainly on Windows.
PHP binaries for Windows offered for download on php.net use mysqlnd by default as of PHP 5.3.0. Before PHP 5.3.0, php.net has used the MySQL Client Library for its Windows binaries.
Unfortunately mysqlnd does not feature the connection timeout setting before PHP 5.3.1. Internally mysqlnd is using PHP streams. Without setting a connection timeout, mysqlnd defaults to PHP’s default timeout for socket based streams which is 60 seconds default_socket_timeout . As a result any extension using mysqlnd (ext/mysql, ext/mysqli, PDO_MYSQL) may wait up to 60 seconds during the connection establishment. The only way to change this in PHP 5.3.0 is to change the default_socket_timeout setting.
default_socket_timeout = 60
max_execution_time = 30
Note that the default socket timeout is twice the maximum execution time. That PHP manual explains why this is not a misconfiguration:
Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. This is not true on Windows where the measured time is real.
From: http://de.php.net/manual/en/function.set-time-limit.php
Note the exception for Windows and recall that mysqlnd is a default on Windows…. to make it short: you may get an uncatchable fatal error on Windows with PHP 5.3.0 if mysqlnd defaults to the socket timeout, waits for upto 60 seconds but your standard max_execution_time setting is smaller than default_socket_timeout.
This has been fixed in PHP 5.3.1 by adding a connection timeout feature to mysqlnd.
MYSQLI_OPT_CONNECT_TIMEOUT never worked on Windows for TCP/IP
During the bug verification it turned out that MYSQLI_OPT_CONNECT_TIMEOUT (C: MYSQL_OPT_CONNECT_TIMEOUT) had never worked properly on Windows for TCP/IP connections - see MySQL bug #36225. Nonetheless some PHP scripts make use of it to test the reachability of MySQL servers, for example, remote servers of affordable but slow shared hosting services.
What timeout means
Upon closer inspection more surprises came up. All three libraries (MySQL Client Library, Connector/C, mysqlnd) use the same basic approach to implement a connection timeout but differ in detail. After resolving the host name all three do a non-blocking call to the operating system function connect(). If the operating system is not able to immediately establish a TCP/IP connection the libraries - in general - make use of select() or poll() to check the progress of the connection establishment. This is done because connect() does not allow setting a timeout but select()/poll() do. Once a TCP/IP connection has been established, the libraries start the MySQL protocol handshake with the MySQL server.
All three libraries do not consider the MySQL protocol handshake as part of the connection timeout. Time spent for the handshake cannot be controled using MYSQLI_OPT_CONNECT_TIMEOUT. If your TCP/IP connection is really, really slow and your connection timeout setting is short this may become visible. Other timeout settings exist to control the time spend after a successful connect() and before the library returns to the caller.
The below control-flow diagram tries to illustrate the steps and hints for differences in the implementations. The difference on the socket() call is not of relevance for PHP or C clients. The MySQL Client Library which ships with MySQL 5.1.39 uses a timer thread timer to monitor the runtime of socket(), if the code is run as a server (e.g. embedded server). It is shown for completeness.
| Client | Library | MySQL | Differences | Timeout |
|---|---|---|---|---|
| Set timeout = 10 seconds | ||||
| Timeout = 10 seconds | ||||
mysqli_real_connect(host, ...) |
||||
socket() |
! | |||
| Resolve host name | ! | |||
Non-blocking connect() |
||||
select() or poll() for time control |
! | |||
| Connection established | ||||
| Send MySQL protocol greetings | ||||
| Read greetings | ||||
| MySQL protocol handshake | ||||
| MySQL protocol handshake | ||||
| return | ||||
| connection established | ||||
Name resolution
Name resolution is done differently by the MySQL Client Library (from MySQL 5.1.39), Connector/C 6.0 and by mysqlnd. Name resolution refers to the process of translating human readable host names such as "db23.example.com" or "mymysqlbox" into a network address representation understood by the operating system function connect(). Various operating system calls get used which differ in their support for IPv4 and IPv6 and if or how their behaviour can be impacted by resolver (environment) settings. Not only the functions itself differ. They get used in different ways, which impacts the meaning of the connection timeout.
| Library | Name resolution calls | IPv6 accepted |
|---|---|---|
| MySQL Client Library 5.1.39 | inet_addr() with runtime fallback to gethostbyname() |
No |
| Connector/C 6.0 | getaddrinfo() |
Yes |
| mysqlnd = PHP streams | getaddrinfo() with compile time fallback to inet_aton() |
Yes |
Differences all over
Lets go over all libraries and see how they establish a TCP/IP connection using operating system calls.
The MySQL Client Library (from MySQL 5.1.39) first tries to resolve the hostname using inet_addr() and if it fails it inspects all IPv4 addresses returned by gethostbyname(). There is one pitfalls here. The connection timeout is valid for every connection attempt made: Tmax = n x MYSQLI_OPT_CONNECT_TIMEOUT.
| Library | Maximum connect timeout |
|---|---|
| MySQL Client Library 5.1.39 | n x MYSQLI_OPT_CONNECT_TIMEOUT |
| Connector/C 6.0 | MYSQLI_OPT_CONNECT_TIMEOUT |
| mysqlnd = PHP streams | MYSQLI_OPT_CONNECT_TIMEOUT |
The MySQL Connector/C 6.0 uses getaddrinfo() just like PHP streams do. Connector/C asks getaddrinfo() for address running TCP and leaves IPv4 vs. IPv6 unspecified (AF_UNSPEC). This allows the resolver of the operating system to return both IPv4 and IPv6 addresses in whatever order is default on the system. Unlike the MySQL Client Library and PHP, Connector/C picks only the first address and makes a connection attempt.
| Library | Loops over addresses |
|---|---|
| MySQL Client Library 5.1.39 | Yes |
| Connector/C 6.0 | No |
| mysqlnd = PHP streams | Yes |
The MySQL native driver for PHP (mysqlnd) leaves it to PHP streams to do the name resolution. PHP does both a compile time and a runtime check to see if it shall try to use IPv6. If IPv6 support is detected at compile time and PHP can create a datagram socket at runtime (socket(PF_INET6, SOCK_DGRAM, 0)), PHP assumes that IPv6 is not borked and tells getaddrinfo() to return both IPv4 and IPv6 addresses (AF_UNSPEC). Similar to the MySQL Client Library, PHP now starts a loop over all addresses it gets and tries to establish a connection. If there are multiple connection attempts, PHP properly decrements the connection timeout for every attempt made and, if available, additionally uses gettimeofday() to monitor the total runtime. PHP stops looping prematurely, if any address has caused a timeout error code to be set.
| Library | Protocol hint to resolver? |
|---|---|
| MySQL Client Library 5.1.39 | No |
| Connector/C 6.0 | Yes, TCP (ai_protocol= IPPROTO_TCP) |
| mysqlnd = PHP streams | No |
The story about Windows
If you, as a user of any PHP before 5.3.0 on Windows, rely on MYSQLI_OPT_CONNECT_TIMEOUT and believe it is working with TCP/IP connections, you must be mistaken. There is a simple proof. It cannot only be proven with the bug I list above but also with the source code of the MySQL Client Library, which you must be using, because you did not have any alternative before PHP 5.3.0. Neither the MySQL Client Library (from MySQL 5.1.39) nor the MySQL Connector/C 6.0 contain code for Windows to handle the timeout. Both call connect() and let whatever timeout happen that may happen – but they don’t actively control it.
Good news is that MYSQLI_OPT_CONNECT_TIMEOUT works fine with PHP 5.3.1 on Windows: you can set it through mysqlnd, mysqlnd forwards it to PHP streams and PHP streams does its best to implement it. Users of PHP 5.3.0 should upgrade to PHP 5.3.1 as soon as it becomes available, because mysqlnd does not feature the connection timeout setting before PHP 5.3.1.
Summary
TCP/IP connection establishment is implemented differently among the libraries discussed. The different implementations cause different behaviour, for example, with regards to the connection timeout. The connection timeout setting refers only to the operating system level call to establish a connection: connect(). Time spend during the MySQL client server handshake is not considered. Other timeout settings, for example, mysqlnd’s mysqlnd.net_read_timeout or MYSQL_OPT_READ_TIMEOUT, MYSQL_OPT_WRITE_TIMEOUT exist to control time spend while a client reads data from the server or writes data to the server.