Edit report at https://bugs.php.net/bug.php?id=64549&edit=1
ID: 64549
Comment by: rgagnon24 at gmail dot com
Reported by: rgagnon24 at gmail dot com
Summary: mysqlnd persistent connection handling out of
control
Status: Open
Type: Bug
Package: MySQL related
Operating System: CentOS 5.9
PHP Version: 5.3.23
Block user comment: N
Private report: N
New Comment:
Question: Why is there a gtk window git pull request recorded on this bug?
Previous Comments:
------------------------------------------------------------------------
[2013-04-02 16:47:07] 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-03-29 16:55:27] 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.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=64549&edit=1