Bug #67317 [Wfx]: [PDO] $pdo = null doesn't close mysql connection if there are PDOStatements
| From: | bengates at aliceadsl dot fr | Date: | Wed, 21 May 2014 12:04:58 +0000 |
| Subject: | Bug #67317 [Wfx]: [PDO] $pdo = null doesn't close mysql connection if there are PDOStatements | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-185882@lists.php.net to get a copy of this message | ||
Edit report at https://bugs.php.net/bug.php?id=67317&edit=1
ID: 67317
User updated by: bengates at aliceadsl dot fr
Reported by: bengates at aliceadsl dot fr
Summary: [PDO] $pdo = null doesn't close mysql connection if
there are PDOStatements
Status: Wont fix
Type: Bug
Package: PDO MySQL
Operating System: Ubuntu
PHP Version: 5.4.28
Block user comment: N
Private report: N
New Comment:
Hi,
Thanks for this quick reply.
I'm the author of https://github.com/bpolaszek/PDOExtended which
provides a pause() method, allowing to disconnect from MySql when something is long (downloading a
remote file, waiting for an event and triggering a log, parsing a huge file, etc).
This helps avoiding multiple "sleep" connections when a script takes a while (PHP is not
only for HTTP) and works when setting the PDO instance to null, except if there are opened
PDOStatements.
So, I tried to register every single PDOStatement instance to "null" them all, but the
reference to each original statement still exist :
<?php
class PDOExtended {
protected $PDO, $dsn, $username, $password, $driver_options, $is_paused = false;
function __construct($dsn, $username, $password, $driver_options = []) {
$this->driver_options = $driver_options;
$this->dsn = $dsn;
$this->username = $username;
$this->password = $password;
$this->connect();
}
/**
* Opens the PDO connection
*/
public function connect() {
$this->PDO = new PDO($this->dsn, $this->username, $this->password,
$this->driver_options);
$this->PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->PDO->setAttribute(PDO::ATTR_STATEMENT_CLASS, 'PDOStatementExtended');
$this->is_paused = false;
return $this;
}
/**
* Disconnects
*/
public function disconnect() {
$this->PDO = null;
PDOStatementExtended::ClearStoredStatements();
return $this;
}
/**
* Pauses the connection
*/
public function pause() {
$this->disconnect();
$this->is_paused = true;
return $this;
}
/**
* Redirects everything to PDO, reconnects if needed
*/
public function __call($method, $args) {
if ($this->is_paused)
$this->connect();
return call_user_func_array([$this->PDO, $method], $args);
}
}
class PDOStatementExtended extends PDOStatement {
public static $STORED_STMTS = array();
/**
* Stores the current statement into static::$STORED_STMTS array
*/
protected function __construct() {
static::$STORED_STMTS[md5($this->queryString)] = $this;
}
/**
* Clears all statements
*/
static public function ClearStoredStatements() {
foreach (static::$STORED_STMTS AS $s => $statement)
static::$STORED_STMTS[$s] = null;
static::$STORED_STMTS = array();
}
}
When calling ClearStoredStatements, the original prepared statement still exist. It's like
static::$STORED_STMTS[md5($this->queryString)] = $this doesn't really link to the
reference... though it is.
Previous Comments:
------------------------------------------------------------------------
[2014-05-21 10:12:18] johannes@php.net
This would break legitimate code like this:
<?php
function foo() {
$pdo = new Pdo("mysql:host=localhost", 'user', 'password');
return $pdo->prepare("SELECT * FROM mytable");
}
$result = foo()->execute();
?>
The better approach would be to have a "close" method which disconnects, but those have
proven to have issues. By PHP's short living nature this usually isn't needed. In the rare
case where this is needed the developer has to handle his resources manually to make sure to unset
all related instances.
------------------------------------------------------------------------
[2014-05-21 09:55:21] bengates at aliceadsl dot fr
Description:
------------
Hello,
I figured out that closing a PDO connection will only be effective if no PDOStatement are open.
If you prepare multiple statements, setting the $pdo instance to null will have no effect until you
set any PDOStatement instance to null too.
Test script:
---------------
Examples :
<?php
$pdo = new Pdo("mysql:host=localhost", 'user', 'password');
$pdo = null;
sleep(10);
exit;
?>
=> Connection has sucessfully been closed and isn't visible in MySql processlist within the
10 seconds for the script to terminate.
<?php
$pdo = new Pdo("mysql:host=localhost", 'user', 'password');
$stmt = $pdo->prepare("SELECT * FROM mytable WHERE Id = ?");
$pdo = null;
sleep(10);
exit;
?>
=> Connection hasn't been closed and is visible (Sleep) in MySql processlist within the 10
seconds for the script to terminate.
<?php
$pdo = new Pdo("mysql:host=localhost", 'user', 'password');
$stmt = $pdo->prepare("SELECT * FROM mytable WHERE Id = ?");
$pdo = null;
$stmt = null;
sleep(10);
exit;
?>
=> Connection has sucessfully been closed and isn't visible in MySql processlist within the
10 seconds for the script to terminate.
Expected result:
----------------
Setting the pdo instance to null should really close mysql's connection, regardless of any
PDOStatement instance still existing at this time.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=67317&edit=1