Hi. I would like to offer three changes to PDO module.
- 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.
- 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.
- 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