Do unbuffered queries for one request
You can set the attribute on the PDO connection:
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
then run this particular query which result needs to be unbuffered,
$uresult = $pdo->query("SELECT Name FROM City");
while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
echo $row['Name'] . PHP_EOL;
}
and then set the attribute back
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
PDO Unbuffered queries
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;
}
?>
PDO MYSQL_ATTR_USE_BUFFERED_QUERY Not taking affect
The PDO::ATTR_PERSISTENT
value is not boolean. It identifies the connection being used, use unique values for multiple connections. In my case:
$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => 'unbuff', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
$db_ub = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => 'buff', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
PDO failing with too many records, buffered queries
When you use an unbuffered query, that means your result set is being streamed in from the MySQL server. So, the connection on which the (unbuffered) query runs is busy until you read the last row of the query. In your case the connection is $MysqlConn
.
(A buffered query slurps the entire resultset into your php program's RAM and frees up the connection. You use unbuffered queries when your whole result set doesn't fit in RAM).
Unbuffered queries should be closed explicitly when you're done with them. So add a closeCursor()
call. Like this.
while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
$order_ids[] = $row['order_id'];
}
$ordStat->closeCursor();
There's no harm in closing buffered queries too. It's a good habit.
PHP PDO Buffered query problem
If I understand this right, buffered queries involve telling PHP that you want to wait for the entire result set before you begin processing. Prior to PDO, this was the default and you had to call mysql_unbuffered_query
if you wanted to deal with results immediately.
Why this isn't explained on the PDO MySQL driver page, I don't know.
Related Topics
Cannot Initialize Mbstring with PHP 7
Fatal Error: Uncaught Argumentcounterror: Too Few Arguments to Function
In PHP, Is There Any Harm in Running Session_Start() Multiple Times
Set Maximum Execution Time in MySQL/Php
PHP Readfile() Adding Extra Bytes to Downloaded File
How to Use MySQL Found_Rows() in PHP
Using Pdo Prepared Statement and Incrementing a Column Value
Serial Comm with PHP on Windows
Best Method for Converting a PHP Array to JavaScript
How to Parse HTML Table Using PHP
Convert an Array of Strings, Each String Has Dot Separated Values, to a Multidimensional Array
Processing Multidimensional JSON Array with PHP
How to Add a Condition Inside a PHP Array