php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #64549 mysqlnd persistent connection handling are not properly pooling
Submitted: 2013-03-29 16:55 UTC Modified: 2021-06-21 09:34 UTC
Votes:11
Avg. Score:4.5 ± 0.7
Reproduced:9 of 9 (100.0%)
Same Version:2 (22.2%)
Same OS:2 (22.2%)
From: rgagnon24 at gmail dot com Assigned:
Status: Verified Package: PDO MySQL
PHP Version: 5.3.23 OS: CentOS 5.9
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: rgagnon24 at gmail dot com
New email:
PHP Version: OS:

 

 [2013-03-29 16:55 UTC] rgagnon24 at gmail dot com
Description:
------------
When PHP 5.3 is compiled with 
   --enable-mysqlnd=shared
   --with-mysql=shared,mysqlnd
   --with-mysqli=shared,mysqlnd
   --with-pdo-mysql=shared,mysqlnd

In order to use the native driver, persistent connections using PDO don't appear to use any kind of managable or determinate connection pooling.

Running the test script below via apache web server, refreshing the page every few seconds (10 or 12 times), will produce at least 10 connections to the database as shown by the mysql "SHOW PROCESSLIST" command...  yet the phpinfo() section will indicate a number that is not the same as the actual number of connections.

In my test prior to posting, I had 10 actual connections (of which 9 were sleeping, and the 10th one was just used to run the test query) and phpinfo() showed 5 active_persistent_connections, and pconnect_success was 8 (under the mysqlnd stats section).

This leads me to believe there may be a memory leak in the area of code where the module is managing the connection pool.  If no memory leak, the management of the connections is off somehow as idle connections to a production webserver are ridiculously high.  




Test script:
---------------
<?php
$options = array(
	PDO::ATTR_PERSISTENT => true,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
	);
$host_name = 'database_host';
$database_name = 'some_database';
$port = 3306;
$username = 'db_user';
$password = 'db_pass';

$dsn = sprintf("mysql:host=%s;dbname=%s;port=%d",
	$host_name, $database_name, $port);

$dbh = new PDO($dsn, $username, $password, $options);
$sql = 'SELECT * FROM test WHERE id=1 LIMIT 1';

print "<pre>";
$stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
while ($row = $stmt->fetch()) {
	var_dump($row);
}
$stmt->closeCursor();

print '</pre>';

Expected result:
----------------
active_persistent_connections and pconnect_success should be accurate to match what you are really doing.  Also the command line 'netstat -anp|grep :3306|grep httpd|grep ESTABLISHED" should show a limit at some point on the number of connections that are persistent, or they should get re-used.

Actual result:
--------------
There are a lot of unaccounted for idle ESTABLISHED in the netstat command, for connections from httpd to mysql when mysqlnd indicates there are not that many.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-04-02 16:47 UTC] rgagnon24 at gmail dot com
Another thing that is probably related to this...

PDO constructor can emit a warning that it really should not be able to.  It just doesn't make any sense:

PHP Warning:  PDO::__construct(): MySQL server has gone away in <filepath> on line <line_number>

This can happen when PDO::ATTR_PERSISTENT => true is passed during PDO construction, as in:

$dbh = new PDO($dsn_str, $user_name, $password, array(PDO::ATTR_PERSISTENT => true));

A warning like "mysql server has gone away" doesn't make any sense here.  What seems to be happening is that the underlying mysqlnd code is finding a connection in its pool that has died, and it performs a reconnect for you, but the warning is still emitted on the connection.

You would think if you can't connect, that a PDOException would be raised.  No exception is raised because the connection is in fact returned in a working condition, but the warning is still emitted to the error system.

The only workaround I could find was to prefix the statement with "@" as in

$dbh = @new PDO($dsn_str, $user_name, $password, array(PDO::ATTR_PERSISTENT => true));

Any REAL connection exception is still raised, but at least the fake warning is suppressed.
 [2013-04-02 16:48 UTC] rgagnon24 at gmail dot com
Question:  Why is there a gtk window git pull request recorded on this bug?
 [2013-06-07 10:00 UTC] uw@php.net
PDO - that's the thing that does its own pooling, isn't it? Wondering if this is MySQL specific at all...
 [2013-06-14 05:09 UTC] rgagnon24 at gmail dot com
The solution may lie in the patch attached to bug 64993 which does not clear a reference or free memory when a failed SQL happens.
 [2014-10-30 13:39 UTC] patryk dot kozlowski at toxic-software dot pl
PHP Warning:  PDO::__construct(): MySQL server has gone away in <filepath> on line <line_number> - this error still occurs randomly on lastes versions of php-5.5 and php-5.6
 [2017-02-08 09:01 UTC] steffen dot weber at gmail dot com
I've just run into the problem that PDO::__construct emits a needless warning 'MySQL server has gone away' when a persistent connection was closed remotely (e.g. by a MySQL server restart). Since the connection is automatically reestablished there is no need for such a warning.

I did not know that you could use the '@'-error-control-operator to suppress the unnecessary warning without suppressing exceptions, too. Thank you for the hint, rgagnon24!
 [2017-02-08 09:03 UTC] steffen dot weber at gmail dot com
This bug still exists in PHP 7.0.15 and PHP 7.1.1.
 [2018-10-15 20:45 UTC] nick at sabramedia dot com
We have spent a few days trying to figure this one out. We are moving to a new server with Ubuntu Bionic, using PHP 5.6.38 and mysqlnd as the driver. We need to use persistent connections (PDO::ATTR_PERSISTENT => TRUE). However, we kept getting the server gone away or lost connection errors, apparently a false positive. These went away when we took persistent connections off. The solution for using persistent connections was to use the error control operator '@' as rgagnon24 stated more than 5 years ago. Just posting here to let the powers that be know this is still a 'persistent' bug. Pardon the pun.
 [2020-04-17 12:41 UTC] cmb@php.net
Well, there is a persistent connection cache per PHP process (NTS)
or per PHP thread (ZTS) ...
 [2021-06-09 13:43 UTC] cmb@php.net
-Status: Open +Status: Feedback -Package: MySQL related +Package: PDO MySQL -Assigned To: +Assigned To: cmb
 [2021-06-09 13:43 UTC] cmb@php.net
> This bug still exists in PHP 7.0.15 and PHP 7.1.1.

Which bug?  I'm genuinely asking because this ticket mixes quite a
lot of issues (some of which are not bugs at all), and the title
is completely useless.
 [2021-06-20 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 [2021-06-20 06:04 UTC] rgagnon24 at gmail dot com
-Status: No Feedback +Status: Closed
 [2021-06-20 06:04 UTC] rgagnon24 at gmail dot com
The bug as originally posted by me over 8 years ago still exists.  I don't understand the question "which bug?" asked by cmb@php.net.

As originally posted, the bug exists, the test script exploits it, and the expected results vs. actual results are shown.
 [2021-06-20 06:05 UTC] rgagnon24 at gmail dot com
-Status: Closed +Status: Open
 [2021-06-20 06:05 UTC] rgagnon24 at gmail dot com
Why is it I received an email that said I could re-open the case if the problem still exists, yet when I do (which the correct password), it says "
 [2021-06-20 06:06 UTC] rgagnon24 at gmail dot com
Why is it I received an email that said I could re-open the case if the problem still exists, yet when I do (which the correct password), it says "you are not allowed to change a ticket to that state" (or something like that
 [2021-06-20 06:07 UTC] rgagnon24 at gmail dot com
-Summary: mysqlnd persistent connection handling out of control +Summary: mysqlnd persistent connection handling are not properly pooling
 [2021-06-20 06:07 UTC] rgagnon24 at gmail dot com
Changing Summary
 [2021-06-20 08:30 UTC] wf3f2g dot wf2f3f at egrg dot dd
maybe you don't understand that each worker process has it's own persistent connection and that every client connection to the Webserver may be handled by a different worker

so in the reality you have sooner or later the number of max worker processes mysql connections and hence the concept of persistent database connections is idiotic
 [2021-06-21 09:34 UTC] cmb@php.net
Indeed, the persistent connections and the stats are stored
separately for each worker process (or thread), so in a typical
scenario with multiple workers, pooling may not work as expected.
The respective documentation[1] should clarify that; the note
about ODBC connection pooling already hints at this, but is not
general enough.

[1] <https://www.php.net/manual/en/pdo.connections.php>
 [2021-06-21 09:34 UTC] cmb@php.net
-Status: Open +Status: Verified -Type: Bug +Type: Documentation Problem -Assigned To: cmb +Assigned To:
 [2021-06-21 13:29 UTC] rgagnon24 at gmail dot com
Ok, so that sort of makes sense that there is a connection pool per worker in apache, but the bug noted in the comment from "[2013-04-02 16:47 UTC]" still exists that emits an error about the connection being dead even though one is returned after being reconnected.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 01:01:30 2024 UTC