pdo_sqlite: statement unusable after a failed execute() since 8.4.27 (SQLITE_MISUSE) #24990

Description

Since 8.4.27, a pdo_sqlite prepared statement whose execute() fails with a constraint violation can no longer be executed again: the next execute() fails with SQLSTATE[HY000]: General error: 21 bad parameter or other API misuse. On 8.4.26 the statement is reusable.

This breaks the common pattern of caching a prepared INSERT and catching the duplicate-key failure (we hit it through a SQL-backed cache, which then silently lost every later write).

The following code:

<?php
$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec('CREATE TABLE t (k TEXT PRIMARY KEY NOT NULL)');
$insert = $pdo->prepare('INSERT INTO t (k) VALUES (?)');

$insert->execute(['a']);
try {
    $insert->execute(['a']); // duplicate key
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}
try {
    $insert->execute(['b']);
    echo "inserted b\n";
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

Resulted in this output (8.4.27):

SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: t.k
SQLSTATE[HY000]: General error: 21 bad parameter or other API misuse

But I expected this output instead (as on 8.4.26):

SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: t.k
inserted b

Calling $insert->closeCursor() after the failure makes the statement usable again.

Likely cause

GH-23938 (11690db, "ext/pdo: Drop the previous result when execute() fails") makes PDOStatement::execute() call the new pdo_stmt_invalidate_result() when stmt->executed is set, which closes the cursor and clears stmt->executed.

pdo_sqlite_stmt_execute() (unchanged) only calls sqlite3_reset() when stmt->executed && !S->done, and on a failed step it only resets for SQLITE_ERROR; a constraint violation returns SQLITE_CONSTRAINT and goes to the default: branch without a reset. stmt->executed stays 0 after the failure, so on the next execute() neither PDO nor pdo_sqlite resets the statement, and binding to the un-reset statement returns SQLITE_MISUSE.

Before 8.4.27, stmt->executed stayed 1 after the first successful execution, so pdo_sqlite reset the statement on every later execution. (A statement whose very first execution fails was already affected on 8.4.26, for the same reason.)

Resetting in pdo_sqlite on every failed step, not only SQLITE_ERROR, looks like it would cover both cases.

PHP Version

PHP 8.4.27 (cli) (built: Oct 21 2026 07:53:46) (NTS)

Also reproduced with the official php:8.4.27-cli Docker image; not reproducible with php:8.4.26-cli. SQLite 3.46.1.

Operating System

Debian 13 (trixie)