Debian Codegraph @

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

raw
1 source include/have_binlog_format_statement.inc;
2 --source include/set_privilege_checks_user_as_system_user.inc
3 source include/master-slave.inc;
4
5 disable_query_log;
6 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
7 enable_query_log;
8
9 # It is not possible to replicate FOUND_ROWS() using statement-based
10 # replication, but there is a workaround that stores the result of
11 # FOUND_ROWS() into a user variable and then replicates this instead.
12 #
13 # The purpose of this test case is to test that the workaround works
14 # properly even when inside stored programs (i.e., stored routines and
15 # triggers).
16
17 --echo ==== Initialize ====
18
19 --echo **** On Master ****
20 connection master;
21 CREATE TABLE t1 (a INT) ENGINE = InnoDB;
22 CREATE TABLE logtbl (sect INT, test INT, count INT) ENGINE = InnoDB;
23
24 INSERT INTO t1 VALUES (1),(2),(3);
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 INSERT INTO t1 SELECT 2*a+3 FROM t1;
30 INSERT INTO t1 SELECT 2*a+3 FROM t1;
31
32
33 --echo ==== Simple test ====
34
35 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
36
37 # Instead of
38 # INSERT INTO logtbl VALUES(1, 1, FOUND_ROWS());
39 # we write
40 SELECT FOUND_ROWS() INTO @a;
41 INSERT INTO logtbl VALUES(1,1,@a);
42
43 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
44 # Instead of
45 # INSERT INTO logtbl VALUES(1, 2, FOUND_ROWS());
46 # we write
47 SELECT FOUND_ROWS() INTO @a;
48 INSERT INTO logtbl VALUES(1,2,@a);
49
50 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
51 --echo **** On Slave ****
52 --source include/sync_slave_sql_with_master.inc
53 SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
54
55
56 --echo ==== Stored procedure ====
57
58 # Here we do both the calculation and the logging. We also do it twice
59 # to make sure that there are no limitations on how many times it can
60 # be used.
61
62 --echo **** On Master ****
63 connection master;
64 --delimiter $$
65 CREATE PROCEDURE calc_and_log(sect INT, test INT) BEGIN
66 DECLARE cnt INT;
67 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
68 SELECT FOUND_ROWS() INTO cnt;
69 INSERT INTO logtbl VALUES(sect,test,cnt);
70 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
71 SELECT FOUND_ROWS() INTO cnt;
72 INSERT INTO logtbl VALUES(sect,test+1,cnt);
73 END $$
74 --delimiter ;
75
76 CALL calc_and_log(2,1);
77
78 --delimiter $$
79 CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN
80 INSERT INTO logtbl VALUES (sect,test,found_rows);
81 END $$
82 --delimiter ;
83
84 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
85 SELECT FOUND_ROWS() INTO @found_rows;
86 CALL just_log(2,3,@found_rows);
87
88 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
89 --echo **** On Slave ****
90 --source include/sync_slave_sql_with_master.inc
91 SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
92
93
94 --echo ==== Stored functions ====
95 --echo **** On Master ****
96 connection master;
97 --delimiter $$
98 CREATE FUNCTION log_rows(sect INT, test INT, found_rows INT)
99 RETURNS INT
100 BEGIN
101 INSERT INTO logtbl VALUES(sect,test,found_rows);
102 RETURN found_rows;
103 END $$
104 --delimiter ;
105
106 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
107 SELECT FOUND_ROWS() INTO @found_rows;
108 SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows);
109
110 SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test;
111 --echo **** On Slave ****
112 --source include/sync_slave_sql_with_master.inc
113 SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test;
114
115
116 --echo ==== Cleanup ====
117 --echo **** On Master ****
118 connection master;
119 DROP TABLE t1, logtbl;
120 DROP PROCEDURE just_log;
121 DROP PROCEDURE calc_and_log;
122 DROP FUNCTION log_rows;
123
124 --source include/rpl_end.inc