1 include/master-slave.inc
3 CREATE TABLE test.t1 (a INT AUTO_INCREMENT KEY, t CHAR(6)) ENGINE=INNODB;
4 CREATE TABLE test.t2 (a INT AUTO_INCREMENT KEY, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON DELETE CASCADE) ENGINE=INNODB;
5 create procedure test.p1(IN i CHAR(6))
7 INSERT INTO test.t1 (t) VALUES (i);
8 INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID());
10 create procedure test.p2(IN i INT)
12 DELETE FROM test.t1 where a < i;
15 < -- test 1 call p1 -- >
16 ------------------------
17 SET FOREIGN_KEY_CHECKS=1;
18 call test.p1('texas');
23 call test.p1('MySQL');
25 < -- test 1 select master after p1 -- >
26 ---------------------------------------
27 SELECT * FROM test.t1;
35 SELECT * FROM test.t2;
44 < -- test 1 select slave after p1 -- >
45 --------------------------------------
46 SELECT * FROM test.t1;
54 SELECT * FROM test.t2;
63 < -- test 1 call p2 & select master -- >
64 ----------------------------------------
66 SELECT * FROM test.t1;
71 SELECT * FROM test.t2;
77 < -- test 1 select slave after p2 -- >
78 --------------------------------------
79 SELECT * FROM test.t1;
84 SELECT * FROM test.t2;
90 < -- End test 1 Begin test 2 -- >
91 ---------------------------------
92 SET FOREIGN_KEY_CHECKS=0;
93 DROP PROCEDURE IF EXISTS test.p1;
94 DROP PROCEDURE IF EXISTS test.p2;
95 DROP TABLE IF EXISTS test.t1;
96 DROP TABLE IF EXISTS test.t2;
97 CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=INNODB;
98 CREATE TABLE test.t2 (a INT, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON UPDATE CASCADE, PRIMARY KEY(a)) ENGINE=INNODB;
99 CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6))
101 INSERT INTO test.t1 (a,t) VALUES (nm, ch);
102 INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID());
104 CREATE PROCEDURE test.p2(IN i INT)
106 UPDATE test.t1 SET a = i*10 WHERE a = i;
108 SET FOREIGN_KEY_CHECKS=1;
109 CALL test.p1(1,'texas');
110 CALL test.p1(2,'Live');
111 CALL test.p1(3,'next');
112 CALL test.p1(4,'to');
113 CALL test.p1(5,'OK');
114 CALL test.p1(6,'MySQL');
116 < -- test 2 select Master after p1 -- >
117 ---------------------------------------
118 SELECT * FROM test.t1;
126 SELECT * FROM test.t2;
135 < -- test 2 select Slave after p1 -- >
136 --------------------------------------
137 SELECT * FROM test.t1;
145 SELECT * FROM test.t2;
154 < -- test 2 call p2 & select Master -- >
155 ----------------------------------------
159 SELECT * FROM test.t1;
167 SELECT * FROM test.t2;
176 < -- test 1 select Slave after p2 -- >
177 --------------------------------------
178 SELECT * FROM test.t1;
186 SELECT * FROM test.t2;
195 < -- End test 2 Begin test 3 -- >
196 ---------------------------------
197 CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=INNODB;
198 CREATE PROCEDURE test.p3(IN n INT)
204 INSERT INTO test.t3 VALUES (NULL,'NONE');
210 select * from test.t3;
212 select * from test.t3;
216 select * from test.t3;
219 select * from test.t3;
223 SET FOREIGN_KEY_CHECKS=0;
224 DROP PROCEDURE test.p3;
225 DROP PROCEDURE test.p1;
226 DROP PROCEDURE test.p2;