What would cause PHP to ignore mysqli.reconnect setting?

Question

We have several different servers running the same PHP scripts, all of which use PHP mysqli connections/functions, and we noticed that, on one new server, we started getting many MYSQL Gone Away errors.

wait_timeout in MYSQL is set to 300 seconds on all servers, and this is the length of time it takes before the connection drops on this particular server (i.e. a wait of 301 seconds between queries in the code below produces the error, but 299 seconds does not).

However, all servers also have mysqli.reconnect set to 1 (On). According to documentation, mysqli.reconnect should mean that the dropped connection is reconnected automatically, however it definitely isn't.

Here is a code snippet that I've run that demonstrates the issue. It works on all servers except this particular server:

$mysqli = new mysqli('ip', 'username', 'pass', 'db');
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

$query = "SELECT * FROM table LIMIT 1";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
sleep(301);
$query = "SELECT * FROM table LIMIT 1";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
mysqli_close($mysqli);

print "Finished\n";
exit;

I have also re-written the test script to use mysqli_ping() (since the documentation states that this function should reconnect automatically if mysqli.reconnect is set to 1), however this still does not reconnect, and the second query always produces the error that MySQL has gone away.

All servers are running slightly different versions of PHP. The server that fails is running 5.3.21, other servers are running 5.3.0 and 5.3.10.

You could be getting an overriding setting from somewhere. Have you checked for multiple my.cnf files on the new server? — Raad, Feb 18 '13 at 12:45
When I execute `echo ini_get('mysqli.reconnect');` on the server (command line and through web browser) the setting is always shown as "1" (On), so at run time PHP definitely has the correct value. Is there a MySQL config setting that could cause the reconnection not to work? — Craig Sefton, Feb 18 '13 at 12:52
There is indeed a way of controlling automatic reconnection from the MySQL end - http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html - I assumed all servers were using the same db server, so I discounted that as a possible cause. — Raad, Feb 18 '13 at 15:29
They are all connecting to the same db server, sorry, my bad! — Craig Sefton, Feb 18 '13 at 15:34
Craig, could you update your code snippet to show your latest test script please? Ta — Raad, Feb 18 '13 at 15:57

score 2 · Accepted Answer · answered Feb 19 '13 at 10:20

2

Turned out the issue was with the MYSQLND driver. Our service provider basically recompiled PHP, and this resolved the issue.

answered Feb 19 '13 at 10:20

Craig Sefton

903
11
20

1

Do you have more information? We have the same problem, switching from MYSQLND driver back to libmysql fixes the problem but we'd like to use the native driver. Did the provider recompile php, driver, or other? Can you tell us what version of php was recompiled on? Wondering if you're still using the native driver. – Andy Corman Aug 21 '14 at 21:24
Hi Andy, as I understand it,they only recompiled PHP. Afraid I can't recall what version of PHP it was, I think it was either version 5.3.3, or 5.3.27. Sorry, it was some time ago, and I can't find the original email trail. – Craig Sefton Aug 22 '14 at 09:47
@CraigSefton, Was it due to the bug with Mysqlnd Jay mentioned below? – Pacerier Dec 28 '14 at 21:06

score 1 · Answer 2 · answered Sep 26 '14 at 23:46

1

There is a php bug filed about this, resolved as wontfix: https://bugs.php.net/bug.php?id=52561

answered Sep 26 '14 at 23:46

Jay Paroline

2,487
1
22
27

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

I just came across this while looking for info about the mysqli.reconnect setting. According to the manual:

https://www.php.net/manual/en/mysqli.configuration.php#ini.mysqli.reconnect

Note: This php.ini setting is ignored by the mysqlnd driver.

That didn't specify whether it meant that it always would, or always would not, auto-reconnect. "Would not" seemed more likely, which is confirmed by the comment above noting that it was not happening when the setting was 1.

My issue was that I wanted a way to know when the connection had died (for error-reporting purposes, and so I could implement selective retrying of a failed DB operation in only that case). I don't know if there's a more direct way to check whether a DB connection is alive, but I found my way to the mysqli::ping() method. Except, if it silently auto-reconnected, I wouldn't know it had happened.

So my solution is to save/disable/restore the setting along with using ping(), like this:

if (!$result) { // DB operation failed
    $save = ini_get('mysqli.reconnect'); // save setting
    ini_set('mysqli.reconnect', 0); // disable it
    $connected = $mysqli->ping(); // check connection
    ini_set('mysqli.reconnect', $save); // restore setting
}
if ($connected) {
    // Failure was something else. Handle that...
} else {
    // Failure was due to dropped connection.
    // Explicitly reconnect.
    // Ok to retry operation...
}

What would cause PHP to ignore mysqli.reconnect setting?

3 Answers3

Linked