2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
DROP PROCEDURE IF EXISTS test.p1;
8
DROP PROCEDURE IF EXISTS test.p2;
9
DROP PROCEDURE IF EXISTS test.p3;
10
DROP TABLE IF EXISTS test.t3;
11
DROP TABLE IF EXISTS test.t1;
12
DROP TABLE IF EXISTS test.t2;
13
CREATE TABLE test.t1 (a INT AUTO_INCREMENT KEY, t CHAR(6)) ENGINE=INNODB;
14
CREATE TABLE test.t2 (a INT AUTO_INCREMENT KEY, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON DELETE CASCADE) ENGINE=INNODB;
15
create procedure test.p1(IN i CHAR(6))
17
INSERT INTO test.t1 (t) VALUES (i);
18
INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID());
20
create procedure test.p2(IN i INT)
22
DELETE FROM test.t1 where a < i;
25
< -- test 1 call p1 -- >
26
------------------------
27
SET FOREIGN_KEY_CHECKS=1;
28
call test.p1('texas');
33
call test.p1('MySQL');
35
< -- test 1 select master after p1 -- >
36
---------------------------------------
37
SELECT * FROM test.t1;
45
SELECT * FROM test.t2;
54
< -- test 1 select slave after p1 -- >
55
--------------------------------------
56
SELECT * FROM test.t1;
64
SELECT * FROM test.t2;
73
< -- test 1 call p2 & select master -- >
74
----------------------------------------
76
SELECT * FROM test.t1;
81
SELECT * FROM test.t2;
87
< -- test 1 select slave after p2 -- >
88
--------------------------------------
89
SELECT * FROM test.t1;
94
SELECT * FROM test.t2;
100
< -- End test 1 Begin test 2 -- >
101
---------------------------------
102
SET FOREIGN_KEY_CHECKS=0;
103
DROP PROCEDURE IF EXISTS test.p1;
104
DROP PROCEDURE IF EXISTS test.p2;
105
DROP TABLE IF EXISTS test.t1;
106
DROP TABLE IF EXISTS test.t2;
107
CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=INNODB;
108
CREATE TABLE test.t2 (a INT, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON UPDATE CASCADE, PRIMARY KEY(a)) ENGINE=INNODB;
109
CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6))
111
INSERT INTO test.t1 (a,t) VALUES (nm, ch);
112
INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID());
114
CREATE PROCEDURE test.p2(IN i INT)
116
UPDATE test.t1 SET a = i*10 WHERE a = i;
118
SET FOREIGN_KEY_CHECKS=1;
119
CALL test.p1(1,'texas');
120
CALL test.p1(2,'Live');
121
CALL test.p1(3,'next');
122
CALL test.p1(4,'to');
123
CALL test.p1(5,'OK');
124
CALL test.p1(6,'MySQL');
126
< -- test 2 select Master after p1 -- >
127
---------------------------------------
128
SELECT * FROM test.t1;
136
SELECT * FROM test.t2;
145
< -- test 2 select Slave after p1 -- >
146
--------------------------------------
147
SELECT * FROM test.t1;
155
SELECT * FROM test.t2;
164
< -- test 2 call p2 & select Master -- >
165
----------------------------------------
169
SELECT * FROM test.t1;
177
SELECT * FROM test.t2;
186
< -- test 1 select Slave after p2 -- >
187
--------------------------------------
188
SELECT * FROM test.t1;
196
SELECT * FROM test.t2;
205
< -- End test 2 Begin test 3 -- >
206
---------------------------------
207
CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=INNODB;
208
CREATE PROCEDURE test.p3(IN n INT)
214
INSERT INTO test.t3 VALUES (NULL,'NONE');
220
select * from test.t3;
222
select * from test.t3;
226
select * from test.t3;
229
select * from test.t3;
233
SET FOREIGN_KEY_CHECKS=0;
234
DROP PROCEDURE IF EXISTS test.p3;
235
DROP PROCEDURE IF EXISTS test.p1;
236
DROP PROCEDURE IF EXISTS test.p2;
237
DROP TABLE IF EXISTS test.t1;
238
DROP TABLE IF EXISTS test.t2;
239
DROP TABLE IF EXISTS test.t3;