1
#############################################################################
2
# This test is being created to test out the non deterministic items with #
3
# row based replication. #
4
# Original Author: JBM #
5
# Original Date: Aug/09/2005 #
6
# Updated: Aug/29/2005 #
7
#############################################################################
8
# Test: Contains two stored procedures test one that insert data into tables#
9
# and use the LAST_INSERTED_ID() on tables with FOREIGN KEY(a) #
10
# REFERENCES ON DELETE CASCADE. This test also has a delete sp that #
11
# should cause a delete cascade. #
12
# The second test has a sp that will either insert rows or delete from#
13
# the table depending on the CASE outcome. The test uses this SP in a#
14
# transaction first rolling back and then commiting, #
15
#############################################################################
16
# Mod Date: 08/22/2005 #
17
# TEST: Added test to include UPDATE CASCADE on table with FK per Trudy #
18
#############################################################################
23
-- source include/have_binlog_format_row.inc
24
-- source include/master-slave.inc
27
# Begin clean up test section
30
DROP PROCEDURE IF EXISTS test.p1;
31
DROP PROCEDURE IF EXISTS test.p2;
32
DROP PROCEDURE IF EXISTS test.p3;
33
DROP TABLE IF EXISTS test.t3;
34
DROP TABLE IF EXISTS test.t1;
35
DROP TABLE IF EXISTS test.t2;
39
# Begin test section 1
41
eval CREATE TABLE test.t1 (a INT AUTO_INCREMENT KEY, t CHAR(6)) ENGINE=$engine_type;
42
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;
45
create procedure test.p1(IN i CHAR(6))
47
INSERT INTO test.t1 (t) VALUES (i);
48
INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID());
50
create procedure test.p2(IN i INT)
52
DELETE FROM test.t1 where a < i;
56
let $message=< -- test 1 call p1 -- >;
57
--source include/show_msg.inc
58
SET FOREIGN_KEY_CHECKS=1;
59
call test.p1('texas');
64
call test.p1('MySQL');
66
let $message=< -- test 1 select master after p1 -- >;
67
--source include/show_msg.inc
69
SELECT * FROM test.t1;
70
SELECT * FROM test.t2;
72
let $message=< -- test 1 select slave after p1 -- >;
73
--source include/show_msg.inc
77
SELECT * FROM test.t1;
78
SELECT * FROM test.t2;
80
let $message=< -- test 1 call p2 & select master -- >;
81
--source include/show_msg.inc
84
SELECT * FROM test.t1;
85
SELECT * FROM test.t2;
87
let $message=< -- test 1 select slave after p2 -- >;
88
--source include/show_msg.inc
92
SELECT * FROM test.t1;
93
SELECT * FROM test.t2;
97
let $message=< -- End test 1 Begin test 2 -- >;
98
--source include/show_msg.inc
99
# End test 1 Begin test 2
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;
110
eval CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=$engine_type;
111
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;
114
CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6))
116
INSERT INTO test.t1 (a,t) VALUES (nm, ch);
117
INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID());
119
CREATE PROCEDURE test.p2(IN i INT)
121
UPDATE test.t1 SET a = i*10 WHERE a = i;
124
SET FOREIGN_KEY_CHECKS=1;
125
CALL test.p1(1,'texas');
126
CALL test.p1(2,'Live');
127
CALL test.p1(3,'next');
128
CALL test.p1(4,'to');
129
CALL test.p1(5,'OK');
130
CALL test.p1(6,'MySQL');
132
let $message=< -- test 2 select Master after p1 -- >;
133
--source include/show_msg.inc
134
SELECT * FROM test.t1;
135
SELECT * FROM test.t2;
137
let $message=< -- test 2 select Slave after p1 -- >;
138
--source include/show_msg.inc
142
SELECT * FROM test.t1;
143
SELECT * FROM test.t2;
145
let $message=< -- test 2 call p2 & select Master -- >;
146
--source include/show_msg.inc
151
SELECT * FROM test.t1;
152
SELECT * FROM test.t2;
154
let $message=< -- test 1 select Slave after p2 -- >;
155
--source include/show_msg.inc
159
SELECT * FROM test.t1;
160
SELECT * FROM test.t2;
164
let $message=< -- End test 2 Begin test 3 -- >;
165
--source include/show_msg.inc
166
# End test 2 begin test 3
168
eval CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=$engine_type;
171
CREATE PROCEDURE test.p3(IN n INT)
177
INSERT INTO test.t3 VALUES (NULL,'NONE');
186
-- disable_result_log
190
eval call test.p3($n);
197
select * from test.t3;
201
select * from test.t3;
207
-- disable_result_log
211
eval call test.p3($n);
218
select * from test.t3;
222
select * from test.t3;
225
#show binlog events from 1627;
230
SET FOREIGN_KEY_CHECKS=0;
231
DROP PROCEDURE IF EXISTS test.p3;
232
DROP PROCEDURE IF EXISTS test.p1;
233
DROP PROCEDURE IF EXISTS test.p2;
234
DROP TABLE IF EXISTS test.t1;
235
DROP TABLE IF EXISTS test.t2;
236
DROP TABLE IF EXISTS test.t3;
237
sync_slave_with_master;
239
# End of 5.0 test case