PHP's PDO query not executing when LIMIT is too high?

Question

I have the weirdest PHP PDO problem, and I hope you guys can sort it out for me.

If I set $checkLimit to 50000, the query works fine. However, if I set it to anything above 50k, it doesn't return any results - and it doesn't cast any error messages either (I've already turned them on using $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING).

$sql = "
   SELECT d_domain_name AS domainName, d_domain_id AS domainID
   FROM domains
   ORDER BY d_domain_name_length ASC, d_domain_name ASC
   LIMIT :checkLimit
";
$stmt = $db->prepare($sql);
$stmt->bindValue(':checkLimit', intval($checkLimit), PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll();
foreach ($results as $result) {
 // 50k moments of magic
}

If I run the query outside of PHP, it works with any limit (even 500k, takes about 3 minutes).

I've tried changing $results = $stmt->fetchAll() to while ($result = $stmt->fetch()) {} in order to try and save memory, but that didn't do anything, unfortunately.

Can anyone tell me what I'm doing wrong here? What am I missing? Why can't I go over 50k?

Answer 1

Reference: http://php.net/manual/en/mysqlinfo.concepts.buffering.php

PDO uses "buffered query" by default.

This means that query results are immediately transferred from the MySQL Server to PHP in is then kept in the memory of the PHP process. .... The downside of the buffered mode is that larger result sets might require quite a lot memory. ....

Following these characteristics buffered queries should be used in cases where you expect only a limited result set or need to know the amount of returned rows before reading all rows. Unbuffered mode should be used when you expect larger results.

50k is a large result set. Could you try to let pdo use unbuffered mode and fetch one row at a time? This is the example copied from the reference. The 2nd line sets the unbuffered mode.

<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
   while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
       echo $row['Name'] . PHP_EOL;
   }
}
?>