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
#############################################################################
17
-- source include/have_binlog_format_row.inc
18
-- source include/master-slave.inc
21
# Begin test section 1
23
eval CREATE TABLE test.t1 (a INT AUTO_INCREMENT KEY, t CHAR(6)) ENGINE=$engine_type;
24
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;
27
create procedure test.p1(IN i CHAR(6))
29
INSERT INTO test.t1 (t) VALUES (i);
30
INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID());
32
create procedure test.p2(IN i INT)
34
DELETE FROM test.t1 where a < i;
38
let $message=< -- test 1 call p1 -- >;
39
--source include/show_msg.inc
40
SET FOREIGN_KEY_CHECKS=1;
41
call test.p1('texas');
46
call test.p1('MySQL');
48
let $message=< -- test 1 select master after p1 -- >;
49
--source include/show_msg.inc
51
SELECT * FROM test.t1;
52
SELECT * FROM test.t2;
54
let $message=< -- test 1 select slave after p1 -- >;
55
--source include/show_msg.inc
56
sync_slave_with_master;
57
SELECT * FROM test.t1;
58
SELECT * FROM test.t2;
60
let $message=< -- test 1 call p2 & select master -- >;
61
--source include/show_msg.inc
64
SELECT * FROM test.t1;
65
SELECT * FROM test.t2;
67
let $message=< -- test 1 select slave after p2 -- >;
68
--source include/show_msg.inc
69
sync_slave_with_master;
70
SELECT * FROM test.t1;
71
SELECT * FROM test.t2;
75
let $message=< -- End test 1 Begin test 2 -- >;
76
--source include/show_msg.inc
77
# End test 1 Begin test 2
80
SET FOREIGN_KEY_CHECKS=0;
81
DROP PROCEDURE IF EXISTS test.p1;
82
DROP PROCEDURE IF EXISTS test.p2;
83
DROP TABLE IF EXISTS test.t1;
84
DROP TABLE IF EXISTS test.t2;
88
eval CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=$engine_type;
89
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;
92
CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6))
94
INSERT INTO test.t1 (a,t) VALUES (nm, ch);
95
INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID());
97
CREATE PROCEDURE test.p2(IN i INT)
99
UPDATE test.t1 SET a = i*10 WHERE a = i;
102
SET FOREIGN_KEY_CHECKS=1;
103
CALL test.p1(1,'texas');
104
CALL test.p1(2,'Live');
105
CALL test.p1(3,'next');
106
CALL test.p1(4,'to');
107
CALL test.p1(5,'OK');
108
CALL test.p1(6,'MySQL');
110
let $message=< -- test 2 select Master after p1 -- >;
111
--source include/show_msg.inc
112
SELECT * FROM test.t1;
113
SELECT * FROM test.t2;
115
let $message=< -- test 2 select Slave after p1 -- >;
116
--source include/show_msg.inc
117
sync_slave_with_master;
118
SELECT * FROM test.t1;
119
SELECT * FROM test.t2;
121
let $message=< -- test 2 call p2 & select Master -- >;
122
--source include/show_msg.inc
127
SELECT * FROM test.t1;
128
SELECT * FROM test.t2;
130
let $message=< -- test 1 select Slave after p2 -- >;
131
--source include/show_msg.inc
132
sync_slave_with_master;
133
SELECT * FROM test.t1;
134
SELECT * FROM test.t2;
138
let $message=< -- End test 2 Begin test 3 -- >;
139
--source include/show_msg.inc
140
# End test 2 begin test 3
142
eval CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=$engine_type;
145
CREATE PROCEDURE test.p3(IN n INT)
151
INSERT INTO test.t3 VALUES (NULL,'NONE');
160
-- disable_result_log
164
eval call test.p3($n);
171
select * from test.t3;
172
sync_slave_with_master;
173
select * from test.t3;
179
-- disable_result_log
183
eval call test.p3($n);
190
select * from test.t3;
191
sync_slave_with_master;
192
select * from test.t3;
195
#show binlog events from 1627;
200
SET FOREIGN_KEY_CHECKS=0;
201
DROP PROCEDURE test.p3;
202
DROP PROCEDURE test.p1;
203
DROP PROCEDURE test.p2;
208
# End of 5.0 test case
209
--source include/rpl_end.inc