PHP PDO returning inconsistent results for SELECT FOUND_ROWS()

Question

I have an issue with PHP/PDO and MySQL

When I run a query with SQL_CALC_FOUND_ROWS and then select FOUND_ROWS(), 80% of the time its returning 0, and the rest of the time FOUND_ROWS is accurate

I've reduced it to a simple test loop, but this works fine on my dev server, but in production the test case is having the inconsistency problem.

Running the same queries from MySQL command line works correctly even in production, so it appears to be a PHP/PDO problem

PHP 5.5.28 - using mysqlnd 5.0.11-dev Percona Server 5.6.25-73.1-log on CentOS 6.6 (Final)

Can anyone help please? I've tried everything I can think of, and I'm tearing my hair out

<?php
require_once "../consts.php";

$nolimit_query = "select SQL_CALC_FOUND_ROWS targetusers.u_id
  FROM 
    users targetusers 
    LEFT JOIN user_extra targetuserextra ON (targetuserextra.ue_userid = targetusers.u_id) 
    LEFT JOIN countries ON (c_id = targetusers.u_country) 
    LEFT JOIN cities ON (cities.ct_countryid = c_id and cities.ct_id = targetusers.u_city) 
    LEFT JOIN userimages ON (targetusers.u_primaryimage = userimages.ui_id and userimages.ui_userid = targetusers.u_id and userimages.ui_imagetype = 'P') 
  WHERE 
    (targetusers.u_deleted = 0) and 
    (targetusers.u_id NOT IN (19, 32, 115)) and 
    (targetusers.u_active = 1) and 
    (targetusers.u_confirmed=5) order by u_lastupdated DESC LIMIT 10, 10";

// already tried this soln suggested elsewhere -- defaults to off for my PHP anyway though
ini_set("mysql.trace_mode", 0);
ini_set("display_errors", 1);

// set up PDO connection
$dbh = new PDO(
    "mysql:host=" . Config::Get()->DB_SERVER . ";dbname=" . Config::Get()->DATABASE_NAME . ";charset=utf8", 
    Config::Get()->DB_USERNAME, 
    Config::Get()->DB_PASSWORD
);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_PERSISTENT, false);

echo $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION) . "<br>";
echo $dbh->getAttribute(PDO::ATTR_DRIVER_NAME) . "<br>";
echo $dbh->getAttribute(PDO::ATTR_SERVER_INFO) . "<br>";
echo $dbh->getAttribute(PDO::ATTR_SERVER_VERSION) . "<br><br>";

echo date("r") . "<br>";
for ($i=0; $i<5; $i++)
{
    // run query above
    $stmt = $dbh->prepare($nolimit_query);
    if ($stmt === FALSE)
    {
        die($stmt->errorInfo());
    }

    // get result set 
    if (($result = $stmt->execute()) === TRUE)
    {
        $a = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $stmt->closeCursor();
        $stmt = NULL;

        // this query returns inconsistent results (80% of the time it returns 0!)
        $b = $dbh->query("SELECT FOUND_ROWS() as cnt")->fetch(PDO::FETCH_ASSOC);
        echo "FOUND ROWS = " . $b["cnt"] . "<br>";      
    }
}

The output of this script is below.

Note that only 2 of the 5 loops are returning the correct value for FOUND_ROWS, with the other 3 coming back as 0

mysqlnd 5.0.11-dev - 20120503 - $Id: 15d5c781cfcad91193dceae1d2cdd127674ddb3e $
mysql
Uptime: 1857223 Threads: 1 Questions: 4446069 Slow queries: 32 Opens: 465 Flush tables: 1 Open tables: 403 Queries per second avg: 2.393
5.6.25-73.1

Fri, 04 Sep 2015 12:01:10 +0100
FOUND ROWS = 0
FOUND ROWS = 0
FOUND ROWS = 59836
FOUND ROWS = 0
FOUND ROWS = 59836

Just a small point, you set errormode to Exception but you are not running the db code in a try/catch block. — RiggsFolly, Sep 04 '15 at 11:17
You are also `preparing` a statement within a loop which is unnecessary — RiggsFolly, Sep 04 '15 at 11:20
You are doing everything all right, as [using try catch for the basic error reporting is but a delusion](http://phpdelusions.net/try-catch) — Your Common Sense, Sep 04 '15 at 11:22
@Riggs, yes this is just the simplest test case I could come up with. In my real codebase I have exception handlers etc. Likewise for prepare(), in production I am binding params so prepare is required, whereas in this code I removed all that to simplify it — carpii, Sep 04 '15 at 11:23
I dont see why it should fail 3 out of 5 times, but you do run the `FOUND_ROWS` second query outside the `if` test that checks the first query, try moving the second query inside the first query check and also add an else to report whatever error may have happened to the first query — RiggsFolly, Sep 04 '15 at 11:48
I've finally got to the bottom of it (much to my relief). It seems the NewRelic Application Monitoring extension is somehow interfering with it. Sounds crazy but commenting their extension out of php.ini and suddenly FOUND_ROWS is working as expected again — carpii, Sep 04 '15 at 11:50

score 2 · Answer 1 · answered Sep 04 '15 at 12:01

2

Problem solved. It turns out the NewRelic Application Monitoring Daemon or extension was interfering with the results of FOUND_ROWS()

That's SO bad :(

Disabling the extension and FOUND_ROWS is bulletproof again

answered Sep 04 '15 at 12:01

carpii

1,917
4
20
24

1

newrelic are investigating. If anyone else encounters this problem, a workaround is to set newrelic.transaction_tracer.explain_enabled = false – carpii Sep 04 '15 at 14:43

PHP PDO returning inconsistent results for SELECT FOUND_ROWS()

1 Answers1

Linked