Rick
Rick

Reputation: 711

PHP PDO SQL_CALC_FOUND_ROWS / FOUND_ROWS() strange issue

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

Answers (1)

Rick
Rick

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

Related Questions