Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_trig001.test

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