Debian Codegraph @

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

raw
1 source include/have_binlog_format_statement.inc;
2 source include/master-slave.inc;
3
4 disable_query_log;
5 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
6 enable_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
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
31 --echo ==== Simple test ====
32
33 SELECT 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
38 --disable_cursor_protocol
39 --disable_ps2_protocol
40 SELECT FOUND_ROWS() INTO @a;
41 --enable_ps2_protocol
42 --enable_cursor_protocol
43 INSERT INTO logtbl VALUES(1,1,@a);
44
45 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
46 # Instead of
47 # INSERT INTO logtbl VALUES(1, 2, FOUND_ROWS());
48 # we write
49 --disable_cursor_protocol
50 --disable_ps2_protocol
51 SELECT FOUND_ROWS() INTO @a;
52 --enable_ps2_protocol
53 --enable_cursor_protocol
54 INSERT INTO logtbl VALUES(1,2,@a);
55
56 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
57 sync_slave_with_master;
58 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
59
60
61 --echo ==== Stored procedure ====
62
63 # Here we do both the calculation and the logging. We also do it twice
64 # to make sure that there are no limitations on how many times it can
65 # be used.
66
67 connection master;
68 --delimiter $$
69 CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN
70 DECLARE cnt INT;
71 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
72 SELECT FOUND_ROWS() INTO cnt;
73 INSERT INTO logtbl VALUES(sect,test,cnt);
74 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
75 SELECT FOUND_ROWS() INTO cnt;
76 INSERT INTO logtbl VALUES(sect,test+1,cnt);
77 END $$
78 --delimiter ;
79
80 CALL calc_and_log(2,1);
81
82 --delimiter $$
83 CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN
84 INSERT INTO logtbl VALUES (sect,test,found_rows);
85 END $$
86 --delimiter ;
87
88 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
89 --disable_cursor_protocol
90 --disable_ps2_protocol
91 SELECT FOUND_ROWS() INTO @found_rows;
92 --enable_ps2_protocol
93 --enable_cursor_protocol
94 CALL just_log(2,3,@found_rows);
95
96 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
97 sync_slave_with_master;
98 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
99
100
101 --echo ==== Stored functions ====
102 connection master;
103 --delimiter $$
104 CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT)
105 RETURNS INT
106 BEGIN
107 INSERT INTO logtbl VALUES(sect,test,found_rows);
108 RETURN found_rows;
109 END $$
110 --delimiter ;
111
112 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
113 --disable_ps2_protocol
114 --disable_cursor_protocol
115 SELECT FOUND_ROWS() INTO @found_rows;
116 --enable_cursor_protocol
117 SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows);
118 --enable_ps2_protocol
119
120 SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test;
121 sync_slave_with_master;
122 SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test;
123
124
125 --echo ==== Cleanup ====
126 connection master;
127 DROP TABLE t1, logtbl;
128 DROP PROCEDURE just_log;
129 DROP PROCEDURE calc_and_log;
130 DROP FUNCTION log_rows;
131
132 --source include/rpl_end.inc