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