1source include/have_binlog_format_mixed.inc; 2source include/master-slave.inc; 3 4# It is not possible to replicate FOUND_ROWS() using statement-based 5# replication, but there is a workaround that stores the result of 6# FOUND_ROWS() into a user variable and then replicates this instead. 7# 8# The purpose of this test case is to test that the workaround works 9# properly even when inside stored programs (i.e., stored routines and 10# triggers). 11 12--echo ==== Initialize ==== 13 14connection master; 15CREATE TABLE t1 (a INT); 16CREATE TABLE logtbl (sect INT, test INT, count INT); 17 18INSERT INTO t1 VALUES (1),(2),(3); 19INSERT INTO t1 SELECT 2*a+3 FROM t1; 20INSERT INTO t1 SELECT 2*a+3 FROM t1; 21INSERT INTO t1 SELECT 2*a+3 FROM t1; 22INSERT INTO t1 SELECT 2*a+3 FROM t1; 23INSERT INTO t1 SELECT 2*a+3 FROM t1; 24INSERT INTO t1 SELECT 2*a+3 FROM t1; 25 26--echo ==== Checking a procedure ==== 27 28connection master; 29 30# We will now check some stuff that will not work in statement-based 31# replication, but which should cause the binary log to switch to 32# row-based logging. 33 34--delimiter $$ 35CREATE PROCEDURE just_log(sect INT, test INT) BEGIN 36 INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS()); 37END $$ 38--delimiter ; 39sync_slave_with_master; 40 41connection master1; 42SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 43CALL just_log(1,1); 44 45connection master; 46SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 47CALL just_log(1,2); 48 49connection master1; 50SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 51CALL just_log(1,3); 52sync_slave_with_master; 53 54connection master; 55SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 56CALL just_log(1,4); 57sync_slave_with_master; 58 59connection master; 60SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 61sync_slave_with_master; 62SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 63 64--echo ==== Checking a stored function ==== 65connection master; 66--delimiter $$ 67CREATE FUNCTION log_rows(sect INT, test INT) 68 RETURNS INT 69BEGIN 70 DECLARE found_rows INT; 71 SELECT FOUND_ROWS() INTO found_rows; 72 INSERT INTO logtbl VALUES(sect,test,found_rows); 73 RETURN found_rows; 74END $$ 75--delimiter ; 76 77SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 78SELECT log_rows(2,1), log_rows(2,2); 79 80CREATE TABLE t2 (a INT, b INT); 81 82# Trying with referencing FOUND_ROWS() directly in the trigger. 83 84--delimiter $$ 85CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW 86BEGIN 87 INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS()); 88END $$ 89--delimiter ; 90 91SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 92INSERT INTO t2 VALUES (2,3), (2,4); 93 94# Referencing FOUND_ROWS() indirectly. 95 96DROP TRIGGER t2_tr; 97 98--delimiter $$ 99CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW 100BEGIN 101 DECLARE dummy INT; 102 SELECT log_rows(NEW.a, NEW.b) INTO dummy; 103END $$ 104--delimiter ; 105 106SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 107INSERT INTO t2 VALUES (2,5), (2,6); 108 109# Putting FOUND_ROWS() even lower in the call chain. 110 111connection master; 112DROP TRIGGER t2_tr; 113 114--delimiter $$ 115CREATE PROCEDURE log_me_inner(sect INT, test INT) 116BEGIN 117 DECLARE dummy INT; 118 SELECT log_rows(sect, test) INTO dummy; 119 SELECT log_rows(sect, test+1) INTO dummy; 120END $$ 121 122CREATE PROCEDURE log_me(sect INT, test INT) 123BEGIN 124 CALL log_me_inner(sect,test); 125END $$ 126--delimiter ; 127 128--delimiter $$ 129CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW 130BEGIN 131 CALL log_me(NEW.a, NEW.b); 132END $$ 133--delimiter ; 134 135SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 136INSERT INTO t2 VALUES (2,5), (2,6); 137 138SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 139sync_slave_with_master; 140SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 141 142connection master; 143DROP TABLE t1, t2, logtbl; 144DROP PROCEDURE just_log; 145DROP PROCEDURE log_me; 146DROP PROCEDURE log_me_inner; 147DROP FUNCTION log_rows; 148sync_slave_with_master; 149 150--source include/rpl_end.inc 151