HY000/2014 php protocol_error ai_generated true

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active in /var/www/app/src/Repository/UserRepository.php:22

ID: php/pdo-prepared-statement-column-count

Also available as: JSON · Markdown · 中文
85%Fix Rate
89%Confidence
1Evidence
2024-04-18First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
PHP 7.4 active — — —
PHP 8.0 active — — —
PHP 8.1 active — — —
PHP 8.2 active — — —
PHP 8.3 active — — —
MySQL 5.7 active — — —
MySQL 8.0 active — — —
MariaDB 10.5 active — — —

Root Cause

PDO's buffered query mode is exhausted when a previous query's result set has not been fully fetched or closed, and a new query is attempted on the same connection, which MySQL forbids.

generic

中文

PDO 的缓冲查询模式耗尽，因为上一个查询的结果集尚未完全获取或关闭，而同一连接上尝试了新查询，MySQL 禁止此操作。

Official Documentation

https://www.php.net/manual/en/pdo.begintransaction.php

Workarounds

  1. 90% success Ensure all previous result sets are fetched or closed before executing a new query: $stmt = $pdo->query('SELECT * FROM users'); $rows = $stmt->fetchAll(); // fetch all rows to free the result; then execute next query. 
    Ensure all previous result sets are fetched or closed before executing a new query: $stmt = $pdo->query('SELECT * FROM users'); $rows = $stmt->fetchAll(); // fetch all rows to free the result; then execute next query.
  2. 85% success Close the cursor explicitly with $stmt->closeCursor() after fetching if not using fetchAll(), especially for unbuffered queries. 
    Close the cursor explicitly with $stmt->closeCursor() after fetching if not using fetchAll(), especially for unbuffered queries.
  3. 80% success Use separate PDO connections for concurrent queries: $pdo1 = new PDO(...); $pdo2 = new PDO(...); to avoid interference. 
    Use separate PDO connections for concurrent queries: $pdo1 = new PDO(...); $pdo2 = new PDO(...); to avoid interference.

中文步骤

  1. Ensure all previous result sets are fetched or closed before executing a new query: $stmt = $pdo->query('SELECT * FROM users'); $rows = $stmt->fetchAll(); // fetch all rows to free the result; then execute next query.
  2. Close the cursor explicitly with $stmt->closeCursor() after fetching if not using fetchAll(), especially for unbuffered queries.
  3. Use separate PDO connections for concurrent queries: $pdo1 = new PDO(...); $pdo2 = new PDO(...); to avoid interference.

Dead Ends

Common approaches that don't work:

  1. 70% fail

    Setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false globally may cause memory issues and does not force fetching; it only switches to unbuffered mode which still requires closing.

  2. 90% fail

    Simply restarting the database server clears all connections temporarily, but the code will trigger the same error on the next request if not fixed.