1

According to this answer: here

The use of SQL_CALC_FOUND_ROWS is not subject to race conditions. The result of the SELECT query, yes, but not the result of FOUND_ROWS(). Don't confuse this.

But from all the queries I have ran it is subject to race conditions and every time I use SQL_CALC_FOUND_ROWS I run into those issues a good amount of the time for some reason where my results from select found_rows() as rows returns 0

Please let me know if you have more information on this subject.

Using --> Mysql 5.6 (Aurora RDS)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Taylor Austin
  • 5,407
  • 15
  • 58
  • 103
  • Please include the series of queries you are running which demonstrates the problem you mention in the question. – Tim Biegeleisen Jul 17 '19 at 15:06
  • Note that `SQL_CALC_FOUND_ROWS` has been deprecated in MySQL 8.0. So you should probably get out of the habit of using it, and this question will be moot. – Barmar Jul 17 '19 at 15:09
  • @Barmar what is used in 8.0? – Taylor Austin Jul 17 '19 at 15:10
  • 1
    Just make a second query that uses `SELECT COUNT(*)` and the same conditions. It's in the documentation: https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows – Barmar Jul 17 '19 at 15:11
  • 1
    LOL, just noticed a bug in the documentation: they forgot the `FROM` clause in the `COUNT(*)` query. – Barmar Jul 17 '19 at 15:13
  • I heard using count(*) can be costly on performance if not using an indexed column or something ? – Taylor Austin Jul 17 '19 at 15:23

1 Answers1

1

Aurora is not MySQL.

Amazon has ripped out a lot of code and features from the storage engine, but they haven't disabled the corresponding functions or status variables or configuration variables.

There are a bunch of features familiar to MySQL users that are still usable on Aurora, but they have no effect. Like the steering wheel on one of those coin-operated kiddie rides.

enter image description here

Aurora does not provide complete documentation on which features are still functioning and which ones are "disconnected" in this way.

In the case of SQL_CALC_FOUND_ROWS, it might still work, depending on your Aurora version. I found a reference to it in their release notes:

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.20170515.html

Fixed a regression, introduced in Aurora MySQL version 1.11, in which queries can return incorrect results when using the SQL_CALC_FOUND_ROWS option and invoking the FOUND_ROWS() function.

Is it possible you're using an Aurora instance that is affected by this bug, or is it possible that the bug has regressed again in the version you're using?

You need to double-check the available Aurora information, and if you can't find what you're looking for, contact AWS support.

And don't assume that something that works in MySQL will work the same way in Aurora. They are effectively different software products. MySQL is open-source, Aurora is not.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828