4

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..

  • Red Hat Enterprise Linux Server release 7.1 (Maipo)
  • Server version: Apache/2.4.6
  • mysql Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using EditLine wrapper
  • PHP 5.4.16 (cli)

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)..

Rick
  • 712
  • 5
  • 23
  • try `var_dump($pdo->query("SELECT FOUND_ROWS();")->fetchColumn();)` instead of `echo` – bassxzero Feb 21 '17 at 19:23
  • http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count – RiggsFolly Feb 21 '17 at 19:27
  • I'm guessing it has to do with the unnecessary semicolon included at the end of the SQL text. Why is there a semicolon following `LIMIT 10`? – spencer7593 Feb 21 '17 at 19:27
  • http://php.net/manual/en/pdo.error-handling.php anything from that, if you're not checking for errors – Funk Forty Niner Feb 21 '17 at 19:29
  • agreed `SELECT FOUND_ROWS();` < the semi-colon for it is an end of statement character and could be at fault here. – Funk Forty Niner Feb 21 '17 at 19:30
  • var_dump gives me string(1) "0" – Rick Feb 21 '17 at 19:31
  • also happens both with or without the semicolons. the query still works just fine.. it returns all the results expected. it's just that FOUND_ROWS() comes up empty for some reason.. unless I add that trailing space or \n even – Rick Feb 21 '17 at 19:32
  • 1
    I concur, this is strange. I wouldn't expect that the absence of a trailing space on a query would make a difference. I'm going to assume that there isn't some other "hidden" SELECT running between the `SELECT SQL_CALC_FOUND_ROWS and `SELECT FOUND_ROWS()`. From the evidence presented, it's pretty clear there isn't an intermediate SELECT when the query text includes a trailing space. I think my next step in debugging would be to turn on general_log to capture every SQL statement issued by the session (closest we get to emulating a session trace.) I hate to jump on the "it's a bug" bandwagon... – spencer7593 Feb 21 '17 at 21:38
  • @Rick: I'm wondering if you've also tested with `PDO::ATTR_EMULATE_PREPARE`, to see if that makes a difference. The semicolon at the end of the SQL statement still makes me shudder... it makes it look like we're trying to execute multiple SQL statements in a single execution. I believe that is not supported by PDO MySQL, but other PDO drivers for other DBMS do. – spencer7593 Feb 21 '17 at 22:28
  • I just tested with PDO::ATTR_EMULATE_PREPARES and the problem is still there.. And about the semicolon's, the problem is still there with or without them.. – Rick Feb 21 '17 at 22:39
  • I can't reproduce this, but there is one thing you didn't do - you `prepare` the statement but you never check whether it prepared / executed successfully. You proceed to invoke `FOUND_ROWS()` which returns `0`, meaning previous query 1) didn't find records or 2) didn't execute at all. Set PDO to exception mode and use try/catch to discover whether something goes wrong with prepared statement(s). – Mjh Feb 22 '17 at 14:49
  • I am initiating PDO using "PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION". And the initial query does find records. I can fetch the results and they display just fine. The ONLY issue is that FOUND_ROWS() does not get a count without adding a leading space within the prepare() PDO method. I'm baffled! – Rick Feb 22 '17 at 15:15

1 Answers1

3

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!

Community
  • 1
  • 1
Rick
  • 712
  • 5
  • 23