Memory leak with PDOConnection->prepare()

[cmorbitzer]

Doctrine\DBAL\Driver\PDOConnection()->prepare() returns a prepared statement with an extra reference to it, so efforts to destroy the statement or close the connection does not free the associated memory in PHP.

Scripts that execute many statements (e.g., a script that performs an insert for each chunk of rows returned from a select statement) and daemons that run indefinitely will eventually throw an out of memory error.

For comparison, PDO()->prepare() returns a statement that when destroyed does free the associated memory.

In fact, it appears that any class that inherits from PDO instead of composing a new wrapper class will experience this same issue. This is documented here: https://bugs.php.net/bug.php?id=75190

I am using PHP 7.2.2

Examples with comparison:

Using PDO:

<?php

print(memory_get_usage() . "\n");

$dbh = new PDO($dsn, $user, $password);

xdebug_debug_zval('dbh');
print(memory_get_usage() . "\n");

for($i = 0; $i < 100; $i++) {

    // Obviously we normally would not generate a new identical prepared statement inside
    // of a loop, but this demonstrates the problem well, and an ORM using PDOConnection
    // (e.g. Laravel Eloquent) does actually generate a new prepared statement for each
    // query, expecting that the statement will be properly destroyed once all references
    // to it are destroyed.
    $stmt = $dbh->prepare(
        "INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)");

    if($i == 0) {
        xdebug_debug_zval('dbh');
        xdebug_debug_zval('stmt');
        print(memory_get_usage() . "\n");
    }

    $stmt->execute([1, 1, '2018-01-01', '2018-01-01']);

    if($i == 0) {
        xdebug_debug_zval('dbh');
        xdebug_debug_zval('stmt');
        print(memory_get_usage() . "\n");
    }

}

$stmt = null;

xdebug_debug_zval('dbh');
print(memory_get_usage() . "\n");

$dbh = null;
print(memory_get_usage() . "\n");

Output:

631648
dbh: (refcount=2, is_ref=0)=class PDO {  }
642616
dbh: (refcount=3, is_ref=0)=class PDO {  }
stmt: (refcount=2, is_ref=0)=class PDOStatement { public $queryString = (refcount=1, is_ref=0)='INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)' }
643744
dbh: (refcount=3, is_ref=0)=class PDO {  }
stmt: (refcount=1, is_ref=0)=class PDOStatement { public $queryString = (refcount=1, is_ref=0)='INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)' }
645328
dbh: (refcount=2, is_ref=0)=class PDO {  }
687416
687416

Using a PDOConnection that inherits from PDO:

<?php

class PDOConnection extends PDO {

    public function prepare($prepareString, $driverOptions = [])
    {
        return parent::prepare($prepareString, $driverOptions);
    }
}

print(memory_get_usage() . "\n");

$dbh = new PDOConnection($dsn, $user, $password);

xdebug_debug_zval('dbh');
print(memory_get_usage() . "\n");

for($i = 0; $i < 100; $i++) {

    // Obviously we normally would not generate a new identical prepared statement inside
    // of a loop, but this demonstrates the problem well, and an ORM using PDOConnection
    // (e.g. Laravel Eloquent) does actually generate a new prepared statement for each
    // query, expecting that the statement will be properly destroyed once all references
    // to it are destroyed.
    $stmt = $dbh->prepare(
        "INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)");

    if($i == 0) {
        xdebug_debug_zval('dbh');
        xdebug_debug_zval('stmt');
        print(memory_get_usage() . "\n");
    }

    $stmt->execute([1, 1, '2018-01-01', '2018-01-01']);

    if($i == 0) {
        xdebug_debug_zval('dbh');
        xdebug_debug_zval('stmt');
        print(memory_get_usage() . "\n");
    }

}

$stmt = null;

xdebug_debug_zval('dbh');
print(memory_get_usage() . "\n");

$dbh = null;
print(memory_get_usage() . "\n");

Output:

642048
dbh: (refcount=2, is_ref=0)=class PDOConnection {  }
653016
dbh: (refcount=3, is_ref=0)=class PDOConnection {  }
stmt: (refcount=3, is_ref=0)=class PDOStatement { public $queryString = (refcount=1, is_ref=0)='INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)' }
654160
dbh: (refcount=3, is_ref=0)=class PDOConnection {  }
stmt: (refcount=2, is_ref=0)=class PDOStatement { public $queryString = (refcount=1, is_ref=0)='INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)' }
655744
dbh: (refcount=102, is_ref=0)=class PDOConnection {  }
888592
888592

Using a composed PDOConnection that wraps PDO:

<?php

class PDOConnection {

    protected $dbh;

    public function __construct($dsn, $user = null, $password = null, array $options = null)
    {
        $this->dbh = new PDO($dsn, $user, $password, $options);
    }

    public function prepare($prepareString, $driverOptions = [])
    {
        return $this->dbh->prepare($prepareString, $driverOptions);
    }
}

print(memory_get_usage() . "\n");

$dbh = new PDOConnection($dsn, $user, $password);

xdebug_debug_zval('dbh');
print(memory_get_usage() . "\n");

for($i = 0; $i < 100; $i++) {

    // Obviously we normally would not generate a new identical prepared statement inside
    // of a loop, but this demonstrates the problem well, and an ORM using PDOConnection
    // (e.g. Laravel Eloquent) does actually generate a new prepared statement for each
    // query, expecting that the statement will be properly destroyed once all references
    // to it are destroyed.
    $stmt = $dbh->prepare(
        "INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)");

    if($i == 0) {
        xdebug_debug_zval('dbh');
        xdebug_debug_zval('stmt');
        print(memory_get_usage() . "\n");
    }

    $stmt->execute([1, 1, '2018-01-01', '2018-01-01']);

    if($i == 0) {
        xdebug_debug_zval('dbh');
        xdebug_debug_zval('stmt');
        print(memory_get_usage() . "\n");
    }

}

$stmt = null;

xdebug_debug_zval('dbh');
print(memory_get_usage() . "\n");

$dbh = null;
print(memory_get_usage() . "\n");

Output:

634344
dbh: (refcount=1, is_ref=0)=class PDOConnection { protected $dbh = (refcount=2, is_ref=0)=class PDO {  } }
645744
dbh: (refcount=1, is_ref=0)=class PDOConnection { protected $dbh = (refcount=3, is_ref=0)=class PDO {  } }
stmt: (refcount=2, is_ref=0)=class PDOStatement { public $queryString = (refcount=1, is_ref=0)='INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)' }
646872
dbh: (refcount=1, is_ref=0)=class PDOConnection { protected $dbh = (refcount=3, is_ref=0)=class PDO {  } }
stmt: (refcount=1, is_ref=0)=class PDOStatement { public $queryString = (refcount=1, is_ref=0)='INSERT INTO user (active, admin, created_at, updated_at) VALUES (?, ?, ?, ?)' }
648456
dbh: (refcount=1, is_ref=0)=class PDOConnection { protected $dbh = (refcount=2, is_ref=0)=class PDO {  } }
690544
690112

[Ocramius]

@cmorbitzer where is the reference kept, exactly? Any variable that holds it, or is PDO simply broken?

[cmorbitzer]

I think it's a reference inside PDO or PDOStatement. In the examples above, it would appear that the refcount for $dbh and $stmt should be zero by the end of the script but the memory is obviously not being freed. It appears that the statement is not destroyed at the end of the loop, and that actually retains a reference to the connection object. We see there are 102 references to $dbh by the end of the script.

I'm not well versed enough in the PHP internals to be able to read the PDO source. The comments on the linked bug report suggest that the PHP team is resisting calling this a bug, and rather strongly recommends not extending the PDO class. But composition by itself has its own drawbacks too.

[Ocramius]

and rather strongly recommends not extending the PDO class.

A bit too late for that now :-)

Yeah, this is a PDO bug if the reference is held in internals. It can probably not be GC'd because it's not a normal zval...

[morozov]

A bit too late for that now :-)

FWIW, as of #2958 Driver\PDOStatement doesn't extend \PDOStatement anymore. Technically, we can rework the other classes as well if needed. @cmorbitzer could you check if the issue is reproducible on develop?

[NiklasBr]

I'm experiencing this with PHP 7.4 and DBAL 2.10.2

[NiklasBr]

@cmorbitzer Did you make any progress on this?

[lcobucci]

@NiklasBr as @morozov mentioned the new API doesn't extend PDO objects anymore. Would you be able to try dbal v3.0 (dev) and see if you experience this?

[NiklasBr]

I misunderstood, thought it was not finished yet, but looks like it is. 👍

I'm using this with Pimcore (see referenced issues above), I'll try to force upgrade the dependencies to the 3.x dev branch and see if it is resolved.

[dev-flti]

@NiklasBr

Hi NiklasBr,

did you make any progress here? We have the same problems for bulk importing objects to pimcore and are currently looking for a solution until pimcore upgrades to dbal v3...

[morozov]

Closing as this seems to be specific for 2.x.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.