I found a workaround:

Apparently mysqlnd does listen to 'default_socket_timeout'.

ini_set('default_socket_timeout', 5) sets a 5 second limit

I found that ini_set('default_socket_timeout', -1) disables the timeout all 
together, though that might be undocumented behaviour.


$storeTimeout = ini_set('default_socket_timeout', -1);
$link = mysqli_init();
mysqli_real_connect($link, 'localhost', 'root', '', 'mysql', null, null, 
MYSQLI_CLIENT_INTERACTIVE);
ini_set('default_socket_timeout', $storeTimeout);

mysqli_query($link, 'SELECT SLEEP(62)');

mysqli_close($link);

See also bug #49436

Note: MYSQL_OPT_READ_TIMEOUT != MYSQL_OPT_CONNECT_TIMEOUT . 

What you are saying is that you query times out. The report is not about a connection timeout. Your timeout happens after the connection has been established and therefore setting MYSQLI_OPT_CONNECT_TIMEOUT won't change anything.

In the end ext/mysqli is a wrapper of the MySQL C API and thus we can consult the C API documentation on the difference. The docs at http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html explain:

MYSQL_OPT_CONNECT_TIMEOUT (argument type: unsigned int *)
Connect timeout in seconds.

MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int *)

The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes. This option works only for TCP/IP connections and, prior to MySQL 5.1.12, only for Windows.

MYSQL_OPT_READ_TIMEOUT has never been supported by ext/mysqli. You can easily check that by "grepping" through the source of ext/mysqli as contained in, for example, PHP 5.2.10. 

nixnutz@ulflinux:~/ftp/php-5.2.10> grep -i -R MYSQL_OPT_READ_TIMEOUT ext/
nixnutz@ulflinux:~/ftp/php-5.2.10> grep -i -R MYSQLI_OPT_READ_TIMEOUT ext/
nixnutz@ulflinux:~/ftp/php-5.2.10>    

However, mysqlnd is not the MySQL Client Library, mysqlnd makes use of PHP Streams. Looks like PHP Streams use different default timeouts than the MySQL Client Library on Windows. That is a problem for you as a user, no doubt. 

Question is how to solve that problem.

It is not correct to say that mysqlnd on windows differs from libmysql.

Libmysql uses native APIs directly no matter the platform while mysqlnd always uses php's stream, on every platform.

The different between windows and unix is that windows relies on mysqlnd only (no choice, no compatible library available). 

On the available options on Windows:

ext/mysql, ext/mysqli and PDO_MYSQL can either be compiled against the MySQL Client Library or mysqlnd. That is also true for Windows in general. If not, it is a bug. Windows users do have a choice.

Pierre, php.net restricts itself on Windows to mysqlnd for a reason. The reason is that you cannot compile current versions of the MySQL Client Library using Visual Studio 6, just like you cannot compile current versions of the MySQL Server using Visual Studio 6. Visual Studio 6 is old - it is from 1998. As far as I know, Microsoft has stopped to support this compiler. Consequently, MySQL has dropped support for it.

To my understanding VC 6 is still of relevance for php.net because other projects have or still do use VC 6. I would not want to use an unsupported compiler for my project but they do although there are superior and free offering from Microsoft. Using their products together with a PHP binary that is not compiled with VC 6 can lead to crashes. 

Therefore, you are looking to support VC 6, which is not possible when using the MySQL Client Library.

If one fails to use a recent version of the MySQL Client Library together with ext/mysql, ext/mysqli or PDO_MYSQL on Windows using a recent Visual Studio it should be reported as a bug.

It canont be compiled against libmysql. Their libraries are not compatible. I don't choose to do it but they did. I also reported the issue upstream but they don't care much about this problem, for two reasons:

1. they gave php a new driver so they won't worry anymore about such troubles (or licensing issues)
2. libmysql latest version CRT (5.0 or 5.1) can only be built only for vc8+

There is even BC breaks as latest libmysql.dll makes php 5.2 crashes.

But that's not the problem here, there is a bug in mysqlnd and it has to be addressed. It is not even a windows only issue.

Pierre,

1) is an aside discussion. 

I assume 2) is about binaries offered for download from mysql.com? CRT is a runtime dependency specific to the Windows platform. Windows binaries can have those dependencies. Educate me about the details but if any Windows binary has such a dependency there can be trouble using it with other Windows binaries that use other CRTs.

In the worst case users needs to compile the library from source. 

Now, if you compile the MySQL Client Library from source to ensure that it uses the CRT you want and that self-compiled library cannot be used together with ext/mysql, ext/mysqli or PDO_MYSQL, it is a bug and should be reported - at bugs.php.net or at bugs.mysql.com.

On the bug itself:

The MySQL Client Library (libmysql) sets a limit of 365 * 24 * 3600 seconds as a default timeout for select() etc. One will hardly ever get into that timeout but see a TCP/IP timeout happening before (e.g. after 10 minutes). 

mysqlnd does currently set no PHP streams timeout and therefore the default PHP setting applies. 

To get the same behaviour in mysql as with libmysql, we would need to set the streams timeout to 1 year upon connect and wait. We may wait longer than max_execution_time. However, it seems that by default default_socket_timeout > max_execution_time and thus it may be irrelevant to take care of it.

Only ext/mysqli is more or less prepared to allow users to set the timeout via an API call - one would need to introduce MYSQLI_READ_TIMEOUT. While it may be easy to add that option also to PDO_MYSQL there is easy way to make the read timeout configurable via API in ext/mysql.

My current thinking is to introduce mysqlnd.read_timeout with a default value equal to libmysql (= huuuuuugggggeeee) and make it SYSTEM_INI only: no API changes required and admins can set it to max_execution_time if they want.

Regarding default_socket_timeout = -1 -> endless:
I could not find it in the documentation either. It may need documentation.

Sorry, I was not clear. Bugs have been reported and nothing has been done so far. It is not a PHP bug and no, the libmysql libraries (5.0 or 5.1) does not build with VC6 either. As I said, we rather have to fix mysqlnd instead of forking libmysql for our usages (as we will have to do to support VC6).

I really don't want to do that for the next 3-4 years (expected lifetime for 5.3) :)

And yes, your comment about this bug is right, Ulf agrees too afaics :)

Hi Ulf,

Your solution seems spot on.

Also, I don't understand what you are saying here:
"To get the same behaviour in mysql as with libmysql, we would need to set the streams timeout to 1 year upon connect and 
wait. We may wait longer than max_execution_time. However, it seems that by default default_socket_timeout >  
max_execution_time and thus it may be irrelevant to take care of it."

In the manual on set_time_limit() it says:
"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." (http://www.php.net/manual/en/function.set-time-
limit.php)

Regarding default_socket_timeout = -1 -> endless:
It could be that it's just overflowing an int, resulting in 2^64-1 or 2^32-1 seconds.

Pierre, 

I already stated that MySQL does no longer support VC6. MySQL does not support VC6 because Microsoft itself does not support it. 

Who to blame? It depends on how you feel about VC6 being EOL'd. If you think it should not have been EOL'd, you need to blame both Microsoft and users of Microsoft products, such as MySQL. 

If you consider the EOL of VC6 as reasonable, the root cause of the VC6 trouble is neither Microsoft nor MySQL. The trouble is caused by those who still use VC6 for their binaries and/or do not make their source compile with recent versions of Visual Studio and therefore cause incompatibilities.

Not supporting VC6 is not a bug, it is a feature decision. Not supporting compilers which are out of maintenance, is a pretty sane policy in my eyes considering lack of vendor support, lack of bug fixes, lack of support for modern hardware. VC6 is from the same generation as GCC 2.8.0. I wonder how many of us bother about GCC 2.8.0 ...

For every reasonable current (no GCC 2.8 no VC6!) environment the availability of a binary (here: libmysql) is unrelated to the question if a software can be used. I am sure that there are Linux systems which use a glibc (stdc++, etc. ) that is not compatible with the glibc used to build a certain software. This does not necessarily mean that you cannot use the software altogether. As a last resort you can always try to compile it yourself on the target platform.

I am aware how inconvenient it is for php.net to build tons of libraries from source. Any request for a pre-compiled binary that uses CRT x.y is as valid as a request for glibc x.y. It is good and correct to precisely repeat any build request - preferably at bugs.mysql.com. This is the best place to make MySQL do anything for you.

Anyway, I still cannot believe that it is *impossible* to the the MySQL Client Library together with ext/mysqli, ext/mysql or PDO_MYSQL. 

If you have any problems compiling the MySQL Client Library from source using a recent version of Visual Studio, please report it as a bug on bugs.mysql.com. If any of those self-compiled libraries cannot be used because of faulty coding leading to stinky run-time dependencies, please report it as a bug on bugs.mysql.com. If you think the code of the extensions needs to be tweaked to be compatible with any such binary, please report it as a bug on bugs.php.net or bugs.mysql.com.

I'm not blaming anyone, I'm only saying that we can forget libmysql on windows, no matter which compiler we use as the crashes occur too with the other versions.

Now, it seems that you insist that we should use libmysql. Does it mean that mysqlnd is not usable or will not be usable in a production environment in a reasonable time frame (aka 5.3.1)? No irony meant here, but if it is the case then we have a serious problem to solve.

Any bug reports on libmysql crashes we could work on?

For the public and on "libmysql not compatible": Pierre and I had an argue on IRC. While we still disagree in some points we concluded it would be benefitial to document some background information for users - either in the docs or on the wiki.

On the bug itself: I am waiting for Andrey to review a patch.

It should be fixed now, please test a SVN snapshot.

We may want to stress out in the documentation that a) users cannot set MYSQL_OPT_READ_TIMEOUT through an API call and, if we ever allow setting it, there may be subtle differences between libmysql and streams how to interpret the value of MYSQL_OPT_READ_TIMEOUT. 

Using mysqlnd means using PHP streams. PHP streams documentation needs to be consulted on such details not dev.mysql.com documentation on the MySQL Client Library AKA libmysql AKA C-API.

Regarding default_socket_timeout = -1 -> endless:

I don't think it is an overflow. I only glimpsed over the code and saw that sometimes an extra check is made for -1 to convert it into NULL. And NULL means endless, see http://msdn.microsoft.com/en-us/library/ms740141%28VS.85%29.aspx or, same on Linux, man select. 

Tony, can you take it? Thanks!