Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_sp001.test

raw
1 #############################################################################
2 # This test is being created to test out the non deterministic items with #
3 # row based replication. #
4 # Original Author: JBM #
5 # Original Date: Aug/09/2005 #
6 # Updated: Aug/29/2005
7 #############################################################################
8 # Test: Includes two stored procedure tests. First test uses SP to insert #
9 # values from RAND() and NOW() into a table. #
10 # The second test uses SP with CASE structure to decide what to text #
11 # to update a given table with. #
12 ############################################################################
13
14 # Includes
15 -- source include/have_binlog_format_row.inc
16 -- source include/master-slave.inc
17
18 -- disable_query_log
19 -- disable_result_log
20
21 # Begin clean up test section
22 connection master;
23 --disable_warnings
24 DROP PROCEDURE IF EXISTS test.p1;
25 DROP PROCEDURE IF EXISTS test.p2;
26 DROP TABLE IF EXISTS test.t1;
27 DROP TABLE IF EXISTS test.t2;
28
29 -- enable_query_log
30 -- enable_result_log
31
32 # Begin test section 1 for non deterministic SP
33 let $message=<Begin test section 1 (non deterministic SP)>;
34 --source include/show_msg.inc
35
36 create table test.t1 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n));
37
38 delimiter //;
39 create procedure test.p1()
40 begin
41 INSERT INTO test.t1 (f,d) VALUES (RAND(),NOW());
42 end//
43 delimiter ;//
44
45 # show binlog events;
46
47 -- disable_query_log
48 -- disable_result_log
49 SET @wait_count=1;
50 let $1=10;
51 while ($1)
52 {
53 call test.p1();
54 let $wait_condition= SELECT COUNT(*) = @wait_count FROM test.t1;
55 -- source include/wait_condition.inc
56 -- disable_query_log
57 SET @wait_count = @wait_count + 1;
58 dec $1;
59 }
60 -- enable_result_log
61 -- enable_query_log
62
63 ## Used for debugging
64 #show binlog events;
65 #select * from test.t1;
66 #sync_slave_with_master;
67 #select * from test.t1;
68 #connection master;
69
70 let $message=<End test section 1 (non deterministic SP)>;
71 --source include/show_msg.inc
72
73
74 CREATE TABLE test.t2 (a INT NOT NULL AUTO_INCREMENT, t CHAR(4), PRIMARY KEY(a));
75
76 delimiter //;
77 CREATE PROCEDURE test.p2(n int)
78 begin
79 CASE n
80 WHEN 1 THEN
81 UPDATE test.t2 set t ='Tex';
82 WHEN 2 THEN
83 UPDATE test.t2 set t ='SQL';
84 ELSE
85 UPDATE test.t2 set t ='NONE';
86 END CASE;
87 end//
88 delimiter ;//
89
90 INSERT INTO test.t2 VALUES(NULL,'NEW'),(NULL,'NEW'),(NULL,'NEW'),(NULL,'NEW');
91
92 SELECT * FROM t2 ORDER BY a;
93 sync_slave_with_master;
94 SELECT * FROM t2 ORDER BY a;
95
96 connection master;
97 call test.p2(1);
98 SELECT * FROM t2 ORDER BY a;
99 sync_slave_with_master;
100 SELECT * FROM t2 ORDER BY a;
101
102
103 connection master;
104 call test.p2(2);
105 SELECT * FROM t2 ORDER BY a;
106 sync_slave_with_master;
107 SELECT * FROM t2 ORDER BY a;
108
109 connection master;
110 call test.p2(3);
111 SELECT * FROM t2 ORDER BY a;
112 sync_slave_with_master;
113 SELECT * FROM t2 ORDER BY a;
114
115 ##Used for debugging
116 #show binlog events;
117
118 # time to dump the databases and so we can see if they match
119
120 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp001_master.sql
121 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp001_slave.sql
122
123 # First lets cleanup
124
125 connection master;
126 DROP PROCEDURE test.p1;
127 DROP PROCEDURE test.p2;
128 DROP TABLE test.t1;
129 DROP TABLE test.t2;
130 sync_slave_with_master;
131
132 # Lets compare. Note: If they match test will pass, if they do not match
133 # the test will show that the diff statement failed and not reject file
134 # will be created. You will need to go to the mysql-test dir and diff
135 # the files your self to see what is not matching :-) Failed dump files
136 # will be located in $MYSQLTEST_VARDIR/tmp
137
138 diff_files $MYSQLTEST_VARDIR/tmp/sp001_master.sql $MYSQLTEST_VARDIR/tmp/sp001_slave.sql;
139
140 # If all is good, when can cleanup our dump files.
141 --remove_file $MYSQLTEST_VARDIR/tmp/sp001_master.sql
142 --remove_file $MYSQLTEST_VARDIR/tmp/sp001_slave.sql
143
144 # End of 5.0 test case
145 --source include/rpl_end.inc