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

go to bug id or search bugs for

Bug #64549 mysqlnd persistent connection handling out of control
Submitted: 2013-03-29 16:55 UTC Modified: 2020-04-17 12:41 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: Open Package: MySQL related
PHP Version: 5.3.23 OS: CentOS 5.9
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but 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

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.
 [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) ...
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved. 		Last updated: Fri Mar 05 13:01:24 2021 UTC