MySQL “Gone Away” Error with Persistent PHP Connec

Question

I'm hosting a website locally on a WAMP stack. I recently switched the PHP connection to be persistent by adding array(PDO::ATTR_PERSISTENT => true) to the PDO constructor options argument. I've noticed a material drop in the response time as a result (hooray!).

The downside seems to be a gone away error when the machine wakes up. This never happened before changing the connection style.

Is it possible that the cached connection is closed, but continues to be returned? Is it possible to reset a PDO connection or reset the connection pool via PHP inside a catch block?

Answer 1

For what it's worth I'm looking into using persistent connections on php-fpm 7.3 behind nginx and trying to reproduce that behaviour with a static pool of 1 children, and so far I can't.

I can see through SHOW PROCESSLIST on a separate terminal how the database closes the persistent connection after 5 seconds of a request that does a SELECT, but the next just opens a new one and works just as well. On the other hand if I bombard the API with a load testing tool the same connection is maintained and all requests succeed.

Maybe it was because you used Apache+mod_php instead of the php-fpm worker pool, or maybe there's been a genuine fix between PHP 5.4 and 7.3

Versions tested:

PHP-FPM: 7.3.13
mysqlnd (underlying PDO_MYSQL driver): 5.0.12-dev - 20150407
MySQL Server: 5.7.29 and 8.0.19
MariaDB Server (MySQL drop-in replacement): 10.1.43

PD thanks for laying out the reproducing steps and your thought process, it was invaluable.

Answer 2

I've kicked this around for a few days and based on the prevalence of similar issues on the web, this appears to be a deficiency of PDO preventing efficient managing of persistent connections.

Answers to the obvious questions:

• PHP 5.4.22
• Driver settings in php.ini have persistent connections turned on
• Session limits are not bounded (set to -1)
• Pool limits are not bounded (set to -1)

I can recreate the issue by doing the following:

Issue the following statements on the MySQL database.

set @@GLOBAL.interactive_timeout := 10;
set @@GLOBAL.wait_timeout := 10;

Issue a few requests against the server to generate some cached connections. You can see the thread count increase compared to doing this with non-persistent connections via:

echo $conn->getAttribute(PDO::ATTR_SERVER_INFO);

Wait at least 10 seconds and start issuing more requests. You should start receiving 'gone away' messages.

The issue is SQL closes the connections and subsequent calls to the PDO constructor return these closed connections without reconnecting them.

This is where PDO is deficient. There is no way to force a connection open and no good way to even detect state.

The way I'm currently getting around this (admittedly a bit of a hack) is issuing these MySQL statements

set @@GLOBAL.interactive_timeout := 86400;

set @@GLOBAL.wait_timeout := 86400;

These variables are set to 28800sec (8 hours) by default. Note that you'll want to restart Apache to clear out cached connections or you wont notice a difference until all connections in the pool have been cycled (I have no idea how / when that happens). I chose 86400 which is 24 hours and I'm on this machine daily so this should cover the basic need.

After this update I let my machine sit for at least 12 hours which was how long it sat previously when I started getting 'gone away message'. It looks like problem solved.

I've been thinking that while I cant force open a connection, it may be possible to remove a bad connection from the pool. I haven't tried this, but a slightly more elegant solution might be to detect the 'gone away' message then set the object to NULL telling PHP to destroy the resource. If the database logic made a few attempts like this (there'd have to be a limit in case a more severe error occurred), it might help keep these errors to a minimum.

Answer 3

Yes you will need to reconnect if the connection closes.

http://brady.lucidgene.com/2013/04/handling-pdo-lost-mysql-connection-error/