Debian Codegraph @

mysql-test/suite/rpl/include/rpl_row_sp002.test

raw
1 #############################################################################
2 # This test is being created to test out the non deterministic items with #
3 # row based replication. #
4 #############################################################################
5 # Test: Contains two stored procedures test one that insert data into tables#
6 # and use the LAST_INSERTED_ID() on tables with FOREIGN KEY(a) #
7 # REFERENCES ON DELETE CASCADE. This test also has a delete sp that #
8 # should cause a delete cascade. #
9 # The second test has a sp that will either insert rows or delete from#
10 # the table depending on the CASE outcome. The test uses this SP in a#
11 # transaction first rolling back and then commiting, #
12 #############################################################################
13
14
15
16 # Includes
17 -- source include/have_binlog_format_row.inc
18 -- source include/master-slave.inc
19
20 # Begin test section 1
21
22 eval CREATE TABLE test.t1 (a INT AUTO_INCREMENT KEY, t CHAR(6)) ENGINE=$engine_type;
23 eval CREATE TABLE test.t2 (a INT AUTO_INCREMENT KEY, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON DELETE CASCADE) ENGINE=$engine_type;
24
25 delimiter |;
26 create procedure test.p1(IN i CHAR(6))
27 begin
28 INSERT INTO test.t1 (t) VALUES (i);
29 INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID());
30 end|
31 create procedure test.p2(IN i INT)
32 begin
33 DELETE FROM test.t1 where a < i;
34 end|
35 delimiter ;|
36
37 let $message=< -- test 1 call p1 -- >;
38 --source include/show_msg.inc
39 SET FOREIGN_KEY_CHECKS=1;
40 call test.p1('texas');
41 call test.p1('Live');
42 call test.p1('next');
43 call test.p1('to');
44 call test.p1('OK');
45 call test.p1('MySQL');
46
47 let $message=< -- test 1 select master after p1 -- >;
48 --source include/show_msg.inc
49
50 SELECT * FROM test.t1;
51 SELECT * FROM test.t2;
52
53 let $message=< -- test 1 select slave after p1 -- >;
54 --source include/show_msg.inc
55 sync_slave_with_master;
56 SELECT * FROM test.t1;
57 SELECT * FROM test.t2;
58
59 let $message=< -- test 1 call p2 & select master -- >;
60 --source include/show_msg.inc
61 connection master;
62 call test.p2(4);
63 SELECT * FROM test.t1;
64 SELECT * FROM test.t2;
65
66 let $message=< -- test 1 select slave after p2 -- >;
67 --source include/show_msg.inc
68 sync_slave_with_master;
69 SELECT * FROM test.t1;
70 SELECT * FROM test.t2;
71
72 connection master;
73 #show binlog events;
74 let $message=< -- End test 1 Begin test 2 -- >;
75 --source include/show_msg.inc
76 # End test 1 Begin test 2
77
78 --disable_warnings
79 SET FOREIGN_KEY_CHECKS=0;
80 DROP PROCEDURE IF EXISTS test.p1;
81 DROP PROCEDURE IF EXISTS test.p2;
82 DROP TABLE IF EXISTS test.t1;
83 DROP TABLE IF EXISTS test.t2;
84 --enable_warnings
85 # End of cleanup
86
87 eval CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=$engine_type;
88 eval CREATE TABLE test.t2 (a INT, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON UPDATE CASCADE, PRIMARY KEY(a)) ENGINE=$engine_type;
89
90 delimiter |;
91 CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6))
92 BEGIN
93 INSERT INTO test.t1 (a,t) VALUES (nm, ch);
94 INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID());
95 END|
96 CREATE PROCEDURE test.p2(IN i INT)
97 BEGIN
98 UPDATE test.t1 SET a = i*10 WHERE a = i;
99 END|
100 delimiter ;|
101 SET FOREIGN_KEY_CHECKS=1;
102 CALL test.p1(1,'texas');
103 CALL test.p1(2,'Live');
104 CALL test.p1(3,'next');
105 CALL test.p1(4,'to');
106 CALL test.p1(5,'OK');
107 CALL test.p1(6,'MySQL');
108
109 let $message=< -- test 2 select Master after p1 -- >;
110 --source include/show_msg.inc
111 SELECT * FROM test.t1;
112 SELECT * FROM test.t2;
113
114 let $message=< -- test 2 select Slave after p1 -- >;
115 --source include/show_msg.inc
116 sync_slave_with_master;
117 SELECT * FROM test.t1;
118 SELECT * FROM test.t2;
119
120 let $message=< -- test 2 call p2 & select Master -- >;
121 --source include/show_msg.inc
122 connection master;
123 CALL test.p2(2);
124 CALL test.p2(4);
125 CALL test.p2(6);
126 SELECT * FROM test.t1;
127 SELECT * FROM test.t2;
128
129 let $message=< -- test 1 select Slave after p2 -- >;
130 --source include/show_msg.inc
131 sync_slave_with_master;
132 SELECT * FROM test.t1;
133 SELECT * FROM test.t2;
134
135 connection master;
136 #show binlog events;
137 let $message=< -- End test 2 Begin test 3 -- >;
138 --source include/show_msg.inc
139 # End test 2 begin test 3
140
141 eval CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=$engine_type;
142
143 delimiter |;
144 CREATE PROCEDURE test.p3(IN n INT)
145 begin
146 CASE n
147 WHEN 2 THEN
148 DELETE from test.t3;
149 ELSE
150 INSERT INTO test.t3 VALUES (NULL,'NONE');
151 END CASE;
152 end|
153 delimiter ;|
154
155 SET AUTOCOMMIT=0;
156 START TRANSACTION;
157
158 -- disable_query_log
159 -- disable_result_log
160 let $n=50;
161 while ($n)
162 {
163 eval call test.p3($n);
164 dec $n;
165 }
166 -- enable_result_log
167 -- enable_query_log
168
169 ROLLBACK;
170 select * from test.t3;
171 sync_slave_with_master;
172 select * from test.t3;
173
174 connection master;
175 START TRANSACTION;
176
177 -- disable_query_log
178 -- disable_result_log
179 let $n=50;
180 while ($n)
181 {
182 eval call test.p3($n);
183 dec $n;
184 }
185 -- enable_result_log
186 -- enable_query_log
187
188 COMMIT;
189 select * from test.t3;
190 sync_slave_with_master;
191 select * from test.t3;
192
193 connection master;
194 #show binlog events from 1627;
195
196
197 # First lets cleanup
198 SET AUTOCOMMIT=1;
199 SET FOREIGN_KEY_CHECKS=0;
200 DROP PROCEDURE test.p3;
201 DROP PROCEDURE test.p1;
202 DROP PROCEDURE test.p2;
203 DROP TABLE test.t1;
204 DROP TABLE test.t2;
205 DROP TABLE test.t3;
206
207 # End of 5.0 test case
208 --source include/rpl_end.inc