PDO Unbuffered queries

Question

I'm trying to get into PDO details. So I coded this:

$cn = getConnection();

// get table sequence
$comando = "call p_generate_seq('bitacora')";
$id = getValue($cn, $comando);

//$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (?, ?, ?)';
$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (:id, :estado, :fch_creacion)';
$parametros = array (
    ':id'=> (int)$id,
    ':estado'=>1,
    ':fch_creacion'=>date('Y-m-d H:i:s')
);
execWithParameters($cn, $comando, $parametros);

my getValue function works fine, and I get the next sequence for the table. But when I get into execWithParameters, i get this exception:

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. in D:\Servidor\xampp_1_7_1\htdocs\bitacora\func_db.php on line 77

I tried to modify the connection attributes but it doesn't work.

These are my core db functions:

function getConnection() {
    try {
        $cn = new PDO("mysql:host=$host;dbname=$bd", $usuario, $clave, array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            ));

        $cn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
        return $cn;
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }
}
function getValue($cn, $comando) {
    $resul = $cn->query($comando);
        if (!$resul) return null;
        while($res = $resul->fetch()) {
            $retorno = $res[0][0];
            break;
        }
        return $retorno;
}
function execWithParameters($cn, $comando, $parametros) {
    $q = $cn->prepare($comando);
    $q->execute($parametros);
    if ($q->errorInfo() != null) {
        $e = $q->errorInfo();
        echo $e[0].':'.$e[1].':'.$e[2];
    }
}

Somebody who can shed a light for this? PD. Please do not suggest doing autonumeric id, cause i am porting from another system.

What's the definition of p_generate_seq? SHOW PROCEDURE p_generate_seq — VolkerK, May 09 '09 at 10:03
the problem doesn't lie there, same error happens with SELECT MAX(id) FROM dsa_bitacora — Jhonny D. Cano -Leftware-, May 09 '09 at 12:06

score 23 · Accepted Answer · answered Aug 13 '09 at 02:54

The issue is that mysql only allows for one outstanding cursor at a given time. By using the fetch() method and not consuming all the pending data, you are leaving a cursor open.

The recommended approach is to consume all the data using the fetchAll() method. An alternative is to use the closeCursor() method.

If you change this function, I think you will be happier:

<?php
function getValue($cn, $comando) {
    $resul = $cn->query($comando);
    if (!$resul) return null;
    foreach ($resul->fetchAll() as $res) {
            $retorno = $res[0];
            break;
    }
    return $retorno;
}
?>

I do know use connection functions as above but have the same issue. The fetchALL() method does not seem to function on my machine properly. Any advice or alternatives since your answer? — JM4, Apr 05 '11 at 22:11

score 16 · Answer 2 · answered May 03 '11 at 03:25

16

I don't think PDOStatement::closeCursor() would work if you're not doing a query that returns data (i.e. an UPDATE, INSERT, etc).

A better solution is to simply unset() your PDOStatement object after calling PDOStatement::execute():

$stmt = $pdo->prepare('UPDATE users SET active = 1');
$stmt->execute();
unset($stmt);

answered May 03 '11 at 03:25

Jonathon Hill

3,445
1
33
31

Had the same problem in a php recursive function. Thanks for this answer problem solved like this: $rows = $sth->fetchAll(PDO::FETCH_ASSOC); unset($sth); foreach ($rows as $row). What I had previous : while ($row = $sth->fetch(PDO::FETCH_ASSOC)). Thank you! – Alqin Apr 02 '13 at 15:35

score 8 · Answer 3 · answered May 08 '09 at 19:58

8

The problem seems to be---I'm not too familiar with PDO--- that after your getValue call returns, the query is still bound to the connection (You only ever ask for the first value, yet the connection returns several, or expects to do so).

Perhaps getValue can be fixed by adding

$resul->closeCursor();

before the return.

Otherwise, if queries to getValue will always return a single (or few enough) value, it seems that using fetchAll will be preferred.

answered May 08 '09 at 19:58

Tordek

10,628
3
36
67

tx... I've just tryed the closeCursor and the fetchAll, and it keeps raising the same exception. – Jhonny D. Cano -Leftware- May 08 '09 at 20:03

score 5 · Answer 4 · answered Jun 30 '14 at 18:00

I just spend 15 minutes googling all around the internet, and viewed at least 5 different Stackoverflow questions, some who claimed my bug apparently arose from the wrong version of PHP, wrong version of MySQL library or any other magical black-box stuff...

I changed all my code into using "fetchAll" and I even called closeCursor() and unset() on the query object after each and every query. I was honestly getting desperate! I also tried the MYSQL_ATTR_USE_BUFFERED_QUERY flag, but it did not work.

FINALLY I threw everything out the window and looked at the PHP error, and tracked the line of code where it happened.

SELECT AVG((original_bytes-new_bytes)/original_bytes) as saving 
    FROM (SELECT original_bytes, new_bytes FROM jobs ORDER BY id DESC LIMIT 100) AS t1

Anyway, the problem happened because my original_bytes and new_bytes both where unsigned bigints, and that meant that if I ever had a job where the new_bytes where actually LARGER than the original_bytes, then I would have a nasty MySQL "out of range" error. And that just happened randomly after running my minification service for a little while.

Why the hell I got this weird MySQL error instead of just giving me the plain error, is beyond me! It actually showed up in SQLBuddy (lightweight PHPMyAdmin) when I ran the raw query. I had PDO exceptions on, so it should have just given me the MySQL error.

Never mind, the bottom line is:

If you ever get this error, be sure to check that your raw MySQL is actually correct and STILL working!!!

Just faced with this cryptic and useless error, and thanks to that answert I thought outside the box for a moment. `SHOW WARNINGS` on the same connection in which the query failed proved crucial to debug that. In my case, the error was: `Unknown or incorrect time zone: 'Europe/London'` which I defined when creating the PDO instance. Thanks @Henrik. — Diego Agulló, Oct 28 '16 at 19:12

score 0 · Answer 5 · answered Oct 25 '09 at 14:35

0

If you're using XAMPP 1.7.1, you just need to upgrade to 1.7.2.

answered Oct 25 '09 at 14:35

Khashayar

1,321
10
9

score 0 · Answer 6 · answered May 19 '09 at 08:11

0

A friend of mine had very much the same problem with the xampp 1.7.1 build. After replacing xampp/php/* by the 5.2.9-2 php.net build and copying all necessary files to xampp/apache/bin it worked fine.

answered May 19 '09 at 08:11

VolkerK

95,432
20
163
226

PDO Unbuffered queries

6 Answers6

Linked