Debian Codegraph @

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

raw
1 #############################################################################
2 # Original Author: JBM #
3 # Original Date: Aug/15/2005 #
4 # Updated: Aug/29/2005: Removed sleeps #
5 #############################################################################
6 # Test: Tests SPs with cursors, flow logic, and alter sp. In addition the #
7 # tests SPs with insert and update operations. #
8 #############################################################################
9
10 # Includes
11 -- source include/have_binlog_format_row.inc
12 -- source include/master-slave.inc
13
14
15 # Begin clean up test section
16 connection master;
17 --disable_warnings
18 DROP PROCEDURE IF EXISTS test.p1;
19 DROP PROCEDURE IF EXISTS test.p2;
20 DROP TABLE IF EXISTS test.t2;
21 DROP TABLE IF EXISTS test.t1;
22 DROP TABLE IF EXISTS test.t3;
23 --enable_warnings
24 # End of cleanup
25
26 # Begin test section 1
27 CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id));
28 CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2));
29 CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16));
30
31 delimiter |;
32 CREATE PROCEDURE test.p1()
33 BEGIN
34 DECLARE done INT DEFAULT 0;
35 DECLARE spa CHAR(16);
36 DECLARE spb,spc INT;
37 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id;
38 DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2;
39 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
40
41 OPEN cur1;
42 OPEN cur2;
43
44 REPEAT
45 FETCH cur1 INTO spb, spa;
46 FETCH cur2 INTO spc;
47 IF NOT done THEN
48 IF spb < spc THEN
49 INSERT INTO test.t3 VALUES (spb,spa);
50 ELSE
51 INSERT INTO test.t3 VALUES (spc,spa);
52 END IF;
53 END IF;
54 UNTIL done END REPEAT;
55
56 CLOSE cur1;
57 CLOSE cur2;
58 END|
59 CREATE PROCEDURE test.p2()
60 BEGIN
61 INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle');
62 INSERT INTO test.t2 VALUES (4),(2),(1),(3);
63 UPDATE test.t1 SET id=id+4 WHERE id=4;
64 END|
65 delimiter ;|
66
67 CALL test.p2();
68 SELECT * FROM test.t1 ORDER BY id;
69 SELECT * FROM test.t2 ORDER BY id2;
70
71 sync_slave_with_master;
72 SELECT * FROM test.t1 ORDER BY id;
73 SELECT * FROM test.t2 ORDER BY id2;
74
75 connection master;
76 CALL test.p1();
77
78 let $wait_condition= SELECT COUNT(*) = 4 FROM t3;
79 --source include/wait_condition.inc
80 save_master_pos;
81 SELECT * FROM test.t3 ORDER BY id3;
82
83 sync_slave_with_master;
84 SELECT * FROM test.t3 ORDER BY id3;
85
86 connection master;
87
88 ALTER PROCEDURE test.p1 MODIFIES SQL DATA;
89 #show binlog events;
90
91 # Cleanup
92
93 connection master;
94 DROP PROCEDURE IF EXISTS test.p1;
95 DROP PROCEDURE IF EXISTS test.p2;
96 DROP TABLE IF EXISTS test.t1;
97 DROP TABLE IF EXISTS test.t2;
98 DROP TABLE IF EXISTS test.t3;
99 sync_slave_with_master;
100
101 # End of 5.0 test case
102 --source include/rpl_end.inc