Why Does FOUND_ROWS() Keep Returning 1? CodeUnit 16 MAR 2010

MySQL comes with the handy little function known as SQL_CALC_FOUND_ROWS which can be used in normal LIMITed SQL statements to force the DBMS to calculate the number of rows fetched had the LIMIT statement not been applied and leave the result for a later FOUND_ROWS() call to be applied.

Quite useful for pagination really.

Anyhow, if you are using PHP and connecting to MySQL, you’ll sometimes find that using the FOUND_ROWS() call after running a SQL_CALC_FOUND_ROWS statement previously will continuously bring up the result of 1 – which is obviously utterly incorrect.

It turns out that there exists an acknowledged bug that if the PHP .ini setting of mysql.trace_mode is set to true, i.e. on, FOUND_ROWS() will always return 1.

So the solution? Well considering that mysql.trace_mode simply controls the display of warnings for table/index scans, non free result sets and SQL errors, you could probably just turn it off I suppose.

Related Link: http://bugs.php.net/bug.php?id=33021

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.