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 # 2006-02-08 By JBM added ORDER BY for use with NDB engine
10 #############################################################################
11
12 # Includes
13 -- source include/have_binlog_format_row.inc
14 -- source include/set_privilege_checks_user_as_system_user.inc
15 -- source include/master-slave.inc
16
17
18 # Begin clean up test section
19 connection master;
20 --disable_warnings
21 DROP PROCEDURE IF EXISTS test.p1;
22 DROP PROCEDURE IF EXISTS test.p2;
23 DROP TABLE IF EXISTS test.t2;
24 DROP TABLE IF EXISTS test.t1;
25 DROP TABLE IF EXISTS test.t3;
26 --enable_warnings
27 # End of cleanup
28
29 # Begin test section 1
30 CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id));
31 CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2));
32 CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16));
33
34 delimiter |;
35 CREATE PROCEDURE test.p1()
36 BEGIN
37 DECLARE done INT DEFAULT 0;
38 DECLARE spa CHAR(16);
39 DECLARE spb,spc INT;
40 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id;
41 DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2;
42 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
43
44 OPEN cur1;
45 OPEN cur2;
46
47 REPEAT
48 FETCH cur1 INTO spb, spa;
49 FETCH cur2 INTO spc;
50 IF NOT done THEN
51 IF spb < spc THEN
52 INSERT INTO test.t3 VALUES (spb,spa);
53 ELSE
54 INSERT INTO test.t3 VALUES (spc,spa);
55 END IF;
56 END IF;
57 UNTIL done END REPEAT;
58
59 CLOSE cur1;
60 CLOSE cur2;
61 END|
62 CREATE PROCEDURE test.p2()
63 BEGIN
64 INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle');
65 INSERT INTO test.t2 VALUES (4),(2),(1),(3);
66 UPDATE test.t1 SET id=id+4 WHERE id=4;
67 END|
68 delimiter ;|
69
70 let $message=< ---- Master selects-- >;
71 --source include/show_msg.inc
72 CALL test.p2();
73 SELECT * FROM test.t1 ORDER BY id;
74 SELECT * FROM test.t2 ORDER BY id2;
75
76 let $message=< ---- Slave selects-- >;
77 --source include/show_msg.inc
78 --source include/sync_slave_sql_with_master.inc
79 SELECT * FROM test.t1 ORDER BY id;
80 SELECT * FROM test.t2 ORDER BY id2;
81
82 let $message=< ---- Master selects-- >;
83 --source include/show_msg.inc
84 connection master;
85 CALL test.p1();
86
87 let $wait_condition= SELECT COUNT(*) = 4 FROM t3;
88 --source include/wait_condition_or_abort.inc
89 save_master_pos;
90 SELECT * FROM test.t3 ORDER BY id3;
91
92 let $message=< ---- Slave selects-- >;
93 --source include/show_msg.inc
94 --source include/sync_slave_sql_with_master.inc
95 SELECT * FROM test.t3 ORDER BY id3;
96
97 connection master;
98
99 ALTER PROCEDURE test.p1 MODIFIES SQL DATA;
100 #show binlog events;
101
102 # Cleanup
103
104 connection master;
105 DROP PROCEDURE IF EXISTS test.p1;
106 DROP PROCEDURE IF EXISTS test.p2;
107 DROP TABLE IF EXISTS test.t1;
108 DROP TABLE IF EXISTS test.t2;
109 DROP TABLE IF EXISTS test.t3;
110 --source include/sync_slave_sql_with_master.inc
111
112 # End of 5.0 test case
113 --source include/rpl_end.inc