php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #58863 Reconnect support broken
Submitted: 2009-09-20 17:13 UTC Modified: 2013-10-02 05:52 UTC
Votes:4
Avg. Score:4.8 ± 0.4
Reproduced:4 of 4 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: marion dot jeff at gmail dot com Assigned:
Status: Wont fix Package: *General Issues
PHP Version: 5.2.8 OS: *nix (Centos 4/5, Arch)
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2009-09-20 17:13 UTC] marion dot jeff at gmail dot com
Description:
------------
In mysql_driver.c (1.0.2) there is an #ifdef MYSQL_OPT_RECONNECT, to check for the availability of the option to enable auto-reconnects, which never matches. The reconnect option changed from default on to off with 5.0.3 and the MYSQL_OPT_RECONNECT option was added to re-enable it with 5.0.13.

So since mysql 5.0.3 pdo_mysql connections which are timed out by a wait_timeout on the server are unusable without a manual reconnect. The intention of the code is to force auto-reconnects when available and is commented as such.

The mysql_options are enums defined in mysql.h and are not available to the preprocessor in mysql_driver.c. In 1.0.2 this is the only issue caused by an ifdef on a MYSQL_OPT_* but in newer branches there is an at least one additional broken check.

Reproduce code:
---------------
/etc/my.cnf
[mysqld]
wait_timeout = 30

<?php

$conn = new PDO('mysql:host=localhost', 'user', 'pass');

$a = $conn->query('SELECT NOW(), 1');
var_dump($a->fetchAll(PDO::FETCH_ASSOC));

sleep(40);

$b = $conn->query('SELECT NOW(), 2');
var_dump($conn->errorInfo());
var_dump($b->fetchAll(PDO::FETCH_ASSOC));

?>

Patch for mysql_driver.c (also takes in to account a mysql bug between 5.0.13 and 5.1.6 where mysql_real_connect reset OPT_RECONNECT to off):
483c483
< #ifdef MYSQL_OPT_RECONNECT
---
> #if MYSQL_VERSION_ID >= 50013 && MYSQL_VERSION_ID >= 50106
485c485
<                * we want the old behaviour */
---
>                * we want the old behaviour. starting with 5.1.6 set pre-connect. */
535a536,543
> #if MYSQL_VERSION_ID >= 50013 && MYSQL_VERSION_ID < 50106
>       /* before 5.1.6, MYSQL_OPT_RECONNECT gets reset by mysql_real_connect */
>       {
>               long reconnect = 1;
>               mysql_options(H->server, MYSQL_OPT_RECONNECT, (const char*)&reconnect);
>       }
> #endif
>

Expected result:
----------------
array(1) {
  [0]=>
  array(2) {
    ["NOW()"]=>
    string(19) "2009-09-20 21:05:58"
    [1]=>
    string(1) "1"
  }
}
array(1) {
  [0]=>
  string(5) "00000"
}
array(1) {
  [0]=>
  array(2) {
    ["NOW()"]=>
    string(19) "2009-09-20 21:06:38"
    [2]=>
    string(1) "2"
  }
}

Actual result:
--------------
array(1) {
  [0]=>
  array(2) {
    ["NOW()"]=>
    string(19) "2009-09-20 21:07:04"
    [1]=>
    string(1) "1"
  }
}
array(3) {
  [0]=>
  string(5) "HY000"
  [1]=>
  int(2006)
  [2]=>
  string(26) "MySQL server has gone away"
}

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-09-25 16:16 UTC] marion dot jeff at gmail dot com
Changing title to highlight problem better.
 [2009-11-05 08:02 UTC] ulf dot wendel at phpdoc dot de
This would break BC, wouldn't it?
 [2009-11-05 12:26 UTC] marion dot jeff at gmail dot com
There is a BC break but it fixes what is a larger one introduced by the mysql behavior change.

Prior to mysql version 5.0.3 the auto reconnect was the default behavior, they documented the change and provided the OPT to enable it at that time.

In version 1.0.2 code was introduced to PDO_MYSQL to preserve the old expected behavior, and commented as such "we want the old behaviour". If it worked it would have preserved the expected behavior of the driver, for people transitioning from a mysql version prior to 5.0.3 and, in my opinion, preserved the expected behavior of the PDO driver as well, which I would expect to intelligently maintain a connection as needed (such as the pgsql driver does).

Anyways, bit of a long winded explanation but the upshot is that yes it does mean that people using mysql >= 5.0.3 with server side connection timeouts and PDO 1.0.2 will no longer have their PDO connections timing out, they will auto reconnect with this change. The existing behavior is really the compatibility break, it is just fairly long standing at this point.

Introducing a new driver attribute, say PDO::MYSQL_ATTR_RECONNECT to control the behavior would allow for maintaining the existing broken behavior while also allowing those who need the old behavior to re-enable it. I'm unsure of the scope of introducing a new attribute, it looks straight forward enough within the driver but I'm not familiar with what other changes would need to be made in the docs or elsewhere so I can't really submit a patch for such at the moment.
 [2010-11-14 21:00 UTC] ron at ronkorving dot nl
Any follow-ups on this? I think the community really needs this feature. I know I do.
 [2011-01-13 20:47 UTC] datibbaw@php.net
Just came across this ticket when I realized that PDO doesn't 
have a reconnect method and assumed it was done "magically"; 
so I was surprised that this still doesn't work in 5.3.4

It would be really nice if this behaviour could be governed 
via a PDO:: parameter, specifically for MySQL or otherwise.
 [2011-05-26 08:27 UTC] johannes at schlueters dot de
Doing a magic reconnect without notice to the user is bad as the connection looses its state (prepared statements become invalid, session variables are lost, temporary tables go away, ...)

A manual reconnect also won't be implemented as MySQL specific feature. Please report it at bugs.php.net as general PDO request if you think this is really think this is useful.
 [2011-05-30 17:31 UTC] marion dot jeff at gmail dot com
Johannes,

If the current PDO stance on the issue is that you don't want to allow for even an optional enablement of the underlying MySQL OPT_RECONNECT behavior that's your prerogative.

However, it seems the exact opposite decision was made in the past by the PDO_MYSQL maintainers when they chose to add code which explicitly enabled auto-reconnect behavior in the driver following the MySQL 5.0.3 change to the OPT_RECONNECT default. The existence of that code is the basis for this bug. That code is in fact broken. Lines 639-646 in the PHP 5.4 branch are not just dead code which is impossible to ever reach but both the code and comment are very misleading. Additionally there is another block up code (633-638) which makes the same mistake of trying to ifdef based on one of the MYSQL_OPT_ enums, which are not available to the preprocessor and so will always fail to match.

If the project has changed it's mind about the reconnect behavior and wants it to  be intentionally off that is fine by me but please fix the code so it represents this decision and ideally add some documentation about the behavior change that occurred.

Feel free to contact me off list to discuss.
 [2011-06-05 04:02 UTC] marion dot jeff at gmail dot com
Reopening as there hasn't been any follow-up or comment.
 [2011-07-01 05:24 UTC] sinomad123 at gmail dot com
I've got the same problem,too. PDO class does not have a reconnect function.When reading the source, I found the broken code. So I have to make some change as below:

mysql_driver.c:
-------------
594
(added) long reconnect = pdo_attr_bval(driver_options, PDO_MYSQL_ATTR_RECONNECT, 0 TSRMLS_CC);
595

639 	//#ifdef MYSQL_OPT_RECONNECT
640 	/* since 5.0.3, the default for this option is 0 if not specified.
641 	* we want the old behaviour */
642 	if( reconnect ){
643 	long reconnect = 1;
644 	mysql_options(H->server, MYSQL_OPT_RECONNECT, (const char*)&reconnect);
645 	}
646 	//#endif 

php_pdo_mysql_int.h
-------------------
159 	PDO_MYSQL_ATTR_INIT_COMMAND, 
(added) MYSQL_OPT_RECONNECT,

pdo_mysql.c
-----------
72 	REGISTER_PDO_CLASS_CONST_LONG("MYSQL_ATTR_INIT_COMMAND", (long)PDO_MYSQL_ATTR_INIT_COMMAND); 
(added) REGISTER_PDO_CLASS_CONST_LONG("MYSQL_OPT_RECONNECT", (long)PDO_MYSQL_OPT_RECONNECT); 

implemention of function pdo_attr_bval:(in pdo/php_pdo_driver.h)
--------------
static inline long pdo_attr_bval(zval *options, enum pdo_attribute_type option_name, long defval TSRMLS_DC)
{
    zval **v;
    if (options && SUCCESS == zend_hash_index_find(Z_ARRVAL_P(options), option_name, (void**)&v)) {
        convert_to_boolean_ex(v);
        return Z_BVAL_PP(v);
    }
    return defval;
}

after make&install, you can use pdo_options to set MYSQL_OPT_RECONNECT:
$conn = new PDO('mysql:host=xxx', 'xx', 'xx',array(PDO::MYSQL_OPT_RECONNECT=>true));

hope this will help.
 [2013-10-02 05:52 UTC] mike@php.net
-Status: Open +Status: Wont fix -Package: PDO_MYSQL +Package: *General Issues
 [2013-10-02 05:52 UTC] mike@php.net
PECL PDO packages are obsolete.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Wed Jun 29 12:03:52 2022 UTC