1
by brian
clean slate |
1 |
#############################################################################
|
2 |
# Original Author: JBM # |
|
3 |
# Original Date: Aug/14/2005 # |
|
4 |
# Updated: 08/29/2005 added save master pos and sync with master # |
|
5 |
#############################################################################
|
|
6 |
# TEST: Taken and modified from BUG#12048 After Insert updates replication # |
|
7 |
#############################################################################
|
|
8 |
||
9 |
# Includes |
|
10 |
-- source include/have_binlog_format_row.inc
|
|
11 |
-- source include/master-slave.inc
|
|
12 |
||
13 |
#-- disable_query_log |
|
14 |
#-- disable_result_log |
|
15 |
||
16 |
# Begin clean up test section |
|
17 |
connection master; |
|
18 |
--disable_warnings
|
|
19 |
--error 0,1360
|
|
20 |
DROP TRIGGER test.t2_ai; |
|
21 |
DROP TABLE IF EXISTS test.t1; |
|
22 |
DROP TABLE IF EXISTS test.t2; |
|
23 |
DROP TABLE IF EXISTS test.t3; |
|
24 |
--enable_warnings
|
|
25 |
||
26 |
# test section 1, Taken from bug #12408 |
|
27 |
||
28 |
CREATE TABLE test.t2 (value CHAR(30),domain_id INT, mailaccount_id INT, program CHAR(30),keey CHAR(30),PRIMARY KEY(domain_id)); |
|
29 |
||
30 |
CREATE TABLE test.t3 (value CHAR(30),domain_id INT, mailaccount_id INT, program CHAR(30),keey CHAR(30),PRIMARY KEY(domain_id)); |
|
31 |
||
32 |
CREATE TABLE test.t1 (id INT,domain CHAR(30),PRIMARY KEY(id)); |
|
33 |
||
34 |
delimiter |; |
|
35 |
CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t3 ms, test.t1 d SET ms.value='No' WHERE ms.domain_id = (SELECT max(id) FROM test.t1 WHERE domain='example.com') AND ms.mailaccount_id IS NULL AND ms.program='spamfilter' AND ms.keey='scan_incoming'| |
|
36 |
delimiter ;| |
|
37 |
||
38 |
INSERT INTO test.t1 VALUES (1, 'example.com'),(2, 'mysql.com'),(3, 'earthmotherwear.com'), (4, 'yahoo.com'),(5, 'example.com'); |
|
39 |
||
40 |
SELECT * FROM test.t1 ORDER BY id; |
|
41 |
#show binlog events; |
|
42 |
save_master_pos; |
|
43 |
connection slave; |
|
44 |
sync_with_master; |
|
45 |
SELECT * FROM test.t1 ORDER BY id; |
|
46 |
connection master; |
|
47 |
||
48 |
INSERT INTO test.t3 VALUES ('Yes', 5, NULL, 'spamfilter','scan_incoming'); |
|
49 |
INSERT INTO test.t3 VALUES ('Yes', 1, NULL, 'spamfilter','scan_incoming'); |
|
50 |
INSERT INTO test.t2 VALUES ('Yes', 1, NULL, 'spamfilter','scan_incoming'); |
|
51 |
||
52 |
select * from test.t2; |
|
53 |
select * from test.t3; |
|
54 |
save_master_pos; |
|
55 |
connection slave; |
|
56 |
sync_with_master; |
|
57 |
select * from test.t2; |
|
58 |
select * from test.t3; |
|
59 |
connection master; |
|
60 |
||
61 |
DELETE FROM test.t1 WHERE id = 1; |
|
62 |
||
63 |
SELECT * FROM test.t1 ORDER BY id; |
|
64 |
connection master; |
|
65 |
SELECT * FROM test.t1 ORDER BY id; |
|
66 |
save_master_pos; |
|
67 |
connection slave; |
|
68 |
sync_with_master; |
|
69 |
SELECT * FROM test.t1 ORDER BY id; |
|
70 |
||
71 |
# Cleanup |
|
72 |
connection master; |
|
73 |
#show binlog events; |
|
74 |
DROP TRIGGER test.t2_ai; |
|
75 |
DROP TABLE test.t1; |
|
76 |
DROP TABLE test.t2; |
|
77 |
DROP TABLE test.t3; |
|
78 |
sync_slave_with_master; |
|
79 |
||
80 |
# End of 5.0 test case |