rpl_found_rows.result 5.5 KB
Newer Older
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
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
==== 0. Setting it all up ====
SET BINLOG_FORMAT=STATEMENT;
**** On 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;
#### 1. Using statement mode ####
==== 1.1. Simple test ====
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
SELECT FOUND_ROWS() INTO @a;
INSERT INTO logtbl VALUES(1,1,@a);
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
a
1
SELECT FOUND_ROWS() INTO @a;
INSERT INTO logtbl VALUES(1,2,@a);
SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
sect	test	count
1	1	183
1	2	3
**** On Slave ****
SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
sect	test	count
1	1	183
1	2	3
==== 1.2. Stored procedure ====
**** On Master ****
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 $$
CALL calc_and_log(2,1);
a
1
a
7
CREATE PROCEDURE just_log(sect INT, test INT, found_rows INT) BEGIN
INSERT INTO logtbl VALUES (sect,test,found_rows);
END $$
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
SELECT FOUND_ROWS() INTO @found_rows;
CALL just_log(2,3,@found_rows);
SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
sect	test	count
2	1	3
2	2	183
2	3	183
**** On Slave ****
SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
sect	test	count
2	1	3
2	2	183
2	3	183
==== 1.3. Stored functions ====
**** On Master ****
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 $$
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
SELECT FOUND_ROWS() INTO @found_rows;
SELECT log_rows(3,1,@found_rows), log_rows(3,2,@found_rows);
log_rows(3,1,@found_rows)	log_rows(3,2,@found_rows)
183	183
SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test;
sect	test	count
3	1	183
3	2	183
**** On Slave ****
SELECT * FROM logtbl WHERE sect = 3 ORDER BY sect,test;
sect	test	count
3	1	183
3	2	183
==== 1.9. Cleanup ====
**** On Master ****
DELETE FROM logtbl;
DROP PROCEDURE just_log;
DROP PROCEDURE calc_and_log;
DROP FUNCTION log_rows;
**** Resetting master and slave ****
106
include/stop_slave.inc
107 108
RESET SLAVE;
RESET MASTER;
109
include/start_slave.inc
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233
#### 2. Using mixed mode ####
==== 2.1. Checking a procedure ====
**** On Master ****
SET BINLOG_FORMAT=MIXED;
CREATE PROCEDURE just_log(sect INT, test INT) BEGIN
INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS());
END $$
**** On Master 1 ****
SET BINLOG_FORMAT=MIXED;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
CALL just_log(1,1);
**** On Master ****
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
CALL just_log(1,2);
**** On Master 1 ****
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
a
1
CALL just_log(1,3);
**** On Master ****
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
CALL just_log(1,4);
SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
sect	test	count
1	1	183
1	2	183
1	3	3
1	4	183
**** On Slave ****
SELECT * FROM logtbl WHERE sect = 1 ORDER BY sect,test;
sect	test	count
1	1	183
1	2	183
1	3	3
1	4	183
==== 2.1. Checking a stored function ====
**** On Master ****
CREATE FUNCTION log_rows(sect INT, test INT)
RETURNS INT
BEGIN
DECLARE found_rows INT;
SELECT FOUND_ROWS() INTO found_rows;
INSERT INTO logtbl VALUES(sect,test,found_rows);
RETURN found_rows;
END $$
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
a
1
SELECT log_rows(2,1), log_rows(2,2);
log_rows(2,1)	log_rows(2,2)
3	3
CREATE TABLE t2 (a INT, b INT);
CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
BEGIN
INSERT INTO logtbl VALUES (NEW.a, NEW.b, FOUND_ROWS());
END $$
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
a
1
INSERT INTO t2 VALUES (2,3), (2,4);
DROP TRIGGER t2_tr;
CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
BEGIN
DECLARE dummy INT;
SELECT log_rows(NEW.a, NEW.b) INTO dummy;
END $$
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
INSERT INTO t2 VALUES (2,5), (2,6);
DROP TRIGGER t2_tr;
CREATE PROCEDURE log_me_inner(sect INT, test INT)
BEGIN
DECLARE dummy INT;
SELECT log_rows(sect, test) INTO dummy;
SELECT log_rows(sect, test+1) INTO dummy;
END $$
CREATE PROCEDURE log_me(sect INT, test INT)
BEGIN
CALL log_me_inner(sect,test);
END $$
CREATE TRIGGER t2_tr BEFORE INSERT ON t2 FOR EACH ROW
BEGIN
CALL log_me(NEW.a, NEW.b);
END $$
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
INSERT INTO t2 VALUES (2,5), (2,6);
SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
sect	test	count
2	1	3
2	2	3
2	3	3
2	4	3
2	5	183
2	5	183
2	6	183
2	6	0
2	6	183
2	7	0
SELECT * FROM logtbl WHERE sect = 2 ORDER BY sect,test;
sect	test	count
2	1	3
2	2	3
2	3	3
2	4	3
2	5	183
2	5	183
2	6	183
2	6	0
2	6	183
2	7	0
DROP TABLE t1, t2, logtbl;
DROP PROCEDURE just_log;
DROP PROCEDURE log_me;
DROP PROCEDURE log_me_inner;
DROP FUNCTION log_rows;