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 17connection master; 18--disable_warnings 19DROP PROCEDURE IF EXISTS test.p2; 20DROP PROCEDURE IF EXISTS test.p3; 21--error 0,1360 22DROP TRIGGER test.t2_ai; 23--error 0,1360 24DROP TRIGGER test.t3_bi_t2; 25--error 0,1360 26DROP TABLE IF EXISTS test.t1; 27DROP TABLE IF EXISTS test.t2; 28DROP TABLE IF EXISTS test.t3; 29 30 31# test section 1, lets add a trigger to the mix. Taken from bug #12280 32let $message=<Begin test section 1 (Tiggers & SP)>; 33--source include/show_msg.inc 34 35CREATE TABLE test.t1 (n MEDIUMINT NOT NULL, d DATETIME, PRIMARY KEY(n)); 36CREATE TABLE test.t2 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); 37CREATE TABLE test.t3 (n MEDIUMINT NOT NULL AUTO_INCREMENT, d DATETIME, PRIMARY KEY(n)); 38 39INSERT INTO test.t1 VALUES (1,NOW()); 40 41delimiter //; 42CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t1 SET d=NOW() where n = 1// 43CREATE PROCEDURE test.p3() 44BEGIN 45 INSERT INTO test.t3 (d) VALUES (NOW()); 46END// 47CREATE TRIGGER test.t3_bi_t2 BEFORE INSERT ON test.t2 FOR EACH ROW CALL test.p3()// 48CREATE PROCEDURE test.p2() 49BEGIN 50 INSERT INTO test.t2 (f,d) VALUES (RAND(),NOW()); 51END// 52delimiter ;// 53 54# Make sure that all definition have propagated to the slave 55sync_slave_with_master; 56 57connection master; 58-- disable_query_log 59-- disable_result_log 60SET @wait_count = 1; 61let $1=10; 62while ($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 76connection master; 77let $count = `select count(*) from t1`; 78eval INSERT INTO test.t1 VALUES ($count+1, NOW()); 79sync_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 88let $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 97connection master; 98DROP PROCEDURE test.p2; 99DROP PROCEDURE test.p3; 100DROP TRIGGER test.t2_ai; 101DROP TRIGGER test.t3_bi_t2; 102DROP TABLE test.t1; 103DROP TABLE test.t2; 104DROP TABLE test.t3; 105sync_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 113diff_files $MYSQLTEST_VARDIR/tmp/trig001_master.sql $MYSQLTEST_VARDIR/tmp/trig001_slave.sql; 114 115# End of 5.0 test case 116--source include/rpl_end.inc 117