Statement Based Replication and SQL_CALC_FOUND_ROWS

We started using statement-based replication, and found that it breaks SQL_CALC_FOUND_ROWS and FOUND_ROWS(), and I'm looking for a work around. A comment from a bug report in 2007 suggests using SELECT开发者_运维问答 SQL_CALC_FOUND_ROWS INTO @found_rows FROM foo, but that doesn't seem to work. MySQL says the syntax is invalid.

If you're using an older version of MySQL, that could be part of your problem: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows

FOUND_ROWS() is not replicated reliably using statement-based replication. Starting with MySQL 5.1.23, this function is automatically replicated using row-based replication.

Edit: The above only works if you're in MIXED mode.

Also, the correct workaround syntax is:

SELECT SQL_CALC_FOUND_ROWS * FROM foo;
SET @found_rows = FOUND_ROWS();

SQL_CALC_FOUND_ROWS doesn't return anything, it just tells MySQL to calculate the number of found rows from the query, even if a LIMIT clause prevents all of them from being returned to the client. FOUND_ROWS() can then be used to return the value that was temporarily stored by SQL_CALC_FOUND_ROWS.

Edit: The idea behind the above workaround (as documented in MySQL bug 12092):

The result of FOUND_ROWS() is stored into a user variable and used that way instead. This will replicate correctly even under statement-based replication, since it will write a User_var entry to the binary log.