Debian Codegraph @

mysql-test/suite/rpl/include/rpl_row_sp006.test

raw
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
13 DROP TABLE IF EXISTS t1;
14 DROP TABLE IF EXISTS t2;
15 DROP PROCEDURE IF EXISTS p1;
16 DROP PROCEDURE IF EXISTS p2;
17 --enable_warnings
18 # End of cleanup
19
20 # Begin test section 1
21 eval CREATE TABLE IF NOT EXISTS t1(name CHAR(16), birth DATE,PRIMARY KEY(name))ENGINE=$engine_type;
22 eval CREATE TABLE IF NOT EXISTS t2(name CHAR(16), age INT ,PRIMARY KEY(name))ENGINE=$engine_type;
23
24 delimiter |;
25 CREATE PROCEDURE p1()
26 BEGIN
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;
49 END|
50 CREATE PROCEDURE p2()
51 BEGIN
52 INSERT INTO t1 VALUES ('MySQL','1993-02-04'),('ROCKS', '1990-08-27'),('Texas', '1999-03-30'),('kyle','2005-1-1');
53 END|
54 delimiter ;|
55
56 CALL p2();
57 sync_slave_with_master;
58
59 connection master;
60 CALL p1();
61 sync_slave_with_master;
62
63 connection 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
69 DROP TABLE t1;
70 DROP TABLE t2;
71 DROP PROCEDURE p1;
72 DROP 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
80 diff_files $MYSQLTEST_VARDIR/tmp/sp006_master.sql $MYSQLTEST_VARDIR/tmp/sp006_slave.sql;
81
82 sync_slave_with_master;
83
84 # End of 5.1 test case