0

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.

David Refoua
  • 3,476
  • 3
  • 31
  • 55
  • `update` statements do not return a recordset as such – Professor Abronsius Jan 02 '21 at 12:25
  • @ProfessorAbronsius To be clear – I'm aware of that, I just don't know why it would throw **that** exception ("unbuffered queries"), specifically. – David Refoua Jan 02 '21 at 12:29
  • Is this not the question/answer you are looking for?: [Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active](https://stackoverflow.com/questions/17434102/causes-of-mysql-error-2014-cannot-execute-queries-while-other-unbuffered-queries) – Luuk Jan 02 '21 at 12:30
  • 3
    Because it's a big that will be fixed in the upcoming PHP version. That said, you shouldn't call fetchAll on the update statement. – Your Common Sense Jan 02 '21 at 12:33
  • @YourCommonSense If it exists over the PHP bug tracker, can you please post a link? – David Refoua Jan 02 '21 at 12:48
  • Also, to be clear, I'm aware that fetchAll() on update does _not_ make sense, and shouldn't be called on it. What I am asking is that why such an exception would be thrown. – David Refoua Jan 02 '21 at 12:48
  • @Luuk No, that was the first question that I checked, and is not related to my case. The accepted answer actually explains the case fully. – David Refoua Jan 02 '21 at 13:01

1 Answers1

4

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • 1
    TBH, only a very poorly written database abstraction library would ever try to fetch from a DML query – Your Common Sense Jan 02 '21 at 12:42
  • 1
    Not really, turns out that a lot of them do, as it makes coding easier. Whether it's right or wrong is a separate matter. – Dharman Jan 02 '21 at 12:45
  • 1
    That's what I am talking about. "Making coding easier" is the exact definition of a poorly written library. – Your Common Sense Jan 02 '21 at 12:46
  • @Dharman – Thanks for the quick response, and the work you do. It is unfortunate that my production server was updated to that specific PHP version (`7.4.13`) that contains this regression. The new behavior I believe is much more inline with what is expected by the user. – David Refoua Jan 02 '21 at 12:57
  • @Dharman To be clear, is this behavior consistent with versions both _before_ and _after_ 7.4.13? i.e., was this also present in `7.4.12` and before? – David Refoua Jan 02 '21 at 12:58
  • @YourCommonSense What is the best practice in such a case? Should the db abstraction library try to detect the _kind_ of the query that is being performed, or is there another way to check for this kind of fault? – David Refoua Jan 02 '21 at 12:58
  • 1
    @DavidRefoua Sniffing out the query type is a bad code as as well. In my opinion, a database abstraction library should have distinct functions for each task. Like a generic query method that just runs a query; and specific methods to perform different kinds of queries. This setup would follow SOLID principles, and make your code readable and more predictable. – Your Common Sense Jan 02 '21 at 13:03
  • @DavidRefoua No, it isn't. Emulated prepared statements threw an exception in such cases before 7.4.13. In 7.4.14 both emulated and native PS don't report an error for these type of queries anymore. – Dharman Jan 02 '21 at 13:06