1include/master-slave.inc 2[connection master] 3==== Initialize ==== 4connection master; 5CREATE TABLE t1 (a INT); 6CREATE TABLE logtbl (sect INT, test INT, count INT); 7INSERT INTO t1 VALUES (1),(2),(3); 8INSERT INTO t1 SELECT 2*a+3 FROM t1; 9INSERT INTO t1 SELECT 2*a+3 FROM t1; 10INSERT INTO t1 SELECT 2*a+3 FROM t1; 11INSERT INTO t1 SELECT 2*a+3 FROM t1; 12INSERT INTO t1 SELECT 2*a+3 FROM t1; 13INSERT INTO t1 SELECT 2*a+3 FROM t1; 14==== Simple test ==== 15SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 16a 177 18SELECT FOUND_ROWS() INTO @a; 19INSERT INTO logtbl VALUES(1,1,@a); 20SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 21a 221 23SELECT FOUND_ROWS() INTO @a; 24INSERT INTO logtbl VALUES(1,2,@a); 25SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 26sect test count 271 1 183 281 2 3 29connection slave; 30SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 31sect test count 321 1 183 331 2 3 34==== Stored procedure ==== 35connection master; 36CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN 37DECLARE cnt INT; 38SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 39SELECT FOUND_ROWS() INTO cnt; 40INSERT INTO logtbl VALUES(sect,test,cnt); 41SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 42SELECT FOUND_ROWS() INTO cnt; 43INSERT INTO logtbl VALUES(sect,test+1,cnt); 44END $$ 45CALL calc_and_log(2,1); 46a 471 48a 497 50CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN 51INSERT INTO logtbl VALUES (sect,test,found_rows); 52END $$ 53SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 54a 557 56SELECT FOUND_ROWS() INTO @found_rows; 57CALL just_log(2,3,@found_rows); 58SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 59sect test count 602 1 3 612 2 183 622 3 183 63connection slave; 64SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 65sect test count 662 1 3 672 2 183 682 3 183 69==== Stored functions ==== 70connection master; 71CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT) 72RETURNS INT 73BEGIN 74INSERT INTO logtbl VALUES(sect,test,found_rows); 75RETURN found_rows; 76END $$ 77SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 78a 797 80SELECT FOUND_ROWS() INTO @found_rows; 81SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows); 82log_rows(3,1,@found_rows) log_rows(3,2,@found_rows) 83183 183 84SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; 85sect test count 863 1 183 873 2 183 88connection slave; 89SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; 90sect test count 913 1 183 923 2 183 93==== Cleanup ==== 94connection master; 95DROP TABLE t1, logtbl; 96DROP PROCEDURE just_log; 97DROP PROCEDURE calc_and_log; 98DROP FUNCTION log_rows; 99include/rpl_end.inc 100