MySQL Bugs: #12092: FOUND_ROWS() not replicated

Bug #12092	FOUND_ROWS() not replicated
Submitted:	21 Jul 2005 20:40	Modified:	29 Nov 2007 11:23
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0, 4.1, 5.0	OS:	Any (all)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
INSERT INTO mytable VALUES(FOUND_ROWS())
depends on a previously executed SELECT SQL_CALC_FOUND_ROWS.
But as that SELECT is not into the binlog, FOUND_ROWS() is not going to give the correct value when the binlog is replayed (for a restore or by a replication slave).
Even if the SELECT was written to the binlog, another SELECT SQL_CALC_FOUND_ROWS of another conection, if it gets binlogged between the SELECT and the INSERT, will influence the FOUND_ROWS()  of the INSERT.

How to repeat:
Do on master:
CREATE TABLE mytable (a INT);
INSERT INTO mytable VALUES(1),(2);
SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
INSERT INTO mytable VALUES(FOUND_ROWS());
see that on master 2 is inserted by the above statement, while on slave it's a different value.

This is not replicated with rbr either:

:~/jmiller/mysql5.0/mysql-test> cat ./t/rpl_row_sp008.test
#############################################################################
# Original Author: Jonathan Miller                                          #
# Original Date: Aug/15/2005                                                #
#############################################################################

# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc
-- source include/have_innodb.inc

# Begin clean up test section
connection master;
--disable_warnings
DROP PROCEDURE IF EXISTS test.p1;
DROP TABLE IF EXISTS test.t2;
--enable_warnings
# End of cleanup

# Begin test section 1
CREATE TABLE test.t1 (a INT,PRIMARY KEY(a));
CREATE TABLE test.t2 (a INT,PRIMARY KEY(a));
INSERT INTO test.t1 VALUES(1),(2);

delimiter |;
CREATE PROCEDURE test.p1()
BEGIN
 SELECT SQL_CALC_FOUND_ROWS * FROM test.t1 LIMIT 1;
 INSERT INTO test.t2 VALUES(FOUND_ROWS());
END|
delimiter ;|

let $message=< ---- Master selects-- >;
--source include/show_msg.inc
CALL test.p1();
SELECT * FROM test.t2;
sleep 6;

let $message=< ---- Slave selects-- >;
--source include/show_msg.inc
connection slave;
SELECT * FROM test.t2;

connection master;
show binlog events;

DROP PROCEDURE IF EXISTS test.p1;
DROP TABLE IF EXISTS test.t1;
***************************** RESULTS ********************************
+ < ---- Master selects-- >
+ -------------------------
+ CALL test.p1();
+ a
+ 1
+ SELECT * FROM test.t2;
+ a
+ 2
+
+ < ---- Slave selects-- >
+ ------------------------
+ SELECT * FROM test.t2;
+ a

In order to make statement-based replication work (on 5.0 as well as on 5.1), the workaround is to store the result of the FOUND_ROWS() call in a user variable and then use that in the statement. For example, instead of writing::

    SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
    INSERT INTO mytable VALUES(FOUND_ROWS());

one should write::

    SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
    INSERT INTO mytable VALUES(@found_rows);

By doing that, the user variable will be replicated as part of the context, and applied on the slave correctly. Note that since the SELECT statement is not replicated, it is not possible to execute the SELECT before a stored routine and expect the value to be available inside the routine. For example, the following does not work::

  delimiter $$
  CREATE PROCEDURE foo()
  BEGIN
    DECLARE found_rows INT;
    SELECT FOUND_ROWS() INTO found_rows;
    INSERT INTO mytable VALUES (found_rows);
  END $$
  delimiter ;

  SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
  CALL foo();

Instead, pass the ``found_rows`` as a parameter to the procedure, and all will be well::

  delimiter $$
  CREATE PROCEDURE foo(found_rows INT)
  BEGIN
    INSERT INTO mytable VALUES (found_rows);
  END $$
  delimiter ;

  SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
  SELECT FOUND_ROWS() INTO @found_rows;
  CALL foo(@found_rows);

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36597

ChangeSet@1.2580, 2007-10-29 20:32:44+01:00, mats@kindahl-laptop.dnsalias.net +2 -0
  BUG#12092 (FOUND_ROWS() not replicated):
  
  Adding tests to demonstrate that the workarounds documented for
  statement-based replication works as advertised, and that the
  cases that does not work under statement-based replication
  actually work under mixed mode by switching to row-based
  replication.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36598

ChangeSet@1.2580, 2007-10-29 20:33:43+01:00, mats@kindahl-laptop.dnsalias.net +3 -0
  BUG#12092 (FOUND_ROWS() not replicated):
  
  Adding tests to demonstrate that the workarounds documented for
  statement-based replication works as advertised, and that the
  cases that does not work under statement-based replication
  actually work under mixed mode by switching to row-based
  replication.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37200

ChangeSet@1.2580, 2007-11-06 17:51:32+01:00, mats@kindahl-laptop.dnsalias.net +3 -0
  BUG#12092 (FOUND_ROWS() not replicated):
  
  In BUG#30244 added FOUND_ROWS() as an unsafe function, but that
  works only in mixed mode under 5.1. There is a workaround that
  can be used in statement-based mode either under 5.0 or 5.1
  where the result of FOUND_ROWS() is stored into a user vari-
  able 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. For some other cases, the
  value has to be passed explicitly.
  
  This patch adds tests to demonstrate that the workarounds docu-
  mented for statement-based replication works as advertised, and
  does more extensive tests for cases that does not work under sta-
  tement-based replication actually work under mixed mode by switch-
  ing to row-based replication.

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

When using SBR (and not mixed) in 5.1+, does this function throw a warning like the others listed in http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html?

Thanks.

Yes. In general, all statements marked "unsafe" are written to the binary log in row-based format in ``MIXED`` mode and writes a warning in ``STATEMENT`` mode.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Thanks for the clarification.

Documented bugfix in 5.1.23 and 6.0.4 changelogs.

Documented workaround in Replication chapter of Manual for applicable versions.