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
87
let $wait_condition= SELECT COUNT(*) = 4 FROM t3;
88
--source include/wait_condition.inc
90
SELECT * FROM test.t3 ORDER BY id3;
92
let $message=< ---- Slave selects-- >;
93
--source include/show_msg.inc
96
SELECT * FROM test.t3 ORDER BY id3;
100
ALTER PROCEDURE test.p1 MODIFIES SQL DATA;
106
DROP PROCEDURE IF EXISTS test.p1;
107
DROP PROCEDURE IF EXISTS test.p2;
108
DROP TABLE IF EXISTS test.t1;
109
DROP TABLE IF EXISTS test.t2;
110
DROP TABLE IF EXISTS test.t3;
111
sync_slave_with_master;
113
# End of 5.0 test case