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