1source include/have_binlog_format_statement.inc; 2source include/master-slave.inc; 3 4disable_query_log; 5call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 6enable_query_log; 7 8# It is not possible to replicate FOUND_ROWS() using statement-based 9# replication, but there is a workaround that stores the result of 10# FOUND_ROWS() into a user variable and then replicates this instead. 11# 12# The purpose of this test case is to test that the workaround works 13# properly even when inside stored programs (i.e., stored routines and 14# triggers). 15 16--echo ==== Initialize ==== 17 18connection master; 19CREATE TABLE t1 (a INT); 20CREATE TABLE logtbl (sect INT, test INT, count INT); 21 22INSERT INTO t1 VALUES (1),(2),(3); 23INSERT INTO t1 SELECT 2*a+3 FROM t1; 24INSERT INTO t1 SELECT 2*a+3 FROM t1; 25INSERT INTO t1 SELECT 2*a+3 FROM t1; 26INSERT INTO t1 SELECT 2*a+3 FROM t1; 27INSERT INTO t1 SELECT 2*a+3 FROM t1; 28INSERT INTO t1 SELECT 2*a+3 FROM t1; 29 30 31--echo ==== Simple test ==== 32 33SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 34 35# Instead of 36# INSERT INTO logtbl VALUES(1, 1, FOUND_ROWS()); 37# we write 38SELECT FOUND_ROWS() INTO @a; 39INSERT INTO logtbl VALUES(1,1,@a); 40 41SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 42# Instead of 43# INSERT INTO logtbl VALUES(1, 2, FOUND_ROWS()); 44# we write 45SELECT FOUND_ROWS() INTO @a; 46INSERT INTO logtbl VALUES(1,2,@a); 47 48SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 49sync_slave_with_master; 50SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test; 51 52 53--echo ==== Stored procedure ==== 54 55# Here we do both the calculation and the logging. We also do it twice 56# to make sure that there are no limitations on how many times it can 57# be used. 58 59connection master; 60--delimiter $$ 61CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN 62 DECLARE cnt INT; 63 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1; 64 SELECT FOUND_ROWS() INTO cnt; 65 INSERT INTO logtbl VALUES(sect,test,cnt); 66 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 67 SELECT FOUND_ROWS() INTO cnt; 68 INSERT INTO logtbl VALUES(sect,test+1,cnt); 69END $$ 70--delimiter ; 71 72CALL calc_and_log(2,1); 73 74--delimiter $$ 75CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN 76 INSERT INTO logtbl VALUES (sect,test,found_rows); 77END $$ 78--delimiter ; 79 80SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 81SELECT FOUND_ROWS() INTO @found_rows; 82CALL just_log(2,3,@found_rows); 83 84SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 85sync_slave_with_master; 86SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test; 87 88 89--echo ==== Stored functions ==== 90connection master; 91--delimiter $$ 92CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT) 93 RETURNS INT 94BEGIN 95 INSERT INTO logtbl VALUES(sect,test,found_rows); 96 RETURN found_rows; 97END $$ 98--delimiter ; 99 100SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1; 101SELECT FOUND_ROWS() INTO @found_rows; 102SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows); 103 104SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; 105sync_slave_with_master; 106SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test; 107 108 109--echo ==== Cleanup ==== 110connection master; 111DROP TABLE t1, logtbl; 112DROP PROCEDURE just_log; 113DROP PROCEDURE calc_and_log; 114DROP FUNCTION log_rows; 115 116--source include/rpl_end.inc 117