############################################################################# # Original Author: JBM # # Original Date: Aug/09/2005 # ############################################################################# # TEST: Use after insert and before inset triggers and stored procdures to # # Update and insert data # ############################################################################# # 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 DROP PROCEDURE IF EXISTS test.p2; DROP PROCEDURE IF EXISTS test.p3; --error 0,1360 DROP TRIGGER test.t2_ai; --error 0,1360 DROP TRIGGER test.t3_bi_t2; --error 0,1360 DROP TABLE IF EXISTS test.t1; DROP TABLE IF EXISTS test.t2; DROP TABLE IF EXISTS test.t3; # test section 1, lets add a trigger to the mix. Taken from bug #12280 let $message=<Begin test section 1 (Tiggers & SP)>; --source include/show_msg.inc CREATE TABLE test.t1 (n MEDIUMINT NOT NULL, d DATETIME, PRIMARY KEY(n)); CREATE TABLE test.t2 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); CREATE TABLE test.t3 (n MEDIUMINT NOT NULL AUTO_INCREMENT, d DATETIME, PRIMARY KEY(n)); INSERT INTO test.t1 VALUES (1,NOW()); delimiter //; CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t1 SET d=NOW() where n = 1// CREATE PROCEDURE test.p3() BEGIN INSERT INTO test.t3 (d) VALUES (NOW()); END// CREATE TRIGGER test.t3_bi_t2 BEFORE INSERT ON test.t2 FOR EACH ROW CALL test.p3()// CREATE PROCEDURE test.p2() BEGIN INSERT INTO test.t2 (f,d) VALUES (RAND(),NOW()); END// delimiter ;// # Make sure that all definition have propagated to the slave sync_slave_with_master; connection master; -- disable_query_log -- disable_result_log SET @wait_count = 1; let $1=10; while ($1) { CALL test.p2(); let $wait_condition= SELECT COUNT(*) = @wait_count FROM test.t3; --source include/wait_condition.inc --disable_query_log SET @wait_count = @wait_count + 1; dec $1; } -- enable_result_log -- enable_query_log # Just a precaution to make sure all changes have made it over to the # slave connection master; let $count = `select count(*) from t1`; eval INSERT INTO test.t1 VALUES ($count+1, NOW()); sync_slave_with_master; #show binlog events; #select * from test.t2; #select * from test.t3; #connection slave; #select * from test.t2; #select * from test.t3; let $message=<End test section 2 (Tiggers & SP)>; --source include/show_msg.inc # 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/trig001_master.sql --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trig001_slave.sql # Cleanup connection master; DROP PROCEDURE test.p2; DROP PROCEDURE test.p3; DROP TRIGGER test.t2_ai; DROP TRIGGER test.t3_bi_t2; DROP TABLE test.t1; DROP TABLE test.t2; DROP TABLE test.t3; sync_slave_with_master; # Lets compare. Note: If they match test will pass, if they do not match # the test will show that the diff statement failed and not reject file # will be created. You will need to go to the mysql-test dir and diff # the files your self to see what is not matching :-) Failed tests # will leave dump files in $MYSQLTEST_VARDIR/tmp diff_files $MYSQLTEST_VARDIR/tmp/trig001_master.sql $MYSQLTEST_VARDIR/tmp/trig001_slave.sql; # End of 5.0 test case