|
1
by brian
clean slate |
1 |
stop slave;
|
2 |
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
|
|
3 |
reset master;
|
|
4 |
reset slave;
|
|
5 |
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
|
|
6 |
start slave;
|
|
7 |
DROP PROCEDURE IF EXISTS test.p1;
|
|
8 |
DROP PROCEDURE IF EXISTS test.p2;
|
|
9 |
DROP TABLE IF EXISTS test.t2;
|
|
10 |
DROP TABLE IF EXISTS test.t1;
|
|
11 |
DROP TABLE IF EXISTS test.t3;
|
|
12 |
CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id));
|
|
13 |
CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2));
|
|
14 |
CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16));
|
|
15 |
CREATE PROCEDURE test.p1()
|
|
16 |
BEGIN
|
|
17 |
DECLARE done INT DEFAULT 0;
|
|
18 |
DECLARE spa CHAR(16);
|
|
19 |
DECLARE spb,spc INT;
|
|
20 |
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id;
|
|
21 |
DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2;
|
|
22 |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
|
|
23 |
OPEN cur1;
|
|
24 |
OPEN cur2;
|
|
25 |
REPEAT
|
|
26 |
FETCH cur1 INTO spb, spa;
|
|
27 |
FETCH cur2 INTO spc;
|
|
28 |
IF NOT done THEN
|
|
29 |
IF spb < spc THEN
|
|
30 |
INSERT INTO test.t3 VALUES (spb,spa);
|
|
31 |
ELSE
|
|
32 |
INSERT INTO test.t3 VALUES (spc,spa);
|
|
33 |
END IF;
|
|
34 |
END IF;
|
|
35 |
UNTIL done END REPEAT;
|
|
36 |
CLOSE cur1;
|
|
37 |
CLOSE cur2;
|
|
38 |
END|
|
|
39 |
CREATE PROCEDURE test.p2()
|
|
40 |
BEGIN
|
|
41 |
INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle');
|
|
42 |
INSERT INTO test.t2 VALUES (4),(2),(1),(3);
|
|
43 |
UPDATE test.t1 SET id=id+4 WHERE id=4;
|
|
44 |
END|
|
|
45 |
||
46 |
< ---- Master selects-- > |
|
47 |
------------------------- |
|
48 |
CALL test.p2(); |
|
49 |
SELECT * FROM test.t1 ORDER BY id; |
|
50 |
id data |
|
51 |
8 MySQL |
|
52 |
10 kyle |
|
53 |
11 Texas |
|
54 |
20 ROCKS |
|
55 |
SELECT * FROM test.t2 ORDER BY id2; |
|
56 |
id2 |
|
57 |
1 |
|
58 |
2 |
|
59 |
3 |
|
60 |
4 |
|
61 |
||
62 |
< ---- Slave selects-- > |
|
63 |
------------------------ |
|
64 |
SELECT * FROM test.t1 ORDER BY id; |
|
65 |
id data |
|
66 |
8 MySQL |
|
67 |
10 kyle |
|
68 |
11 Texas |
|
69 |
20 ROCKS |
|
70 |
SELECT * FROM test.t2 ORDER BY id2; |
|
71 |
id2 |
|
72 |
1 |
|
73 |
2 |
|
74 |
3 |
|
75 |
4 |
|
76 |
||
77 |
< ---- Master selects-- > |
|
78 |
------------------------- |
|
79 |
CALL test.p1(); |
|
80 |
SELECT * FROM test.t3 ORDER BY id3; |
|
81 |
id3 c |
|
82 |
1 MySQL |
|
83 |
2 kyle |
|
84 |
3 Texas |
|
85 |
4 ROCKS |
|
86 |
||
87 |
< ---- Slave selects-- > |
|
88 |
------------------------ |
|
89 |
SELECT * FROM test.t3 ORDER BY id3; |
|
90 |
id3 c |
|
91 |
1 MySQL |
|
92 |
2 kyle |
|
93 |
3 Texas |
|
94 |
4 ROCKS |
|
95 |
ALTER PROCEDURE test.p1 MODIFIES SQL DATA; |
|
96 |
DROP PROCEDURE IF EXISTS test.p1; |
|
97 |
DROP PROCEDURE IF EXISTS test.p2; |
|
98 |
DROP TABLE IF EXISTS test.t1; |
|
99 |
DROP TABLE IF EXISTS test.t2; |
|
100 |
DROP TABLE IF EXISTS test.t3; |