php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

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 Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
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

Pull Requests

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.
 [2013-06-14 05:13 UTC] rgagnon24 at gmail dot com 
Related To: Bug #64993
 [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.
 [2024-05-22 05:25 UTC] sonia0992alvarez at outlook dot com 
Hello @rgagnon24 at gmail dot com (https://github.com)(https://www-costcoess.com)

Certainly! When encountering a bug, it’s essential to provide relevant information to help the development team identify and address the issue. Here are some steps you can take:

Detailed Bug Report:
If you encounter a bug, create a detailed bug report. Include information such as:
Steps to reproduce the issue.
Expected behavior.
Actual behavior (what went wrong).
Any error messages or logs.
The platform or environment where you encountered the bug (e.g., operating system, browser, etc.).
Simpler Test Case:
Simplify the test case as much as possible. Remove any unnecessary complexity to isolate the issue.
If you can reproduce the bug with a minimal example, it will be easier for others to understand and fix.
Cross-Platform Testing:
Test the bug on different platforms (e.g., different operating systems, browsers, devices).
Sometimes bugs are specific to certain environments, so cross-platform testing helps identify such cases.
Avoid Cluttering the Database:
Instead of just saying “Me too!” in the bug report, focus on providing additional information.
Upvote the bug if it affects you, but avoid adding noise to the discussion.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Nov 21 17:01:32 2024 UTC