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