1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
#############################################################################
# Original Author: JBM #
# Original Date: Aug/16/2005 #
# Updated: 8/29/2005 Remove sleep calls add dump and diff #
#############################################################################
# TEST: This test includes all trigger types. BEFORE/AFTER INSERT, UPDATE & #
# DELETE. In addition, includes cursor, bit, varchar, flow control, #
# looping, ROUND(), NOW(), YEAR(), TIMESTAMP #
#############################################################################
# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc
#-- disable_query_log
#-- disable_result_log
# Begin clean up test section
connection master;
--disable_warnings
--error 0,1360
DROP TRIGGER test.t1_bi;
--error 0,1360
DROP TRIGGER test.t2_ai;
--error 0,1360
DROP TRIGGER test.t1_bu;
--error 0,1360
DROP TRIGGER test.t2_au;
--error 0,1360
DROP TRIGGER test.t1_bd;
--error 0,1360
DROP TRIGGER test.t2_ad;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;
DROP TABLE IF EXISTS test.t3;
--enable_warnings
# test section 1
CREATE TABLE test.t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
CREATE TABLE test.t2 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
CREATE TABLE test.t3 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
# Note Most of these cause the slave to core or do not produce desired results. Currently commenting out the ones not working until they are fixed.
delimiter |;
CREATE TRIGGER test.t1_bi BEFORE INSERT ON test.t1 FOR EACH ROW UPDATE test.t3 SET b1=1 and y=YEAR(NOW())|
CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW BEGIN
INSERT INTO test.t3 VALUES(NULL,0,'MySQL Replication team rocks!', 'Dark beer in prague is #1',12345.34,12.51,0,1965,NOW());
UPDATE test.t3 SET f = ROUND(f);
END|
CREATE TRIGGER test.t1_bu BEFORE UPDATE on test.t1 FOR EACH ROW BEGIN
UPDATE test.t3 SET y = '2000';
INSERT INTO test.t3 VALUES(NULL,1,'Testing MySQL databases before update ', 'Insert should work',621.43, 0105.21,0,1974,NOW());
END|
CREATE TRIGGER test.t2_au AFTER UPDATE on test.t2 FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a DECIMAL(10,4);
DECLARE b FLOAT;
DECLARE num MEDIUMINT;
DECLARE cur1 CURSOR FOR SELECT t2.id, t2.d, t2.f FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO num, a, b;
IF NOT done THEN
UPDATE test.t3 SET total =(a*b) WHERE ID = num;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END|
CREATE TRIGGER test.t1_bd BEFORE DELETE on test.t1 FOR EACH ROW BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a BIT(8);
DECLARE b VARCHAR(255);
DECLARE c CHAR(255);
DECLARE d DECIMAL(10,4);
DECLARE e FLOAT;
DECLARE f BIGINT UNSIGNED;
DECLARE g YEAR;
DECLARE h TIMESTAMP;
DECLARE cur1 CURSOR FOR SELECT t1.b1, t1.vc, t1.bc, t1.d, t1.f, t1.total, t1.y, t1.t FROM test.t1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a, b, c, d, e, f, g, h;
IF NOT done THEN
INSERT INTO test.t3 VALUES(NULL, a, b, c, d, e, f, g, h);
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END|
CREATE TRIGGER test.t2_ad AFTER DELETE ON test.t2 FOR EACH ROW
DELETE FROM test.t1|
delimiter ;|
INSERT INTO test.t1 VALUES(NULL,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer',654321.4321,15.21,0,1965,NOW());
INSERT INTO test.t2 VALUES(NULL,0,'Testing MySQL databases is a cool ', 'MySQL Customers ROCK!',654321.4321,1.24521,0,YEAR(NOW()),NOW());
UPDATE test.t1 SET b1 = 0 WHERE b1 = 1;
INSERT INTO test.t2 VALUES(NULL,1,'This is an after update test.', 'If this works, total will not be zero on the master or slave',1.4321,5.221,0,YEAR(NOW()),NOW());
UPDATE test.t2 SET b1 = 0 WHERE b1 = 1;
INSERT INTO test.t1 VALUES(NULL,1,'add some more test data test.', 'and hope for the best', 3.321,5.221,0,YEAR(NOW()),NOW());
# To make sure BUG#14698 is gone, we sleep 2 seconds before calling trigger
# (with the bug in, that caused differences in TIMESTAMP columns).
# We just need to let the machine's clock advance, it's not
# to do synchronization, so real_sleep is good.
real_sleep 2;
DELETE FROM test.t1 WHERE id = 1;
DELETE FROM test.t2 WHERE id = 1;
save_master_pos;
connection slave;
sync_with_master;
connection master;
# time to dump the databases and so we can see if they match
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trg003_master.sql
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trg003_slave.sql
# cleanup
--disable_warnings
--error 0,1360
DROP TRIGGER test.t1_bi;
--error 0,1360
DROP TRIGGER test.t2_ai;
--error 0,1360
DROP TRIGGER test.t1_bu;
--error 0,1360
DROP TRIGGER test.t2_au;
--error 0,1360
DROP TRIGGER test.t1_bd;
--error 0,1360
DROP TRIGGER test.t2_ad;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;
DROP TABLE IF EXISTS test.t3;
--enable_warnings
diff_files $MYSQLTEST_VARDIR/tmp/trg003_master.sql $MYSQLTEST_VARDIR/tmp/trg003_slave.sql;
# End of 5.0 test case
|
