Reputation: 711
I've found the fix to my issue, but this is more of a question as to why. Here's my scenario..
I'm using the following code on a table containing thousands of records:
$stmt = $pdo->prepare("SELECT SQL_CALC_FOUND_ROWS * FROM ... LIMIT 10;");
$stmt->execute();
echo $pdo->query("SELECT FOUND_ROWS();")->fetchColumn(); # outputs 0
The above should output the actual number of records but instead it outputs 0.
Now if I add a trailing space at the end of the SQL_CALC_FOUND_ROWS query it works properly..
... LIMIT 10; ");
Any ideas as to why this would be??
UPDATE
I've tried running the query without using PDO's prepare and it seems to work just fine as well..
$stmt = $pdo->query("SELECT SQL_CALC_FOUND_ROWS * FROM ... LIMIT 10;");
echo $pdo->query("SELECT FOUND_ROWS();")->fetchColumn(); # works properly
I'm still trying to reproduce this on a smaller level so others can try it out and see if it happens.
UPDATE 2
Well the closest I can get to reproducing this issue is using the following code:
# not working..
$stmt = $pdo->prepare("SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 10");
$stmt->execute();
echo 'COUNT1: ' . $pdo->query("SELECT FOUND_ROWS()")->fetchColumn() . '<br>';
# working (notice the space before SELECT)..
$stmt = $pdo->prepare(" SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 10");
$stmt->execute();
echo 'COUNT2: ' . $pdo->query("SELECT FOUND_ROWS()")->fetchColumn() . '<br>';
# output (from my database)
# COUNT1: 0
# COUNT2: 182020
It's only happening on the larger tables that contain around a minimum of ~50 MB of data. I cannot reproduce this locally either so I'm thinking it's some sort of configuration on the server. Here's what I'm using..
Any ideas/thoughts are most welcome!! Also wondering if anybody out there can successfully reproduce this issue as well (remember to test it on a larger table)..
Upvotes: 4
Views: 2171
Reputation: 711
Well looks like the culprit was New Relic. I disabled the daemon quick to see if that was the issue and counts are perfect again.
Found my answer here: PHP PDO returning inconsistent results for SELECT FOUND_ROWS()
I decided to wrap my queries with a single space at the beginning and end (only beginning is necessary). Note that "\n" characters work as well. This way I didn't have to mess with the New Relic configuration.
Thanks New Relic!
Upvotes: 3