1############################################################################# 2# Original Author: JBM # 3# Original Date: Aug/15/2005 # 4# Updated: 08/29/2005 to remove sleeps # 5############################################################################# 6# Test: This test uses two SPs, one to populate a table, and another to use # 7# Cursors, CURRENT_DATE(), loop control, date math and logic control # 8# to populate a table with data from the first table. # 9############################################################################# 10 11# Begin clean up test section 12--disable_warnings 13DROP TABLE IF EXISTS t1; 14DROP TABLE IF EXISTS t2; 15DROP PROCEDURE IF EXISTS p1; 16DROP PROCEDURE IF EXISTS p2; 17--enable_warnings 18# End of cleanup 19 20# Begin test section 1 21eval CREATE TABLE IF NOT EXISTS t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=$engine_type; 22eval CREATE TABLE IF NOT EXISTS t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=$engine_type; 23 24delimiter |; 25CREATE PROCEDURE p1() 26BEGIN 27 DECLARE done INT DEFAULT 0; 28 DECLARE spa CHAR(16); 29 DECLARE spb INT; 30 DECLARE cur1 CURSOR FOR SELECT name, 31 (YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) 32 FROM t1; 33 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 34 35 OPEN cur1; 36 37 SET AUTOCOMMIT=0; 38 REPEAT 39 FETCH cur1 INTO spa, spb; 40 IF NOT done THEN 41 START TRANSACTION; 42 INSERT INTO t2 VALUES (spa,spb); 43 COMMIT; 44 END IF; 45 UNTIL done END REPEAT; 46 47 SET AUTOCOMMIT=1; 48 CLOSE cur1; 49END| 50CREATE PROCEDURE p2() 51BEGIN 52 INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); 53END| 54delimiter ;| 55 56CALL p2(); 57sync_slave_with_master; 58 59connection master; 60CALL p1(); 61sync_slave_with_master; 62 63connection master; 64 65--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp006_master.sql 66--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp006_slave.sql 67 68 69DROP TABLE t1; 70DROP TABLE t2; 71DROP PROCEDURE p1; 72DROP PROCEDURE p2; 73 74# Lets compare. Note: If they match test will pass, if they do not match 75# the test will show that the diff statement failed and not reject file 76# will be created. You will need to go to the mysql-test dir and diff 77# the files your self to see what is not matching :-) Failed test 78# Dump files will be located in $MYSQLTEST_VARDIR/tmp. 79 80diff_files $MYSQLTEST_VARDIR/tmp/sp006_master.sql $MYSQLTEST_VARDIR/tmp/sp006_slave.sql; 81 82sync_slave_with_master; 83 84# End of 5.1 test case 85