diff options
Diffstat (limited to 'mysql-test/suite/rpl/include/rpl_row_sp006.test')
-rw-r--r-- | mysql-test/suite/rpl/include/rpl_row_sp006.test | 84 |
1 files changed, 84 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/include/rpl_row_sp006.test b/mysql-test/suite/rpl/include/rpl_row_sp006.test new file mode 100644 index 00000000000..16a8374ae7f --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_row_sp006.test @@ -0,0 +1,84 @@ +############################################################################# +# Original Author: JBM # +# Original Date: Aug/15/2005 # +# Updated: 08/29/2005 to remove sleeps # +############################################################################# +# Test: This test uses two SPs, one to populate a table, and another to use # +# Cursors, CURRENT_DATE(), loop control, date math and logic control # +# to populate a table with data from the first table. # +############################################################################# + +# Begin clean up test section +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings +# End of cleanup + +# Begin test section 1 +eval CREATE TABLE IF NOT EXISTS t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=$engine_type; +eval CREATE TABLE IF NOT EXISTS t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=$engine_type; + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE spa CHAR(16); + DECLARE spb INT; + DECLARE cur1 CURSOR FOR SELECT name, + (YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) + FROM t1; + DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; + + OPEN cur1; + + SET AUTOCOMMIT=0; + REPEAT + FETCH cur1 INTO spa, spb; + IF NOT done THEN + START TRANSACTION; + INSERT INTO t2 VALUES (spa,spb); + COMMIT; + END IF; + UNTIL done END REPEAT; + + SET AUTOCOMMIT=1; + CLOSE cur1; +END| +CREATE PROCEDURE p2() +BEGIN + INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1'); +END| +delimiter ;| + +CALL p2(); +sync_slave_with_master; + +connection master; +CALL p1(); +sync_slave_with_master; + +connection master; + +--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp006_master.sql +--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp006_slave.sql + + +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +# 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 test +# Dump files will be located in $MYSQLTEST_VARDIR/tmp. + +diff_files $MYSQLTEST_VARDIR/tmp/sp006_master.sql $MYSQLTEST_VARDIR/tmp/sp006_slave.sql; + +sync_slave_with_master; + +# End of 5.1 test case |