include/master-slave.inc
[connection master]
==== 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;
==== Checking a procedure ====
connection master;
CREATE PROCEDURE just_log(sect INT, test INT) BEGIN
INSERT INTO logtbl VALUES (sect,test,FOUND_ROWS());
END $$
connection slave;
connection master1;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
CALL just_log(1,1);
connection master;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
CALL just_log(1,2);
connection master1;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a < 5 ORDER BY a LIMIT 1;
a
1
CALL just_log(1,3);
connection slave;
connection master;
SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a > 5 ORDER BY a LIMIT 1;
a
7
CALL just_log(1,4);
connection slave;
connection master;
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
connection 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
==== Checking a stored function ====
connection 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);
connection master;
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
connection slave;
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
connection master;
DROP TABLE t1, t2, logtbl;
DROP PROCEDURE just_log;
DROP PROCEDURE log_me;
DROP PROCEDURE log_me_inner;
DROP FUNCTION log_rows;
connection slave;
include/rpl_end.inc