Why does PDO::fetchAll() on an UPDATE prepared statement cause “unbuffered queries are active” exception?

^{PHP Version: PHP 7.4.13}

I have run into this issue where a fetchAll() is being called on a prepared statement, with an UPDATE query:

$stmt    = $db->prepare( "UPDATE `table` SET value = value + ? WHERE id = ?" );
$success = $stmt->execute( $arguments );

$result  = $stmt->fetchAll( PDO::FETCH_ASSOC );  // ← This causes the exception

While researching for causes for the MySQL error 2014 Cannot execute queries while other unbuffered queries are active exception, it seems that this case is not covered on other questions asked here.

Why would $stmt->fetchAll() on an UPDATE query cause the following exception to happen?

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. 

Specifically, I’d like to know what “other unbuffered queries are active” in this case is being referred to.

(I know it doesn’t make sense, it is invalid and should not be done, but what I’m asking here is why would it result in that exception, specifically that “unbuffered” queries are active.)

---

n.b. – Setting MYSQL_ATTR_USE_BUFFERED_QUERY to true does not affect what I’m asking here.

Answer

Generally, trying to fetch a result set from an UPDATE/INSERT statement is an invalid action. There is no result returned from MySQL, so there is nothing to fetch.

The exception you are seeing came about as a regression bug because we fixed a number of other bugs. One of them caused the exception to appear when there is no result set and yet you try to fetch it. This is an out of sync error.

However, given that the common usage in database abstraction libraries is to always fetch the result set and that the error message is very confusing, we decided to silence the error message within PDO. Now, if you try to fetch the data from the statement PDO will check internally if there is a result and if not it will return an empty array/null.

This bug is only present in PHP 7.4.13/8.0.0. It has been fixed in 7.4.14 already.