FOUND_ROWS() returning incorrect value I have 5 databases, all of which have the same structure. One is local (MySQL version 5.6.17), the other four are on the same server (MySQL 5.6.11-log). Let's call them A, B, C and C1. C had some of its data imported from B, and C1 is a test database whose structure and data was imported from C.

I have the following query:

SELECT SQL_CALC_FOUND_ROWS
    customergroups.customergroupID AS 'Id',
    customergroups.customergroup AS 'Name'
FROM
    customergroups
ORDER BY customergroups.customergroupID ASC
LIMIT 10;
SELECT FOUND_ROWS();


It works correctly on local, A and B returning expected values (261, 278 and 36 respectively) however it returns 10 for C and C1 (instead of 14 and 16 respectively). Removing ORDER BY customergroups.customergroupID ASC fixes it, same as leaving only SELECT SQL_CALC_FOUND_ROWS customergroups.customergroupID AS 'Id' FROM customergroups

Oddly enough, a similar query on different tables on all databases work correctly:

SELECT SQL_CALC_FOUND_ROWS
    customers.customerID AS 'Id',
    customers.customer AS 'Name'
FROM
    customergroups
ORDER BY customers.customerID ASC
LIMIT 10;
SELECT FOUND_ROWS();


I am aware there is/was a relevant bug in MySQL (https://bugs.mysql.com/bug.php?id=68458) but I would prefer to avoid upgrading MySQL, especially as the query works correctly on other databases on the same server running the same MySQL version and it would take likely several hours if not longer to back everything up and then restore it due to a ton of data in some tables.

I have already compared the structure of the tables and they are identical (data is different though). Any idea what could be causing this weird behaviour and how to fix it? Adding GROUP BY to the statement didn't help, neither did optimize table customergroups.
Seems similar to this Bug #69166:  Wrong result of FOUND_ROWS() in MySQL 5.6:


  Description:
  
  Executing FOUND_ROWS() after SELECT SQL_CALC_FOUND_ROWS ... returns wrong result under certain conditions.
  Might be related to Bug #68458
  
  See steps to reproduce. ...


and of Bug #69119: Wrong FOUND_ROWS() on MySQL 5.6.11


  Description:  
  
  FOUND_ROWS() return is wrong count.


It seems they have not been fixed.

In any case, it would be a good idea to upgrade to latest 5.6 (5.6.33 currently). Both of your installations use very old versions. After upgrading check again and if the error persists, report it in the above bug item (69119).

Related to the upgrade: I don't think you have to restore for an upgrade in same major version. I's good idea to take a backup first, before the upgrade procedure, just in case something goes wrong.