10

We have a laravel 4.1 application that used to run under PHP 5.4, however since upgrading to 5.6.13 (And today to 5.6.14) I've noticed that queries have started to sometimes return 0 for FOUND_ROWS(). On some of our queries it seems to be intermittent, however on others it's more of a permanent issue.

The biggest impacted sets are those with sub queries.

We're using PDO (we're not using the laravel models, just interacting directly with its PDO object). MySQL hasn't been modified in this time frame either.

Tried all sorts - one suggestion was to set trace mode to 0, that didn't help though. I tried setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false, however that leads to a PDO error when you try and select FOUND_ROWS() (Can't get the exact message right now).

Short of rolling back to 5.4 (please God no), I'm completely stuck...

Running these queries directly in MySQL, and then running FOUND_ROWS() always returns the correct results.

user2094178
  • 9,204
  • 10
  • 41
  • 70
BenOfTheNorth
  • 2,904
  • 1
  • 20
  • 46
  • does the PDO object return the correct numbers of actual data rows, as in, you ask for returning number of users and it says in `FOUND_ROWS()` 0 users but returns you 4 data rows, for 4 different users? – Martin Dec 04 '15 at 20:07
  • Everything else works fine yeah - if my query has 45 results, I get the first 30 (from the limit), then run found rows and get 0 back. – BenOfTheNorth Dec 04 '15 at 20:09
  • 1
    CAn you edit your question with the relevant code? – Mihai Dec 07 '15 at 09:24
  • Not really as this happens on multiple queries, and only in one environment - also its intermittent and sometimes works (so I know the query isn't the issue). It also all works fine when using MySQL direct. – BenOfTheNorth Dec 07 '15 at 09:24
  • We would need to see the code. Laravel has internal mechanisms that allow for "event" triggering and executing, it could be that after you're done with your query, PDO object gets altered and subsequent calls to what you need are lost. Can you isolate reproducing code and environment? The "sometimes work,s sometimes doesn't" isn't sufficient for any kind of debugging, you will have to isolate and be able to repeat the case where you get false result back. – Mjh Dec 07 '15 at 09:34
  • Just for kicks, have you tried mysqli? – Machavity Dec 08 '15 at 20:09
  • Version confusion: "5.4" sounds like PHP; "5.6.13" sounds like MySQL. Please clarify all instances of version numbers in your question. – Rick James Nov 05 '16 at 19:08

4 Answers4

5

New Relic has issues

New Relic Application has some issues. NewRelic Application Monitoring Daemon or extension is interfering with the results of FOUND_ROWS()

The current workaround is =>

newrelic.transaction_tracer.explain_enabled = false

How to use FOUND_ROWS

It's not clear yet what your sql PDO queries are. Still trying my way of using FOUND_ROWS() with PDO. Check if you are using same way or please provide some queries your are trying.

$db = new PDO(DSN...); 
$db->setAttribute(array(PDO::MYSQL_USE_BUFFERED_QUERY=>FALSE)); 
$rs  = $db->query('SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 5,15'); 
$rs1 = $db->query('SELECT FOUND_ROWS()'); 
$rowCount = (int) $rs1->fetchColumn();
Luís Cruz
  • 14,780
  • 16
  • 68
  • 100
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
0

Well, it turns out this was due to the New Relic PHP plugin. Disabling this immediately fixed the issues for all of the FOUND_ROWS() queries that were returning zero.

BenOfTheNorth
  • 2,904
  • 1
  • 20
  • 46
0

This has been notified to New Relic just recently so it's now a issue filed with the developers.

You can just disable explain_plans in the newrelic.ini configuration and this will allow you to solve your problem and keep APM reporting from New Relic without having to remove it entirely, you would then only loseexplain plans on slow query report page, you otherwise should have all New Relic functionality until we release an agent that includes a fix.

If you come across an issue that you feel would require uninstalling New Relic entirely, reach out to us either through support.newrelic.com if you're a New Relic customer or on a pro trial or discuss.newrelic.com as we will actively monitor and engage on our public forums and bring this to the attention of our developers as required.

Thanks for all the good information in this thread, I'll be feeding it all back to help towards finding the root cause and resolving this issue.

Aidan Cuffe
  • 54
  • 5
0

Just to let you know. I have encountered the same problem and I can confirm that the problem is fixed in/after PHP Agent 6.6.0.169 (New Relic). I'm currently using PHP Agent 6.7.0.174 and it's working just fine in terms of FOUND_ROWS() issue.

Cheers!

Stefan Atanasov
  • 1