Mats, hej.
The patch is fine.
After stating explicitly what is the problem of this bug it can be
pushed.
> Below is the list of changes that have just been committed into a local
> 5.1 repository of mats. When mats does a push these changes will
> be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
>
> ChangeSet@stripped, 2007-10-29 20:33:43+01:00, mats@stripped +3 -0
> BUG#12092 (FOUND_ROWS() not replicated):
>
There was a patch
Bug #30244 row_count/found_rows does not replicate well
that added the function to the list of automatically switching to
row-based.
This current bug would do that fix, but it's too late :) Could you
please refer to the parent in fixing order sense bug in the cset
comments and outline that the matter of this work is to test your w/a
for stmt format?
> 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.
>
I find the tests covering use cases for stmt format replication with
the function.
> mysql-test/include/reset_master_and_slave.inc@stripped, 2007-10-29 20:33:39+01:00,
> mats@stripped +10 -0
> New BitKeeper file ``mysql-test/include/reset_master_and_slave.inc''
>
> mysql-test/include/reset_master_and_slave.inc@stripped, 2007-10-29 20:33:39+01:00,
> mats@stripped +0 -0
>
> mysql-test/suite/rpl/r/rpl_found_rows.result@stripped, 2007-10-29 20:33:39+01:00,
> mats@stripped +228 -0
> New BitKeeper file ``mysql-test/suite/rpl/r/rpl_found_rows.result''
>
> mysql-test/suite/rpl/r/rpl_found_rows.result@stripped, 2007-10-29 20:33:39+01:00,
> mats@stripped +0 -0
>
> mysql-test/suite/rpl/t/rpl_found_rows.test@stripped, 2007-10-29 20:33:39+01:00,
> mats@stripped +251 -0
> New BitKeeper file ``mysql-test/suite/rpl/t/rpl_found_rows.test''
>
> mysql-test/suite/rpl/t/rpl_found_rows.test@stripped, 2007-10-29 20:33:39+01:00,
> mats@stripped +0 -0
>
> diff -Nrup a/mysql-test/include/reset_master_and_slave.inc
> b/mysql-test/include/reset_master_and_slave.inc
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/include/reset_master_and_slave.inc 2007-10-29 20:33:39 +01:00
> @@ -0,0 +1,10 @@
> +--echo **** Resetting master and slave ****
> +connection slave;
> +STOP SLAVE;
> +source include/wait_for_slave_to_stop.inc;
> +RESET SLAVE;
> +connection master;
> +RESET MASTER;
> +connection slave;
> +START SLAVE;
> +source include/wait_for_slave_to_start.inc;
> diff -Nrup a/mysql-test/suite/rpl/r/rpl_found_rows.result
> b/mysql-test/suite/rpl/r/rpl_found_rows.result
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/rpl/r/rpl_found_rows.result 2007-10-29 20:33:39 +01:00
> @@ -0,0 +1,228 @@
> +stop slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +reset master;
> +reset slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +start slave;
> +==== 0. Setting it all up ====
> +SET BINLOG_FORMAT=STATEMENT;
> +**** On Master ****
> +CREATE TABLE t1 (a INT);
> +CREATE TABLE logtbl (sect INT, test INT, count INT);
> +INSERT INTO t1 VALUES (1),(2),(3);
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +#### 1. Using statement mode ####
> +==== 1.1. Simple test ====
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO logtbl VALUES(1,1,@a);
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +a
> +1
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO logtbl VALUES(1,2,@a);
> +SELECT * FROM logtbl WHERE sect = 1;
> +sect test count
> +1 1 183
> +1 2 3
> +**** On Slave ****
> +SELECT * FROM logtbl WHERE sect = 1;
> +sect test count
> +1 1 183
> +1 2 3
> +==== 1.2. Stored procedure ====
> +**** On Master ****
> +CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN
> +DECLARE cnt INT;
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +SELECT FOUND_ROWS() INTO cnt;
> +INSERT INTO logtbl VALUES(sect,test,cnt);
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +SELECT FOUND_ROWS() INTO cnt;
> +INSERT INTO logtbl VALUES(sect,test+1,cnt);
> +END $$
> +CALL calc_and_log(2,1);
> +a
> +1
> +a
> +7
> +CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN
> +INSERT INTO logtbl VALUES (sect,test,found_rows);
> +END $$
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +SELECT FOUND_ROWS() INTO @found_rows;
> +CALL just_log(2,3,@found_rows);
> +SELECT * FROM logtbl WHERE sect = 2;
> +sect test count
> +2 1 3
> +2 2 183
> +2 3 183
> +**** On Slave ****
> +SELECT * FROM logtbl WHERE sect = 2;
> +sect test count
> +2 1 3
> +2 2 183
> +2 3 183
> +==== 1.3. Stored functions ====
> +**** On Master ****
> +CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT)
> +RETURNS INT
> +BEGIN
> +INSERT INTO logtbl VALUES(sect,test,found_rows);
> +RETURN found_rows;
> +END $$
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +SELECT FOUND_ROWS() INTO @found_rows;
> +SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows);
> +log_rows(3,1,@found_rows) log_rows(3,2,@found_rows)
> +183 183
> +SELECT * FROM logtbl WHERE sect = 3;
> +sect test count
> +3 1 183
> +3 2 183
> +**** On Slave ****
> +SELECT * FROM logtbl WHERE sect = 3;
> +sect test count
> +3 1 183
> +3 2 183
> +==== 1.9. Cleanup ====
> +**** On Master ****
> +DELETE FROM logtbl;
> +DROP PROCEDURE just_log;
> +DROP PROCEDURE calc_and_log;
> +DROP FUNCTION log_rows;
> +**** Resetting master and slave ****
> +STOP SLAVE;
> +RESET SLAVE;
> +RESET MASTER;
> +START SLAVE;
> +#### 2. Using mixed mode ####
> +==== 2.1. Checking a procedure ====
> +**** On Master ****
> +SET BINLOG_FORMAT=MIXED;
> +CREATE PROCEDURE just_log(sect INT, test INT) BEGIN
> +INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS());
> +END $$
> +**** On Master 1 ****
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +CALL just_log(1,1);
> +**** On Master ****
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +CALL just_log(1,2);
> +**** On Master 1 ****
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +a
> +1
> +CALL just_log(1,3);
> +**** On Master ****
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +CALL just_log(1,4);
> +SELECT * FROM logtbl WHERE sect = 1;
> +sect test count
> +1 1 183
> +1 2 183
> +1 3 3
> +1 4 183
> +**** On Slave ****
> +SELECT * FROM logtbl WHERE sect = 1;
> +sect test count
> +1 1 183
> +1 2 183
> +1 3 3
> +1 4 183
> +==== 2.1. Checking a stored function ====
> +**** On Master ****
> +CREATE FUNCTION log_rows(sect INT, test INT)
> +RETURNS INT
> +BEGIN
> +DECLARE found_rows INT;
> +SELECT FOUND_ROWS() INTO found_rows;
> +INSERT INTO logtbl VALUES(sect,test,found_rows);
> +RETURN found_rows;
> +END $$
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +a
> +1
> +SELECT log_rows(2,1), log_rows(2,2);
> +log_rows(2,1) log_rows(2,2)
> +3 3
> +CREATE TABLE t2 (a INT, b INT);
> +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
> +BEGIN
> +INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS());
> +END $$
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +a
> +1
> +INSERT INTO t2 VALUES (2,3), (2,4);
> +DROP TRIGGER t2_tr;
> +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
> +BEGIN
> +DECLARE dummy INT;
> +SELECT log_rows(NEW.a, NEW.b) INTO dummy;
> +END $$
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +INSERT INTO t2 VALUES (2,5), (2,6);
> +DROP TRIGGER t2_tr;
> +CREATE PROCEDURE log_me_inner(sect INT, test INT)
> +BEGIN
> +DECLARE dummy INT;
> +SELECT log_rows(sect, test) INTO dummy;
> +SELECT log_rows(sect, test+1) INTO dummy;
> +END $$
> +CREATE PROCEDURE log_me(sect INT, test INT)
> +BEGIN
> +CALL log_me_inner(sect,test);
> +END $$
> +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
> +BEGIN
> +CALL log_me(NEW.a, NEW.b);
> +END $$
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +a
> +7
> +INSERT INTO t2 VALUES (2,5), (2,6);
> +SELECT * FROM logtbl WHERE sect = 2;
> +sect test count
> +2 1 3
> +2 2 3
> +2 3 3
> +2 4 3
> +2 5 183
> +2 6 183
> +2 5 183
> +2 6 0
> +2 6 183
> +2 7 0
> +SELECT * FROM logtbl WHERE sect = 2;
> +sect test count
> +2 1 3
> +2 2 3
> +2 3 3
> +2 4 3
> +2 5 183
> +2 6 183
> +2 5 183
> +2 6 0
> +2 6 183
> +2 7 0
> +DROP TABLE t1, logtbl;
> diff -Nrup a/mysql-test/suite/rpl/t/rpl_found_rows.test
> b/mysql-test/suite/rpl/t/rpl_found_rows.test
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/rpl/t/rpl_found_rows.test 2007-10-29 20:33:39 +01:00
> @@ -0,0 +1,251 @@
> +source include/master-slave.inc;
> +
> +# It is not possible to replicate FOUND_ROWS() using statement-based
> +# replication, but there is a workaround that stores the result of
> +# FOUND_ROWS() into a user variable and then replicates this instead.
> +
> +# The purpose of this test case is to test that the workaround
> +# function properly even when inside stored programs (i.e., stored
> +# routines and triggers).
> +
> +--echo ==== 0. Setting it all up ====
> +
> +SET BINLOG_FORMAT=STATEMENT;
> +
> +--echo **** On Master ****
> +connection master;
> +CREATE TABLE t1 (a INT);
> +CREATE TABLE logtbl (sect INT, test INT, count INT);
> +
> +INSERT INTO t1 VALUES (1),(2),(3);
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +INSERT INTO t1 SELECT 2*a+3 FROM t1;
> +
> +--echo #### 1. Using statement mode ####
> +
> +--echo ==== 1.1. Simple test ====
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +
> +# Instead of
> +# INSERT INTO logtbl VALUES(1, 1, FOUND_ROWS());
> +# we write
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO logtbl VALUES(1,1,@a);
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +# Instead of
> +# INSERT INTO logtbl VALUES(1, 2, FOUND_ROWS());
> +# we write
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO logtbl VALUES(1,2,@a);
> +
> +SELECT * FROM logtbl WHERE sect = 1;
> +--echo **** On Slave ****
> +sync_slave_with_master;
> +SELECT * FROM logtbl WHERE sect = 1;
> +
> +--echo ==== 1.2. Stored procedure ====
> +
> +# Here we do both the calculation and the logging. We also do it twice
> +# to make sure that there are no limitations on how many times it can
> +# be used.
> +
> +--echo **** On Master ****
> +connection master;
> +--delimiter $$
> +CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN
> + DECLARE cnt INT;
> + SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> + SELECT FOUND_ROWS() INTO cnt;
> + INSERT INTO logtbl VALUES(sect,test,cnt);
> + SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> + SELECT FOUND_ROWS() INTO cnt;
> + INSERT INTO logtbl VALUES(sect,test+1,cnt);
> +END $$
> +--delimiter ;
> +
> +CALL calc_and_log(2,1);
> +
> +--delimiter $$
> +CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN
> + INSERT INTO logtbl VALUES (sect,test,found_rows);
> +END $$
> +--delimiter ;
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +SELECT FOUND_ROWS() INTO @found_rows;
> +CALL just_log(2,3,@found_rows);
> +
> +SELECT * FROM logtbl WHERE sect = 2;
> +--echo **** On Slave ****
> +sync_slave_with_master;
> +SELECT * FROM logtbl WHERE sect = 2;
> +
> +--echo ==== 1.3. Stored functions ====
> +--echo **** On Master ****
> +connection master;
> +--delimiter $$
> +CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT)
> + RETURNS INT
> +BEGIN
> + INSERT INTO logtbl VALUES(sect,test,found_rows);
> + RETURN found_rows;
> +END $$
> +--delimiter ;
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +SELECT FOUND_ROWS() INTO @found_rows;
> +SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows);
> +
> +SELECT * FROM logtbl WHERE sect = 3;
> +--echo **** On Slave ****
> +sync_slave_with_master;
> +SELECT * FROM logtbl WHERE sect = 3;
> +
> +--echo ==== 1.9. Cleanup ====
> +--echo **** On Master ****
> +connection master;
> +DELETE FROM logtbl;
> +DROP PROCEDURE just_log;
> +DROP PROCEDURE calc_and_log;
> +DROP FUNCTION log_rows;
> +sync_slave_with_master;
> +
> +source include/reset_master_and_slave.inc;
> +
> +--echo #### 2. Using mixed mode ####
> +
> +--echo ==== 2.1. Checking a procedure ====
> +
> +--echo **** On Master ****
> +connection master;
> +SET BINLOG_FORMAT=MIXED;
> +
> +# We will now check some stuff that will not work in statement-based
> +# replication, but which should cause the binary log to switch to
> +# row-based logging.
> +
> +--delimiter $$
> +CREATE PROCEDURE just_log(sect INT, test INT) BEGIN
> + INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS());
> +END $$
> +--delimiter ;
> +sync_slave_with_master;
> +
> +--echo **** On Master 1 ****
> +connection master1;
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +CALL just_log(1,1);
> +
> +--echo **** On Master ****
> +connection master;
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +CALL just_log(1,2);
> +
> +--echo **** On Master 1 ****
> +
> +connection master1;
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +CALL just_log(1,3);
> +sync_slave_with_master;
> +
> +--echo **** On Master ****
> +connection master;
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +CALL just_log(1,4);
> +sync_slave_with_master;
> +
> +connection master;
> +SELECT * FROM logtbl WHERE sect = 1;
> +--echo **** On Slave ****
> +sync_slave_with_master;
> +SELECT * FROM logtbl WHERE sect = 1;
> +
> +--echo ==== 2.1. Checking a stored function ====
> +--echo **** On Master ****
> +connection master;
> +--delimiter $$
> +CREATE FUNCTION log_rows(sect INT, test INT)
> + RETURNS INT
> +BEGIN
> + DECLARE found_rows INT;
> + SELECT FOUND_ROWS() INTO found_rows;
> + INSERT INTO logtbl VALUES(sect,test,found_rows);
> + RETURN found_rows;
> +END $$
> +--delimiter ;
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +SELECT log_rows(2,1), log_rows(2,2);
> +
> +CREATE TABLE t2 (a INT, b INT);
> +
> +# Trying with referencing FOUND_ROWS() directly in the trigger.
> +
> +--delimiter $$
> +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
> +BEGIN
> + INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS());
> +END $$
> +--delimiter ;
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
> +INSERT INTO t2 VALUES (2,3), (2,4);
> +
> +# Referencing FOUND_ROWS() indirectly.
> +
> +DROP TRIGGER t2_tr;
> +
> +--delimiter $$
> +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
> +BEGIN
> + DECLARE dummy INT;
> + SELECT log_rows(NEW.a, NEW.b) INTO dummy;
> +END $$
> +--delimiter ;
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +INSERT INTO t2 VALUES (2,5), (2,6);
> +
> +# Putting FOUND_ROWS() even lower in the call chain.
> +
> +connection master;
> +DROP TRIGGER t2_tr;
> +
> +--delimiter $$
> +CREATE PROCEDURE log_me_inner(sect INT, test INT)
> +BEGIN
> + DECLARE dummy INT;
> + SELECT log_rows(sect, test) INTO dummy;
> + SELECT log_rows(sect, test+1) INTO dummy;
> +END $$
> +
> +CREATE PROCEDURE log_me(sect INT, test INT)
> +BEGIN
> + CALL log_me_inner(sect,test);
> +END $$
> +--delimiter ;
> +
> +--delimiter $$
> +CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
> +BEGIN
> + CALL log_me(NEW.a, NEW.b);
> +END $$
> +--delimiter ;
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
> +INSERT INTO t2 VALUES (2,5), (2,6);
> +
> +SELECT * FROM logtbl WHERE sect = 2;
> +sync_slave_with_master;
> +SELECT * FROM logtbl WHERE sect = 2;
> +
> +connection master;
> +DROP TABLE t1, logtbl;
> +sync_slave_with_master;
> +
>
> --
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe: http://lists.mysql.com/commits?unsub=1
cheers,
Andrei
