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