Debian Codegraph @

mysql-test/suite/rpl/t/rpl_mix_found_rows.test

raw
1 source include/have_binlog_format_mixed.inc;
2 source 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
14 connection master;
15 CREATE TABLE t1 (a INT);
16 CREATE TABLE logtbl (sect INT, test INT, count INT);
17
18 INSERT INTO t1 VALUES (1),(2),(3);
19 INSERT INTO t1 SELECT 2*a+3 FROM t1;
20 INSERT INTO t1 SELECT 2*a+3 FROM t1;
21 INSERT INTO t1 SELECT 2*a+3 FROM t1;
22 INSERT INTO t1 SELECT 2*a+3 FROM t1;
23 INSERT INTO t1 SELECT 2*a+3 FROM t1;
24 INSERT INTO t1 SELECT 2*a+3 FROM t1;
25
26 --echo ==== Checking a procedure ====
27
28 connection 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 $$
35 CREATE PROCEDURE just_log(sect INT, test INT) BEGIN
36 INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS());
37 END $$
38 --delimiter ;
39 sync_slave_with_master;
40
41 connection master1;
42 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
43 CALL just_log(1,1);
44
45 connection master;
46 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
47 CALL just_log(1,2);
48
49 connection master1;
50 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
51 CALL just_log(1,3);
52 sync_slave_with_master;
53
54 connection master;
55 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
56 CALL just_log(1,4);
57 sync_slave_with_master;
58
59 connection master;
60 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
61 sync_slave_with_master;
62 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
63
64 --echo ==== Checking a stored function ====
65 connection master;
66 --delimiter $$
67 CREATE FUNCTION log_rows(sect INT, test INT)
68 RETURNS INT
69 BEGIN
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;
74 END $$
75 --delimiter ;
76
77 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
78 --disable_ps2_protocol
79 SELECT log_rows(2,1), log_rows(2,2);
80 --enable_ps2_protocol
81
82 CREATE TABLE t2 (a INT, b INT);
83
84 # Trying with referencing FOUND_ROWS() directly in the trigger.
85
86 --delimiter $$
87 CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
88 BEGIN
89 INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS());
90 END $$
91 --delimiter ;
92
93 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
94 INSERT INTO t2 VALUES (2,3), (2,4);
95
96 # Referencing FOUND_ROWS() indirectly.
97
98 DROP TRIGGER t2_tr;
99
100 --delimiter $$
101 CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
102 BEGIN
103 DECLARE dummy INT;
104 SELECT log_rows(NEW.a, NEW.b) INTO dummy;
105 END $$
106 --delimiter ;
107
108 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
109 INSERT INTO t2 VALUES (2,5), (2,6);
110
111 # Putting FOUND_ROWS() even lower in the call chain.
112
113 connection master;
114 DROP TRIGGER t2_tr;
115
116 --delimiter $$
117 CREATE PROCEDURE log_me_inner(sect INT, test INT)
118 BEGIN
119 DECLARE dummy INT;
120 SELECT log_rows(sect, test) INTO dummy;
121 SELECT log_rows(sect, test+1) INTO dummy;
122 END $$
123
124 CREATE PROCEDURE log_me(sect INT, test INT)
125 BEGIN
126 CALL log_me_inner(sect,test);
127 END $$
128 --delimiter ;
129
130 --delimiter $$
131 CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
132 BEGIN
133 CALL log_me(NEW.a, NEW.b);
134 END $$
135 --delimiter ;
136
137 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
138 INSERT INTO t2 VALUES (2,5), (2,6);
139
140 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
141 sync_slave_with_master;
142 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
143
144 connection master;
145 DROP TABLE t1, t2, logtbl;
146 DROP PROCEDURE just_log;
147 DROP PROCEDURE log_me;
148 DROP PROCEDURE log_me_inner;
149 DROP FUNCTION log_rows;
150 sync_slave_with_master;
151
152 --source include/rpl_end.inc