Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_sp002_innodb.result

raw
1 include/master-slave.inc
2 [connection master]
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))
6 begin
7 INSERT INTO test.t1 (t) VALUES (i);
8 INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID());
9 end|
10 create procedure test.p2(IN i INT)
11 begin
12 DELETE FROM test.t1 where a < i;
13 end|
14
15 < -- test 1 call p1 -- >
16 ------------------------
17 SET FOREIGN_KEY_CHECKS=1;
18 call test.p1('texas');
19 call test.p1('Live');
20 call test.p1('next');
21 call test.p1('to');
22 call test.p1('OK');
23 call test.p1('MySQL');
24
25 < -- test 1 select master after p1 -- >
26 ---------------------------------------
27 SELECT * FROM test.t1;
28 a t
29 1 texas
30 2 Live
31 3 next
32 4 to
33 5 OK
34 6 MySQL
35 SELECT * FROM test.t2;
36 a f
37 1 1
38 2 2
39 3 3
40 4 4
41 5 5
42 6 6
43
44 < -- test 1 select slave after p1 -- >
45 --------------------------------------
46 connection slave;
47 SELECT * FROM test.t1;
48 a t
49 1 texas
50 2 Live
51 3 next
52 4 to
53 5 OK
54 6 MySQL
55 SELECT * FROM test.t2;
56 a f
57 1 1
58 2 2
59 3 3
60 4 4
61 5 5
62 6 6
63
64 < -- test 1 call p2 & select master -- >
65 ----------------------------------------
66 connection master;
67 call test.p2(4);
68 SELECT * FROM test.t1;
69 a t
70 4 to
71 5 OK
72 6 MySQL
73 SELECT * FROM test.t2;
74 a f
75 4 4
76 5 5
77 6 6
78
79 < -- test 1 select slave after p2 -- >
80 --------------------------------------
81 connection slave;
82 SELECT * FROM test.t1;
83 a t
84 4 to
85 5 OK
86 6 MySQL
87 SELECT * FROM test.t2;
88 a f
89 4 4
90 5 5
91 6 6
92 connection master;
93
94 < -- End test 1 Begin test 2 -- >
95 ---------------------------------
96 SET FOREIGN_KEY_CHECKS=0;
97 DROP PROCEDURE IF EXISTS test.p1;
98 DROP PROCEDURE IF EXISTS test.p2;
99 DROP TABLE IF EXISTS test.t1;
100 DROP TABLE IF EXISTS test.t2;
101 CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=INNODB;
102 CREATE TABLE test.t2 (a INT, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON UPDATE CASCADE, PRIMARY KEY(a)) ENGINE=INNODB;
103 CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6))
104 BEGIN
105 INSERT INTO test.t1 (a,t) VALUES (nm, ch);
106 INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID());
107 END|
108 CREATE PROCEDURE test.p2(IN i INT)
109 BEGIN
110 UPDATE test.t1 SET a = i*10 WHERE a = i;
111 END|
112 SET FOREIGN_KEY_CHECKS=1;
113 CALL test.p1(1,'texas');
114 CALL test.p1(2,'Live');
115 CALL test.p1(3,'next');
116 CALL test.p1(4,'to');
117 CALL test.p1(5,'OK');
118 CALL test.p1(6,'MySQL');
119
120 < -- test 2 select Master after p1 -- >
121 ---------------------------------------
122 SELECT * FROM test.t1;
123 a t
124 1 texas
125 2 Live
126 3 next
127 4 to
128 5 OK
129 6 MySQL
130 SELECT * FROM test.t2;
131 a f
132 1 6
133 2 6
134 3 6
135 4 6
136 5 6
137 6 6
138
139 < -- test 2 select Slave after p1 -- >
140 --------------------------------------
141 connection slave;
142 SELECT * FROM test.t1;
143 a t
144 1 texas
145 2 Live
146 3 next
147 4 to
148 5 OK
149 6 MySQL
150 SELECT * FROM test.t2;
151 a f
152 1 6
153 2 6
154 3 6
155 4 6
156 5 6
157 6 6
158
159 < -- test 2 call p2 & select Master -- >
160 ----------------------------------------
161 connection master;
162 CALL test.p2(2);
163 CALL test.p2(4);
164 CALL test.p2(6);
165 SELECT * FROM test.t1;
166 a t
167 1 texas
168 3 next
169 5 OK
170 20 Live
171 40 to
172 60 MySQL
173 SELECT * FROM test.t2;
174 a f
175 1 6
176 3 6
177 5 6
178 20 6
179 40 6
180 60 6
181
182 < -- test 1 select Slave after p2 -- >
183 --------------------------------------
184 connection slave;
185 SELECT * FROM test.t1;
186 a t
187 1 texas
188 3 next
189 5 OK
190 20 Live
191 40 to
192 60 MySQL
193 SELECT * FROM test.t2;
194 a f
195 1 6
196 3 6
197 5 6
198 20 6
199 40 6
200 60 6
201 connection master;
202
203 < -- End test 2 Begin test 3 -- >
204 ---------------------------------
205 CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=INNODB;
206 CREATE PROCEDURE test.p3(IN n INT)
207 begin
208 CASE n
209 WHEN 2 THEN
210 DELETE from test.t3;
211 ELSE
212 INSERT INTO test.t3 VALUES (NULL,'NONE');
213 END CASE;
214 end|
215 SET AUTOCOMMIT=0;
216 START TRANSACTION;
217 ROLLBACK;
218 select * from test.t3;
219 a t
220 connection slave;
221 select * from test.t3;
222 a t
223 connection master;
224 START TRANSACTION;
225 COMMIT;
226 select * from test.t3;
227 a t
228 98 NONE
229 connection slave;
230 select * from test.t3;
231 a t
232 98 NONE
233 connection master;
234 SET AUTOCOMMIT=1;
235 SET FOREIGN_KEY_CHECKS=0;
236 DROP PROCEDURE test.p3;
237 DROP PROCEDURE test.p1;
238 DROP PROCEDURE test.p2;
239 DROP TABLE test.t1;
240 DROP TABLE test.t2;
241 DROP TABLE test.t3;
242 include/rpl_end.inc