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 #############################################################################
13 -- source include/have_binlog_format_row.inc
14 -- source include/master-slave.inc
17 # Begin clean up test section
20 DROP PROCEDURE IF EXISTS test.p1;
21 DROP PROCEDURE IF EXISTS test.p2;
22 DROP TABLE IF EXISTS test.t2;
23 DROP TABLE IF EXISTS test.t1;
24 DROP TABLE IF EXISTS test.t3;
28 # Begin test section 1
29 CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id));
30 CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2));
31 CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16));
34 CREATE PROCEDURE test.p1()
36 DECLARE done INT DEFAULT 0;
39 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id;
40 DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2;
41 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
47 FETCH cur1 INTO spb, spa;
51 INSERT INTO test.t3 VALUES (spb,spa);
53 INSERT INTO test.t3 VALUES (spc,spa);
56 UNTIL done END REPEAT;
61 CREATE PROCEDURE test.p2()
63 INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle');
64 INSERT INTO test.t2 VALUES (4),(2),(1),(3);
65 UPDATE test.t1 SET id=id+4 WHERE id=4;
69 let $message=< ---- Master selects-- >;
70 --source include/show_msg.inc
72 SELECT * FROM test.t1 ORDER BY id;
73 SELECT * FROM test.t2 ORDER BY id2;
75 let $message=< ---- Slave selects-- >;
76 --source include/show_msg.inc
80 SELECT * FROM test.t1 ORDER BY id;
81 SELECT * FROM test.t2 ORDER BY id2;
83 let $message=< ---- Master selects-- >;
84 --source include/show_msg.inc
88 let $wait_condition= SELECT COUNT(*) = 4 FROM t3;
89 --source include/wait_condition.inc
91 SELECT * FROM test.t3 ORDER BY id3;
93 let $message=< ---- Slave selects-- >;
94 --source include/show_msg.inc
97 SELECT * FROM test.t3 ORDER BY id3;
101 ALTER PROCEDURE test.p1 MODIFIES SQL DATA;
107 DROP PROCEDURE IF EXISTS test.p1;
108 DROP PROCEDURE IF EXISTS test.p2;
109 DROP TABLE IF EXISTS test.t1;
110 DROP TABLE IF EXISTS test.t2;
111 DROP TABLE IF EXISTS test.t3;
112 sync_slave_with_master;
114 # End of 5.0 test case
115 --source include/rpl_end.inc