php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #67317 [PDO] $pdo = null doesn't close mysql connection if there are PDOStatements
Submitted: 2014-05-21 09:55 UTC Modified: 2014-05-21 12:04 UTC
From: bengates at aliceadsl dot fr Assigned:
Status: Wont fix Package: PDO MySQL
PHP Version: 5.4.28 OS: Ubuntu
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2014-05-21 09:55 UTC] 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.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-05-21 10:12 UTC] johannes@php.net
-Status: Open +Status: Wont fix
 [2014-05-21 10:12 UTC] 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 12:04 UTC] bengates at aliceadsl dot fr
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.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Wed Jan 27 08:01:26 2021 UTC