Cannot execute queries while other unbuffered queries are active

Question

Okay, so I've developed this website (amateurishly) using a local test server with PHP 5.4 (working 100%) but upon uploading to the remote host, I've had non-stop problems, likely all caused by the difference in PHP version. The remote server uses PHP 5.1. On this page, a value is passed from a previous page and this value is used to fill a form.

I've traced this problem by trial and error but have no idea what is causing it and have read at least 30 related SO questions so far. The code on this page immediately stops executing at the prepare statement. I cannot get any type of error message.

I know that the $link connection is open because I have a query preceding this that works perfectly.

if (('POST' === $_SERVER['REQUEST_METHOD']) && (isset($_POST['details'])))
{
$sql='select * from this where that=:that';
echo $sql; //added for testing
$query=$link->prepare($sql);
echo "this never shows up"; //verified problem area
$query->bindValue(':that', $_POST['details']);
$query->execute();
$row=$query->fetch();
//more things
}

All help appreciated!

EDIT: Not paying proper attention, error:

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

Very obscure error that goes away when I comment out the preceding query. This is odd because the preceding query is on several pages and has been without issue. Additionally, it returns only a single result, so I'm not sure what this error really means. Here is the preceding query:

$sql='select bgcolor from settings inner join users on id=userid where username=:user';
$query=$link->prepare($sql);
$query->bindValue(':user', $_SESSION['Username']);
$query->execute();
$row=$query->fetch();
$rgb = str_split($row['bgcolor'], 3);

It runs on every page (because I'm new and inefficient and don't save SESSION and COOKIE) and works fine before this. It only returns one result though.

I guess syntax error in the query, see [Errors and error handling](http://php.net/manual/en/pdo.error-handling.php) — dlyaza, Jul 27 '14 at 07:07
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); And works perfectly fine on localhost. Must be a difference in PHP issue. (Last problem was filter_var not supported before 5.2 :( ) — user3720386, Jul 27 '14 at 07:11
You edit makes it sound like there is a result waiting to be enumerated. See this answer: http://stackoverflow.com/a/17582620/534109 — Tieson T., Jul 27 '14 at 07:47
Do you have a pending query that runs before the code you've shared? — Álvaro González, Jul 27 '14 at 08:14
Please add the preceding query and the code which fetches its result to your question. — Naktibalda, Jul 27 '14 at 11:42
Does the first query return only one row then you run it from your MySQL client? — Álvaro González, Jul 27 '14 at 16:52
With this new error information, there is a lot more reading of related posts I can do, however, as noted, when I comment out the query, the next one works -- but then the 3rd fails. From the first reading, closeCursor() should fix my problem, but I want to understand why I only occasionally have this problem before I start implementing this "fix" on every page. — user3720386, Jul 27 '14 at 17:14
I'd start by checking whether `settings` has at most one row for every user. — Álvaro González, Jul 27 '14 at 17:32
It's a new project, so {settings} only contains one row total anyway. — user3720386, Jul 27 '14 at 18:15

user3720386 · Accepted Answer · 2014-07-27T18:35:51.543

3

I've used $query->closeCursor();, as noted in some of the linked SO questions, for every select query executed on this page to reach working status.

This is less than ideal and I wish we could determine a real cause and solution for this problem. Unfortunately I don't have server config access to check some of the other solutions.

This also worked, but some comments state that it didn't work for them:

$link->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

edited Jul 27 '14 at 18:35

answered Jul 27 '14 at 18:23

user3720386

65
9

This is actually the correct solution. MySQL requires that you close one query before starting another. The error you were getting was just PDO stating that fact. You can have PDO automatically close the cursor via $cursor->fetchAll(), or by setting $link->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true), but both of these then read your entire dataset into memory, which is a problem if you have a lot of rows. Fetching one by one and then closing the cursor when you are done is the preferred method. – Cuagau Oct 13 '14 at 21:33

score 3 · Answer 2 · answered Aug 11 '14 at 22:55

This is not necessarily the answer to this question, but this may help somebody in the future.

I came across exactly the same error and it took hours to discover what was wrong. It turned out it was just a extremely minor syntax issue all along. If you're not actually using any buffering, but still have this error, like I did, this could be your issue - so check your code.

I was doing my normal database queries when I came across this error -- not purposely using any buffering techniques -- so I highly doubted it had anything to do with buffering. I read every SO question about it and looked deeper in to it.

This was my STUPID syntax issue:

$SQL = "UPDATE articles SET
            topicID = :topic;    <-------- semicolon - woops!
            heading = :heading,
            subheading = :subheading,
            keywords = :keywords,
            rawContent = :rawContent,
            content = :content,
            ...
            ...

This resulted in me getting this buffering error. I fixed the code and it went away. What was most annoying, was the fact the PDO error was pointing at another query, the next query, but that query was in a function elsewhere in the code, and that through me well off course for a while!

Cannot execute queries while other unbuffered queries are active

2 Answers2