1############################################################################# 2# Original Author: JBM # 3# Original Date: Aug/14/2005 # 4############################################################################# 5# Test: This test contains two sp that create and drop tables, insert and # 6# updated data and uses the NOW() function. # 7############################################################################# 8 9 10# Includes 11-- source include/master-slave.inc 12 13 14# Begin clean up test section 15connection master; 16--disable_warnings 17DROP PROCEDURE IF EXISTS test.p1; 18DROP PROCEDURE IF EXISTS test.p2; 19DROP TABLE IF EXISTS test.t2; 20DROP TABLE IF EXISTS test.t1; 21DROP TABLE IF EXISTS test.t3; 22--enable_warnings 23# End of cleanup 24 25# Begin test section 1 26 27delimiter |; 28CREATE PROCEDURE test.p1() 29BEGIN 30 CREATE TABLE IF NOT EXISTS test.t1(a INT,PRIMARY KEY(a)); 31 CREATE TABLE IF NOT EXISTS test.t2(a INT,PRIMARY KEY(a)); 32 INSERT INTO test.t1 VALUES (4),(2),(1),(3); 33 UPDATE test.t1 SET a=a+4 WHERE a=4; 34 INSERT INTO test.t2 (a) SELECT t1.a FROM test.t1; 35 UPDATE test.t1 SET a=a+4 WHERE a=8; 36 CREATE TABLE IF NOT EXISTS test.t3(n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); 37END| 38CREATE PROCEDURE test.p2() 39BEGIN 40 DROP TABLE IF EXISTS test.t1; 41 DROP TABLE IF EXISTS test.t2; 42 INSERT INTO test.t3 VALUES(NULL,11111111.233333,NOW()); 43END| 44delimiter ;| 45 46CALL test.p1(); 47SELECT * FROM test.t1 ORDER BY a; 48SELECT * FROM test.t2 ORDER BY a; 49sync_slave_with_master; 50SELECT * FROM test.t1 ORDER BY a; 51SELECT * FROM test.t2 ORDER BY a; 52 53connection master; 54CALL test.p2(); 55USE test; 56SHOW TABLES; 57#SELECT * FROM test.t3; 58sync_slave_with_master; 59USE test; 60SHOW TABLES; 61#SELECT * FROM test.t3; 62 63connection master; 64CALL test.p1(); 65SELECT * FROM test.t1 ORDER BY a; 66SELECT * FROM test.t2 ORDER BY a; 67#SELECT * FROM test.t3; 68sync_slave_with_master; 69SELECT * FROM test.t1 ORDER BY a; 70SELECT * FROM test.t2 ORDER BY a; 71#SELECT * FROM test.t3; 72 73--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp004_master.sql 74--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp004_slave.sql 75 76# Cleanup 77connection master; 78#show binlog events; 79DROP PROCEDURE IF EXISTS test.p1; 80DROP PROCEDURE IF EXISTS test.p2; 81DROP TABLE IF EXISTS test.t1; 82DROP TABLE IF EXISTS test.t2; 83DROP TABLE IF EXISTS test.t3; 84sync_slave_with_master; 85 86# If the test fails, you will need to diff the dumps to see why. 87 88diff_files $MYSQLTEST_VARDIR/tmp/sp004_master.sql $MYSQLTEST_VARDIR/tmp/sp004_slave.sql; 89 90 91# End of 5.0 test case 92--source include/rpl_end.inc 93