I spent a bit of time looking at a mismatch between pdo_mysql and
MYSQL_OPT_RECONNECT. There is some rather odd code in
pdo_mysql/mysql_driver.c:
http://lxr.php.net/xref/PHP_5_4/ext/pdo_mysql/mysql_driver.c#640
There are two main problems here
-
This code is inside an if(driver_options) conditional so unless you
pass a driver option to the constructor this code doesn't get run. if it
wasn't for the second bug, we would have this odd side-effect behaviour
that setting any random option would also turn on automatic reconnects -
The #ifdef MYSQL_OPT_RECONNECT doesn't work at all because MySQL
options are in an enum and thus obviously not available to the preprocessor.
So, because of these bugs when MySQL turned off auto-reconnects in 5.0.3
everyone has been running with them off despite pdo_mysql's intent to
turn them back on. Fixing these bugs now to make the code do what it was
intended to do would be a BC break. My suggestion is to just expose
MYSQL_OPT_RECONNECT as a normal user-settable option (which wouldn't be
available under mysqlnd) and leaving it off by default.
Looking through the bug db this was actually partially reported a while
ago in bug 58863. Johannes/Stas is exposing MYSQL_OPT_RECONNECT ok for
5.3/5.4? I suppose it is a grey area between a feature change and a bug
fix in this case.
-Rasmus
So, because of these bugs when MySQL turned off auto-reconnects in 5.0.3
everyone has been running with them off despite pdo_mysql's intent to
turn them back on. Fixing these bugs now to make the code do what it was
intended to do would be a BC break. My suggestion is to just expose
MYSQL_OPT_RECONNECT as a normal user-settable option (which wouldn't be
available under mysqlnd) and leaving it off by default.Looking through the bug db this was actually partially reported a while
ago in bug 58863. Johannes/Stas is exposing MYSQL_OPT_RECONNECT ok for
5.3/5.4? I suppose it is a grey area between a feature change and a bug
fix in this case.
I guess exposing it is ok, while I would advertise it. Doing the
automatic reconnect will mean strange things will happen as no state is
kept. So if you set any session variable (SET NAMES, timezones, SQL
mode, ...) they will be lost. This will become especially with the
recently proposed change to go to native prepared statements,thinking of
an example like this:
$pdo = new PDO("mysql:...");
$select = $pdo->prepare("SELECT * FROM t WHERE id = ?");
/* do something ... connection break in between */
$delete = $pdo->prepare("DELETE FROM t WHERE id = ?");
$select->execute([1]);
This will in fact do a DELETE, not a SELECT as the statement handle
effectively is nothing but a per-connection counted unsigned long.
johannes
an example like this:
$pdo = new PDO("mysql:...");
$select = $pdo->prepare("SELECT * FROM t WHERE id = ?");
/* do something ... connection break in between */
$delete = $pdo->prepare("DELETE FROM t WHERE id = ?");
$select->execute([1]);This will in fact do a DELETE, not a SELECT as the statement handle
effectively is nothing but a per-connection counted unsigned long.
Well, this sounds like a bug
Prepared statements should become invalid once connection is lost and further attempts to execute them should lead to exception
an example like this:
$pdo = new PDO("mysql:...");
$select = $pdo->prepare("SELECT * FROM t WHERE id = ?");
/* do something ... connection break in between */
$delete = $pdo->prepare("DELETE FROM t WHERE id = ?");
$select->execute([1]);This will in fact do a DELETE, not a SELECT as the statement handle
effectively is nothing but a per-connection counted unsigned long.Well, this sounds like a bug
Prepared statements should become invalid once connection is lost and
further attempts to execute them should lead to exception
Nothing PHP can do about.*) The reconnect, if requested, happens
silently. I've attached a simple test program (warning: not a good
example for libmysql usage, missing some error handling, leaking
everything etc.) demonstrating this behavior. Compile it using something
like
gcc mysql_config --cflags --libs mysql_reconnect_ps.c
after editing username and password for your setup. If you comment out
the mysql_kill() call the first statement will be executed, if you leave
it as is the second will be executed.
johannes
*) Well there's one thing PHP could do - check the thread-id for every
operation, if it changed there was a reconnect -- but better not use
silent reconnects at all ...