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