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 ORDER BY sect,test; sect test count 1 1 183 1 2 3 **** On Slave **** SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 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 ORDER BY sect,test; sect test count 2 1 3 2 2 183 2 3 183 **** On Slave **** SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 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 ORDER BY sect,test; sect test count 3 1 183 3 2 183 **** On Slave **** SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; 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 **** SET BINLOG_FORMAT=MIXED; 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 ORDER BY sect,test; sect test count 1 1 183 1 2 183 1 3 3 1 4 183 **** On Slave **** SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 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 ORDER BY sect,test; sect test count 2 1 3 2 2 3 2 3 3 2 4 3 2 5 183 2 5 183 2 6 183 2 6 0 2 6 183 2 7 0 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; sect test count 2 1 3 2 2 3 2 3 3 2 4 3 2 5 183 2 5 183 2 6 183 2 6 0 2 6 183 2 7 0 DROP TABLE t1, logtbl; DROP PROCEDURE just_log; DROP PROCEDURE log_me; DROP PROCEDURE log_me_inner; DROP FUNCTION log_rows;