Debian Codegraph @

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

raw
1 --source include/have_binlog_format_mixed.inc
2 # Thread stack overrun in debug mode on sparc
3 --source include/not_sparc_debug.inc
4 --source include/set_privilege_checks_user_as_system_user.inc
5 --source include/master-slave.inc
6
7 # It is not possible to replicate FOUND_ROWS() using statement-based
8 # replication, but there is a workaround that stores the result of
9 # FOUND_ROWS() into a user variable and then replicates this instead.
10 #
11 # The purpose of this test case is to test that the workaround works
12 # properly even when inside stored programs (i.e., stored routines and
13 # triggers).
14
15 --echo ==== Initialize ====
16
17 --echo **** On Master ****
18 connection master;
19 CREATE TABLE t1 (a INT);
20 CREATE TABLE logtbl (sect INT, test INT, count INT);
21
22 INSERT INTO t1 VALUES (1),(2),(3);
23 INSERT INTO t1 SELECT 2*a+3 FROM t1;
24 INSERT INTO t1 SELECT 2*a+3 FROM t1;
25 INSERT INTO t1 SELECT 2*a+3 FROM t1;
26 INSERT INTO t1 SELECT 2*a+3 FROM t1;
27 INSERT INTO t1 SELECT 2*a+3 FROM t1;
28 INSERT INTO t1 SELECT 2*a+3 FROM t1;
29
30 --echo ==== Checking a procedure ====
31
32 --echo **** On Master ****
33 connection master;
34
35 # We will now check some stuff that will not work in statement-based
36 # replication, but which should cause the binary log to switch to
37 # row-based logging.
38
39 --delimiter $$
40 CREATE PROCEDURE just_log(sect INT, test INT) BEGIN
41 INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS());
42 END $$
43 --delimiter ;
44 --source include/sync_slave_sql_with_master.inc
45
46 --echo **** On Master 1 ****
47 connection master1;
48 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
49 CALL just_log(1,1);
50
51 --echo **** On Master ****
52 connection master;
53 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
54 CALL just_log(1,2);
55
56 --echo **** On Master 1 ****
57
58 connection master1;
59 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
60 CALL just_log(1,3);
61 --source include/sync_slave_sql_with_master.inc
62
63 --echo **** On Master ****
64 connection master;
65 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
66 CALL just_log(1,4);
67 --source include/sync_slave_sql_with_master.inc
68
69 connection master;
70 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
71 --echo **** On Slave ****
72 --source include/sync_slave_sql_with_master.inc
73 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
74
75 --echo ==== Checking a stored function ====
76 --echo **** On Master ****
77 connection master;
78 --delimiter $$
79 CREATE FUNCTION log_rows(sect INT, test INT)
80 RETURNS INT
81 BEGIN
82 DECLARE found_rows INT;
83 SELECT FOUND_ROWS() INTO found_rows;
84 INSERT INTO logtbl VALUES(sect,test,found_rows);
85 RETURN found_rows;
86 END $$
87 --delimiter ;
88
89 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
90 SELECT log_rows(2,1), log_rows(2,2);
91
92 CREATE TABLE t2 (a INT, b INT);
93
94 # Trying with referencing FOUND_ROWS() directly in the trigger.
95
96 --delimiter $$
97 CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
98 BEGIN
99 INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS());
100 END $$
101 --delimiter ;
102
103 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
104 INSERT INTO t2 VALUES (2,3), (2,4);
105
106 # Referencing FOUND_ROWS() indirectly.
107
108 DROP TRIGGER t2_tr;
109
110 --delimiter $$
111 CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
112 BEGIN
113 DECLARE dummy INT;
114 SELECT log_rows(NEW.a, NEW.b) INTO dummy;
115 END $$
116 --delimiter ;
117
118 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
119 INSERT INTO t2 VALUES (2,5), (2,6);
120
121 # Putting FOUND_ROWS() even lower in the call chain.
122
123 connection master;
124 DROP TRIGGER t2_tr;
125
126 --delimiter $$
127 CREATE PROCEDURE log_me_inner(sect INT, test INT)
128 BEGIN
129 DECLARE dummy INT;
130 SELECT log_rows(sect, test) INTO dummy;
131 SELECT log_rows(sect, test+1) INTO dummy;
132 END $$
133
134 CREATE PROCEDURE log_me(sect INT, test INT)
135 BEGIN
136 CALL log_me_inner(sect,test);
137 END $$
138 --delimiter ;
139
140 --delimiter $$
141 CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
142 BEGIN
143 CALL log_me(NEW.a, NEW.b);
144 END $$
145 --delimiter ;
146
147 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
148 INSERT INTO t2 VALUES (2,5), (2,6);
149
150 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
151 --source include/sync_slave_sql_with_master.inc
152 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
153
154 connection master;
155 DROP TABLE t1, t2, logtbl;
156 DROP PROCEDURE just_log;
157 DROP PROCEDURE log_me;
158 DROP PROCEDURE log_me_inner;
159 DROP FUNCTION log_rows;
160 --source include/sync_slave_sql_with_master.inc
161
162 --source include/rpl_end.inc