Improving PDO persistent connections and event loop support View as Markdown

Hi. I would like to offer three changes to PDO module.

1. Cleanup persistent connection before passing it to next request

Documentation on persistent connections [1] says:

Warning
PDO does not perform any cleanup of persistent connections. Temporary
tables, locks, transactions and other stateful changes may remain from
previous usage of the connection, causing unexpected problems.

This makes persistent connections effectively unusable. I don't want to
obtain dirty connection from previous request. In contrast, mysqli
extension do automatic cleanup [2]:

The persistent connection of the mysqli extension however provides
built-in cleanup handling code.

Adding cleanup code to PDO will align persisting connection behavior with
mysqli and make persistent connections safe to use. This is breaking change.

2. Add new method to PDO class - reset()

This method will reset connection state. For mysql it will call C-API
function mysql_change_user() like mysqli do. Most value this method would
bring to event loop environments (more on this below), but also can be non
breaking alternative to my first proposal - I just call this method on the
very beginning of my script and proceed without fear, that everything may
break due to garbage state from previous worker request.

After calling proposed refresh() method: all still alive PDOStatement
invalidated, logical db connection refreshed (e.g. mysql_change_user C-API
called), underlying TCP/unix-socket/file-pointer connection stay intact.

3. Add new method to PDO class - close()

PDO does not have method for closing connection. Documentation [1] says:

To close the connection, you need to destroy the object by ... assigning
null to the variable that holds the object.

Explicit close is not necessary for fpm and mod_php environments, because
connection automatically closed at the end of script execution. But in
event loops this may be complicated because of internal references to PDO
object from PDOStatement objects [1]:

Note: If there are still other references to this PDO instance (such as
from a PDOStatement instance, or from other variables referencing the same
PDO instance), these have to be removed also (for instance, by assigning
null to the variable that references the PDOStatement).

After calling proposed close() method: all still alive PDOStatement
objects invalidated, logical db connection closed (e.g. mysql_close C-API
called), underlyind TCP/unix-socket/file-pointer connection closed.

Event loop considerations

Event loop based applications (e.g. roadrunner, frankenphp) are main
intended users of reset() and close() methods. Event loop may look like:

// Example pseudocode
$ev = setupEventLoopEnvironment();
$app = setupApplication();

while ($request = $ev->waitForRequest()) {
  $db = new PDO('dsn', 'user', 'password');
  $app->setDb($db);
  $app->handle($request);
  $db = null;
  $app->setDb(null);
}

Such approach may lead to db connection leak because application may
unintentionally cache PDO or PDOStatement object that would prevent
connection from closing. However, this may be handled by hiding PDO objects
behind abstraction, provided by event loop framework. But performance
impact cannot be avoided.

More safe and performant variant using reset():

$ev = setupEventLoopEnvironment();
$db = new PDO('dsn', 'user', 'password');
$app = setupApplication();
$app->setDb($db);

while ($request = $ev->waitForRequest()) {
  $db->reset();
  $app->handle($request);
}

On this mail I focused on mysql. If the community is interested by the
proposed changes, I'll investigate other supported db drivers as well and
write RFC.

[1] https://www.php.net/manual/en/pdo.connections.php
[2] https://www.php.net/manual/en/mysqli.persistconns.php