Asked  1 Year ago    Answers:  5   Viewed   8 times

I know this question has been asked many times, but I've read the answers to many of the questions and still cannot understand why I am receiving this error:

Fatal error: Uncaught exception 'PDOException' with message '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.'

The first thing that is odd, is that I do not get an error on my localhost (wampserver), but I do get it on my web server. The php version on my localhost is 5.3.10, and on my web server it is 5.3.13.

I have read that the source of this error is making a query when data left in the buffer from a previous query. This is not the case for me -- I have echo'd out all of the data and I know for a fact that every row returned in a query is being fetched.

With that said, I have found that changing one of my queries to fetchAll instead of fetch fixes the problem, but it simply makes no since because I know that all of the rows returned are being read. When I used fetchAll for the query (it is being made in a loop), I printed out the array each loop, and only one item was in the array for each query in the loop.

One more piece of information. It's not the query that I changed to fetchAll (which makes the error go away) that throws the PDO error, there is another query later in my php file that throws the error. My file is basically like this:

... code ...

query 1

... code ...

loop
query 2
end loop

... code ... 

query 3

If I comment out query 3, there is no error. If I comment out, or change to fetchAll, query 2, there is no error. query 1 has no affect whatsoever.

I would also like to add that I have tried adding LIMIT 1 to all of the queries on the page (at the same time), and the error is still there. I think this proves there is not unread data in the buffer, right?

I'm really confused, so I would appreciate your advice. Before someone asks, I can't post the full code for this, but here is a simplified version of my code:

$stmt = $this->db->prepare('SELECT ... :par LIMIT 1');
makeQuery($stmt, array(':par' => $var));
$row = $stmt->fetch(PDO::FETCH_ASSOC);


$stmt = $this->db->prepare('SELECT ... :par LIMIT 1');

for loop
    makeQuery($stmt, array(':par' => $var));
    $row2 = $stmt->fetch(PDO::FETCH_ASSOC);
    ... [use row2] ...
end for loop


$stmt = $this->db->prepare('SELECT ... :par LIMIT 1');
makeQuery($stmt, array(':par' => $var));
$row3 = $stmt->fetch(PDO::FETCH_ASSOC);

Here is makeQuery().

/**************************************************************************************************************
* Function: makeQuery                                                                                         *
* Desc: Makes a PDO query.                                                                                    *
* Pre conditions: The statement/query and an array of named parameters (may be empty) must be passed.         *
* Post conditions: The PDO query is executed. Exceptions are caught, displayed, and page execution stopped.   *
**************************************************************************************************************/
function makeQuery($stmt, $array, $errMsg = '')
{
    try 
    {
        $stmt->execute($array);
    }
    catch (PDOException $e) 
    {
        print $errMsg != ''?$errMsg:"Error!: " . $e->getMessage() . "<br/>";
        die();
    }
}

Thanks for your help!

EDIT: I also tried doing the following after query 2 (since that seems to be the source of the problem:

$row2 = $stmt->fetch(PDO::FETCH_ASSOC); var_dump($row2);

The output was:

bool(false) 

Have I stumbled across a PDO bug?

 Answers

2

you need to fetch until a row fetch attempt fails. I know you may only have one row in the result set and think one fetch is enough, but its not.

you probably have other statements where you didn't fully "fetch until a fetch failed". Yes, i see that you fetch until the fetch failed for one of the statements.

also, see closecursor()

$sql = "select * from test.a limit 1";
$stmt = $dbh->prepare($sql);
$stmt->execute(array());
$row = $stmt->fetch();
$stmt->closeCursor();

or

$sql = "select * from test.a limit 1";
$stmt = $dbh->prepare($sql);
$stmt->execute(array());
list($row) = $stmt->fetchAll(); //tricky
Thursday, April 1, 2021
 
dotoree
 
5

There's a conflict between the PHP that is used by Apache and the PHP that is linked to the command line. (It happens more often that it should to be honest).

What is typically done is:

which php

This tells you which php will be expecuted when running in the command line. e.g. /usr/bin/php

mv /usr/bin/php /usr/bin/php.old

Then link or copy the correct PHP version to an executable path:

ln -s /path/to/php/bin/php /usr/bin/php

or the following should also work.

cp /path/to/php/bin/php /usr/bin/php

Also suggested if you want to be able to manually install mods:

ln -s /path/to/php/bin/phpize /usr/bin/phpize
ln -s /path/to/php/bin/php-config /usr/bin/php-config

This way your CLI will match your webserver.

Update:

If as noted in this answer if you are using Ubuntu with multiple alternative installations of PHP you can do:

sudo update-alternatives --set php /usr/bin/php<version>
sudo update-alternatives --set phar /usr/bin/phar<version>
sudo update-alternatives --set phar.phar /usr/bin/phar.phar<version> 
sudo update-alternatives --set phpize /usr/bin/phpize<version> 
sudo update-alternatives --set php-config /usr/bin/php-config<version>
Thursday, April 1, 2021
 
2

Took a bit of fiddling, but I found that when I took the ATTR_EMULATE_PREPARES=false out (the default is to emulate), it worked:

<?php
$db = new PDO ($cnstring, $user, $pwd);
$db->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//$db->setAttribute (PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

$st = $db->query ("CREATE TRIGGER `CirclesClosureSync` AFTER INSERT ON Circles
FOR EACH ROW BEGIN
    INSERT INTO CirclesClosure (ancestor, descendant) 
    SELECT ancestor, NEW.ID from CirclesClosure WHERE descendant=NEW.Parent;
    INSERT INTO CirclesClosure (ancestor, descendant) values (NEW.ID, NEW.ID);
END;");

$st->closeCursor();
?>

Hope this helps someone

Thursday, April 1, 2021
 
5

You're right, that situation doesn't seem to throw exceptions nor trigger errors. PDOStatement::execute() at least returns false so you can roll your own:

$dsn = "mysql:host=$dbhost;dbname=$dbname;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $dbuser, $dbpass, $options);

$sql = 'SELECT ? AS foo';
$stmt = $pdo->prepare($sql);

if (!$stmt->execute([1, 2])) {
    throw new InvalidArgumentException('Failed to execute statement');
}
while ($row = $stmt->fetch()) {
    var_dump($row);
}

Not ideal but...

Thursday, April 1, 2021
 
jenny
 
1

You say that you posted a simplified version of the code. Did you change anything else when you posted it here? This error is normally caused when you have multiple queries "open" at the same time. For example, you call fetch(), but you don't call it until it's depleted, and then you try to retrieve a second query.

Judging by your code above, this shouldn't happen because you're using fetchAll(). Normally, the solution to this problem is to call closeCursor() [docs]. You could try calling that after each fetchAll and see if that does anything.

Saturday, May 29, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share