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