无缓冲的查询错误 - 如何在localhost上获取它们

Question

Sometimes I'm getting this error (on remote server only):

General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll()...

I added this line on top of my php code - without success:

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute;

Two questions:

• How to properly avoid this error, if possible without using fetchAll()?

• What should I do to get this error on my localhost, if the error exists?

I'm on Windows 7, xampp, phpmyadmin...

here is the code

$st = $db->query("select title from folders where id = " . $_GET['fd'] . " limit 1");
$st->execute();
$title = $st->fetchColumn();
$stb = $db->query("select * from arts where folder = " . $_GET['fd'] . " order by ind asc"); 

error is on the last line

Answer 1

Simple answer

1. Create a new (additional) connection.

Once your in the middle of an un-buffered request that connection is locked/block up. Pretty much unusable.

1. Or don't do it (while the buffered query is on-going).

So if you need to query other things, either do that before the unbuffered part, or open a second buffered (normal) connection to the DB.

I once pulled 140 million rows out of the DB with an un-buffered query. Now they are in MongoDB (even though I have mixed feelings about this). PS. if anyone knows how to improve mongo's count performance I am all ears.

You only really need un-buffered query if your working with a Large dataset, and I mean > 500k rows. I use it as a last resort.

Anyway, good luck. It's a pain.

Update

For you case, you need to make the PDOStatement object go away or call PDOStatement::closeCursor.

$st = $db->query("select title from folders where id = " . $_GET['fd'] . " limit 1");
$st->execute();
$title = $st->fetchColumn();
unset($st); //<---- unset the statement object

$stb = $db->query("select * from arts where folder = " . $_GET['fd'] . " order by ind asc"); 

If I am thinking about it right, that unset should take care of it. Normally this happens when it goes out of scope such as the end of a method or function and no reference to it. This is sort of like free_result or whatever it was for Mysqli

I just assumed you had something like this:

$st = $db->query("select title from folders where id = " . $_GET['fd'] . " limit 1");
$st->execute();
foreach($st->fetchColumn() as $col){
     $stb = $db->query("select * from arts where folder = {$col} order by ind asc"); 
}

Where you were using the results of an open Query for a new query. Which is a no-no. In a case like above the only thing you can do is open a new connection for the part in the loop as it may not be possible to do it all in one Query (for whatever reason).

In any case change this to proper prepared statements:

$st = $db->prepare("select title from folders where id = :id limit 1");
$st->execute(['id' => $_GET['fd']]);

As it is your vulnerable to SQLInjection. It's very easy to fix in PDO, and it's very bad to not do it.

Cheers!