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