1
by brian
clean slate |
1 |
#############################################################################
|
2 |
# Original Author: JBM # |
|
3 |
# Original Date: Aug/09/2005 # |
|
4 |
#############################################################################
|
|
5 |
# TEST: Use after insert and before inset triggers and stored procdures to # |
|
6 |
# Update and insert data # |
|
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 |
DROP PROCEDURE IF EXISTS test.p2; |
|
20 |
DROP PROCEDURE IF EXISTS test.p3; |
|
21 |
--error 0,1360
|
|
22 |
DROP TRIGGER test.t2_ai; |
|
23 |
--error 0,1360
|
|
24 |
DROP TRIGGER test.t3_bi_t2; |
|
25 |
--error 0,1360
|
|
26 |
DROP TABLE IF EXISTS test.t1; |
|
27 |
DROP TABLE IF EXISTS test.t2; |
|
28 |
DROP TABLE IF EXISTS test.t3; |
|
29 |
||
30 |
||
31 |
# test section 1, lets add a trigger to the mix. Taken from bug #12280 |
|
32 |
let $message=<Begin test section 1 (Tiggers & SP)>; |
|
33 |
--source include/show_msg.inc
|
|
34 |
||
35 |
CREATE TABLE test.t1 (n MEDIUMINT NOT NULL, d DATETIME, PRIMARY KEY(n)); |
|
36 |
CREATE TABLE test.t2 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); |
|
37 |
CREATE TABLE test.t3 (n MEDIUMINT NOT NULL AUTO_INCREMENT, d DATETIME, PRIMARY KEY(n)); |
|
38 |
||
39 |
INSERT INTO test.t1 VALUES (1,NOW()); |
|
40 |
||
41 |
delimiter //; |
|
42 |
CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t1 SET d=NOW() where n = 1// |
|
43 |
CREATE PROCEDURE test.p3() |
|
44 |
BEGIN
|
|
45 |
INSERT INTO test.t3 (d) VALUES (NOW()); |
|
46 |
END// |
|
47 |
CREATE TRIGGER test.t3_bi_t2 BEFORE INSERT ON test.t2 FOR EACH ROW CALL test.p3()// |
|
48 |
CREATE PROCEDURE test.p2() |
|
49 |
BEGIN
|
|
50 |
INSERT INTO test.t2 (f,d) VALUES (RAND(),NOW()); |
|
51 |
END// |
|
52 |
delimiter ;// |
|
53 |
||
54 |
# Make sure that all definition have propagated to the slave |
|
55 |
sync_slave_with_master; |
|
56 |
||
57 |
connection master; |
|
58 |
-- disable_query_log
|
|
59 |
-- disable_result_log
|
|
60 |
SET @wait_count = 1; |
|
61 |
let $1=10; |
|
62 |
while ($1) |
|
63 |
{
|
|
64 |
CALL test.p2(); |
|
65 |
let $wait_condition= SELECT COUNT(*) = @wait_count FROM test.t3; |
|
66 |
--source include/wait_condition.inc
|
|
67 |
--disable_query_log
|
|
68 |
SET @wait_count = @wait_count + 1; |
|
69 |
dec $1; |
|
70 |
}
|
|
71 |
-- enable_result_log
|
|
72 |
-- enable_query_log
|
|
73 |
||
74 |
# Just a precaution to make sure all changes have made it over to the |
|
75 |
# slave |
|
76 |
connection master; |
|
77 |
let $count = `select count(*) from t1`; |
|
78 |
eval INSERT INTO test.t1 VALUES ($count+1, NOW()); |
|
79 |
sync_slave_with_master; |
|
80 |
||
81 |
#show binlog events; |
|
82 |
#select * from test.t2; |
|
83 |
#select * from test.t3; |
|
84 |
#connection slave; |
|
85 |
#select * from test.t2; |
|
86 |
#select * from test.t3; |
|
87 |
||
88 |
let $message=<End test section 2 (Tiggers & SP)>; |
|
89 |
--source include/show_msg.inc
|
|
90 |
||
91 |
# time to dump the databases and so we can see if they match |
|
92 |
||
93 |
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trig001_master.sql
|
|
94 |
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trig001_slave.sql
|
|
95 |
||
96 |
# Cleanup |
|
97 |
connection master; |
|
98 |
DROP PROCEDURE test.p2; |
|
99 |
DROP PROCEDURE test.p3; |
|
100 |
DROP TRIGGER test.t2_ai; |
|
101 |
DROP TRIGGER test.t3_bi_t2; |
|
102 |
DROP TABLE test.t1; |
|
103 |
DROP TABLE test.t2; |
|
104 |
DROP TABLE test.t3; |
|
105 |
sync_slave_with_master; |
|
106 |
||
107 |
# Lets compare. Note: If they match test will pass, if they do not match |
|
108 |
# the test will show that the diff statement failed and not reject file |
|
109 |
# will be created. You will need to go to the mysql-test dir and diff |
|
110 |
# the files your self to see what is not matching :-) Failed tests |
|
111 |
# will leave dump files in $MYSQLTEST_VARDIR/tmp |
|
112 |
||
113 |
diff_files $MYSQLTEST_VARDIR/tmp/trig001_master.sql $MYSQLTEST_VARDIR/tmp/trig001_slave.sql; |
|
114 |
||
115 |
# End of 5.0 test case |