In PHP, what state can a reused PDO persistent connection carry over from an earlier request, and how does mysqli's p: prefix handle it differently?
answer
- a connection is a session
- PDO performs no cleanup
- SET time_zone, temp tables, GET_LOCK
- mysqli calls change-user on reuse
- rollback_on_cached_plink defaults Off
basics
~20 sA reused PDO persistent connection keeps session state from the earlier request: SET variables, temporary tables, the selected database and locks. mysqli's p: links run a change-user cleanup on reuse that rolls back, drops temp tables, unlocks and resets session variables.
solid answer
~40 sA connection is a database session, and PDO does no cleanup of persistent ones, per its manual. So a `SET time_zone` or `sql_mode`, a `USE` of another schema, a temporary table, `LOCK TABLES` or a `GET_LOCK()` left by one request is still there for the next request that worker serves; the bug appears intermittently, depending on which worker answers. The one exception in PHP 8.5's source is an open `beginTransaction()` transaction, which PDO rolls back when the object is freed. mysqli's `p:` links are cleaned on reuse: mysqli calls change-user, which rolls back, drops temporary tables, unlocks tables, resets session variables and releases named locks, at the cost of a round-trip. With PDO I set session state explicitly on every connect and clean up what I create, or I skip persistence.
code
php · 15 lines<?php
declare(strict_types=1);
$pdo = new PDO('mysql:host=db;dbname=news;charset=utf8mb4', $user, $pass, [
PDO::ATTR_PERSISTENT => true,
]);
// A reused handle may carry an earlier request's session settings: set them every time.
$pdo->exec("SET time_zone = '+00:00', sql_mode = 'STRICT_ALL_TABLES'");
$pdo->exec('CREATE TEMPORARY TABLE tmp_trending (article_id INT PRIMARY KEY)');
try {
// ... fill and read tmp_trending ...
} finally {
$pdo->exec('DROP TEMPORARY TABLE IF EXISTS tmp_trending');
}go deeper
Recall that a persistent connection keeps its database session, so settings from one request can reach the next.
List what leaks through a PDO persistent handle and what mysqli's change-user cleanup resets on reuse.
Diagnose worker-dependent bugs caused by leftover session state and make connect-time setup and cleanup explicit.
Decide whether per-worker persistence is worth the session-hygiene burden, or whether plain connections or a resetting proxy fit better.
## A connection is a session A database connection carries **session state**: settings and resources that belong to that connection, not to any one query. When a persistent connection passes from one request to the next inside a worker, the question is whether that state is reset. PDO and mysqli answer differently. ## PDO: no cleanup The PDO manual is blunt: **PDO does not perform any cleanup of persistent connections.** Temporary tables, locks and other stateful changes may remain from earlier use of the connection. What can leak into the next request on a MySQL connection includes: - **Session variables** set with `SET`, such as `time_zone` or `sql_mode`. One request sets `SET time_zone = '+09:00'` for a report; the next request on that worker renders every article timestamp in the wrong zone. - **User variables** such as `@last_id`. - **The default database**, if a request switched it with `USE other_db`; later queries hit the wrong schema, or fail. - **Temporary tables**; the next request's `CREATE TEMPORARY TABLE` with the same name fails because the table already exists. - **Table locks** from `LOCK TABLES` and named locks from `GET_LOCK()` that a crashed request never released, blocking other connections. - **The connection character set**, if changed with a query. One thing the source does handle: when the `PDO` object is freed at the end of a request with a transaction still open from `beginTransaction()`, PDO rolls it back, persistent handle or not. The manual's warning lists transactions among the leftovers; in PHP 8.5's source the rollback happens for transactions PDO knows about, but everything else in the list above is left as it was. ## mysqli with `p:`: cleanup on reuse mysqli's persistent links come with built-in cleanup. When a `p:` connection is taken from the worker's cache for reuse, mysqli calls the client library's **change-user** operation, which the manual says: 1. rolls back active transactions; 2. closes and drops temporary tables; 3. unlocks tables; 4. resets session variables; 5. closes prepared statements and handlers; 6. releases locks acquired with `GET_LOCK()`. The price is one extra round-trip each time the link is reused. It can be removed only by compiling PHP with `MYSQLI_NO_CHANGE_USER_ON_PCONNECT` defined, in which case mysqli just pings the link. The `php.ini` switch `mysqli.rollback_on_cached_plink` (default `Off`) adds a rollback of pending transactions at the moment the link is put back into the cache, instead of waiting for its reuse. ## Side by side | Leftover state | PDO persistent | mysqli `p:` | |---|---|---| | Open transaction from the API's own begin call | rolled back when the `PDO` object is freed | rolled back by change-user on reuse | | Session variables (`SET time_zone`, `sql_mode`) | **kept** | reset | | Temporary tables | **kept** | dropped | | `LOCK TABLES` / `GET_LOCK()` | **kept** | released | | Cost per reuse | a liveness ping | a change-user round-trip | ## Writing code that is safe on a reused PDO connection - **Set session state explicitly on every connect**, never rely on its absence: run the `SET` statements you need right after constructing the `PDO` object, not in `Pdo\Mysql::ATTR_INIT_COMMAND`: the init command runs when the driver actually opens the connection, and PDO skips that step when it hands back a cached handle, so it runs once per worker, not once per request. An explicit `exec()` after `new PDO(...)` runs every time. - **Clean up what you create**: drop temporary tables and release named locks in a `finally` block or a shutdown function; the manual suggests destructors or `register_shutdown_function()` for this. - **Avoid per-request session changes** such as `USE` on a shared persistent handle; use fully qualified table names or a separate non-persistent connection. - **If the application needs clean sessions and cannot guarantee cleanup, do not use PDO persistence.** A pooling proxy that resets sessions, or plain connections, are simpler than defensive code in every request. ## How the bug shows up Symptoms are intermittent and worker-dependent: the same URL is correct on one refresh and wrong on the next, because a different worker — with a different history — served it. Reproducing it locally with a single worker often fails, which is itself a clue that per-process state is involved.
- Why does the time-zone bug appear on some page loads and not others?Only the worker whose connection ran the `SET time_zone` carries it. Each request lands on whichever worker is free, so the page is wrong when that worker serves it and right on the others, until the worker exits. A single-worker local setup reproduces it every time or never, which hides the per-process cause.
- Does mysqli's change-user cleanup make persistent mysqli links free of downsides?No. It resets session state, but every reuse pays an extra round-trip, which eats into the setup time persistence was meant to save. It also does nothing about connection counts: each worker still holds its link while idle.
A PDO persistent connection is like a hotel room that is not cleaned between guests: the next guest finds the previous one's alarm clock settings and luggage. A mysqli p: link is the same room after housekeeping has reset it, which takes a few minutes each time.
saying these in an interview costs you the question
- Believing PDO resets session variables on a reused persistent connection.
- Assuming a temporary table disappears at the end of the request.
- Thinking mysqli p: links carry the previous request's session variables.
- Blaming the database for a time-zone bug that follows one worker.
- Expecting a GET_LOCK from a crashed request to be released by PDO.